Re: newbie db design question - Mailing list pgsql-general

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
checkingthe 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
pluralform of the content item (primary key). This is not the only way to do business. Many on this list use integers
asan id. The rationale behind the short text identifiers is that you can see what is going on when inspecting the data.
Soin this spirit you might also consider a “natural”, i.e., text, as the primary key for the products. Opinions vary in
thisarea and often devolve to performance. Remember the basic programers trade off — your time versus hardware time.
Mostlywe are better using conventions that make our time more effective, but some have truly serious data problems and
maybenefit 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
productsfrom that supplier can get entered. This means any attempt to enter a product where the supplier is not known
(orhas been misspelt) will raise an error. IMNSHO constraints are good: they help preserve data integrity, allow keys
tobe 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