newbie db design question

On 11 Jun 2022, at 17:05, Rino Mardo wrote:

… experimental database design:

create table products (
product_id serial primary key,
description text,
supplier_id????) ;

create table supplier (
supplier_id serial primary key,
description text) ;

… the products table should be linked to the supplier table via "supplier_id"

This is best done via foreign key constraint — see below

… what would be the data type of supplier_id in

This is a matter of style, either an integer (even serial), or text (which will help a human like yourself when checking the data)

how then to proceed?

You could adopt a style convention advocated by Joe Celko, eg., in “SQL for Smarties”, where the table name is the plural form of the content item (primary key). This is not the only way to do business. Many on this list use integers as an id. The rationale behind the short text identifiers is that you can see what is going on when inspecting the data. So in this spirit you might also consider a “natural”, i.e., text, as the primary key for the products. Opinions vary in this area and often devolve to performance. Remember the basic programers trade off — your time versus hardware time. Mostly we are better using conventions that make our time more effective, but some have truly serious data problems and may benefit from less human friendly practices. As always YMMV.

So combining all these suggestions:

pendari=# create table products (
pendari(# product serial primary key,
pendari(# description text,
pendari(# supplier text)
pendari-# ;
CREATE TABLE

pendari=# create table suppliers (
pendari(# supplier text primary key,
pendari(# description text)
pendari-# ;
CREATE TABLE

— I used pgadmin4 to build this constraint, and I suggest you will find it helpful as well
— this is the definition of the constraint holding the supplier key between tables
—
pendari=# ALTER TABLE IF EXISTS public.products
pendari-# ADD CONSTRAINT fkey_check__supplier_must_exist FOREIGN KEY (supplier)
pendari-# REFERENCES public.suppliers (supplier) MATCH SIMPLE
pendari-# ON UPDATE CASCADE
pendari-# ON DELETE RESTRICT
pendari-# DEFERRABLE
pendari-# NOT VALID;
ALTER TABLE

You now have a basic skeleton which ties the supplier keys together. The supplier has to be entered first, then all the products from that supplier can get entered. This means any attempt to enter a product where the supplier is not known (or has been misspelt) will raise an error. IMNSHO constraints are good: they help preserve data integrity, allow keys to be changes (and the change cascades to all the places which matter), and only cause extra db work when new data is entered (i.e., no ongoing overhead).

Regards

Gavan Schneider
——
Gavan Schneider, Sodwalls, NSW, Australia
Explanations exist; they have existed for all time; there is always a well-known solution to every human problem — neat, plausible, and wrong.
— H. L. Mencken, 1920

On 11 June 2022 08:05:41 Rino Mardo <rino19ny@gmail.com> wrote:

hello!

really noob question here. i have this experimental database design:

create table products (
product_id serial primary key,
description text,
supplier_id????) ;

create table supplier (
supplier_id serial primary key,
description text) ;

the products table should be linked to the supplier table via "supplier_id"
column. i can't find out what would be the data type of supplier_id in
table products to do that. i tried

Hi there,

"serial" isn't a real type - it's just a shortcut for "integer not null
default nextval(....)". Read about it here:

https://www.postgresql.org/docs/current/datatype-numeric.html#DATATYPE-SERIAL

So the supplier_id column in the first table should be of type "integer".

supplier_id serial primary key references supplier

but it won't allow multiple primary key.

how then to proceed?

You need a foreign key.... add this to the first table:

constraint <constraint name> foreign key (supplier_id) references
supplier(supplier_id)

I hope this helps.

Ray.

On 2022-06-11 09:43:46 +0100, Ray O'Donnell wrote:

On 11 June 2022 08:05:41 Rino Mardo <rino19ny@gmail.com> wrote:
really noob question here. i have this experimental database design:

create table products (
product_id serial primary key,
description text,
supplier_id????) ;

create table supplier (
supplier_id serial primary key,
description text) ;

the products table should be linked to the supplier table via "supplier_id"
column. i can't find out what would be the data type of supplier_id in
table products to do that. i tried

[...]

how then to proceed?

You need a foreign key.... add this to the first table:

constraint <constraint name> foreign key (supplier_id) references supplier
(supplier_id)

Or somewhat simpler:

create table products (
product_id serial primary key,
description text,
supplier_id integer references supplier
);

(You need to create supplier before doing that, of course.)

hp

PS: I noticed that "products" is plural and "supplier" is singular. It's
genereally helpful to choose one convention and stick to it.

--
_ | Peter J. Holzer | Story must make more sense than reality.
|_|_) | |
| | | hjp@hjp.at | -- Charles Stross, "Creative writing
__/ | http://www.hjp.at/ | challenge!"

thank you. i have received some tips on how to approach my problem. i will
be trying them out.

On Sat, 11 Jun 2022, 5:51 pm Peter J. Holzer <hjp-pgsql@hjp.at> wrote: