[Solved] If two Table’s data are independant, but they have relationship (one Main, another Sub). What is the best way to link them?

by


Well you are on your with the last comment creating a Foreign Key. So we have a relationship between Customers and Contacts. However the outstanding question becomes what type is Customer:Contact relationship?

  • One-to-One (1:1) -> Each Customer has one contact and a Contact is for one customer.
  • One-to-Many (1:M) -> Each Customer has many contacts and a Contact is for one customer.
  • Many-to-Many (M:M) -> Each customer has Many Contacts and Each Contact is for many customers.

From your description discard 1:1. For 1:M the you include the customer PK in the contact table. For the M:M you actually create another table that contains the PK from both Customer and Contact.
As for generating ids do not even try to get the last one and increment it (generally referred to as MAX+1). In a multiple user environment it is a virtual guarantee you will get a duplicate. Simply create a sequence or a generated identity for each table and Postgres will handle it.

Tables for 1:M, Postgres v10 and above

create table customers( cust_id    integer generated always as identity  
                      , address    text
                      , tel        text
                      , fax        text
                      , pay_terms  text
                      , constraint cust_pk primary key (cust_id)
                      ) 
                      
 create table contacts( cont_id    integer generated always as identity 
                      , cust_id    interger 
                      , tel        text
                      , email      text
                      , constraint cont_pk primary key (cont_id)
                      , constraint cont_2_cust_fk 
                                   foreign key (cust_id)
                                   references customers(cust_id) 
                      );

Table definitions for M:M, Postgres v10 and above

create table customers( cust_id    integer generated always as identity  
                      , address    text
                      , tel        text
                      , fax        text
                      , pay_terms  text
                      , constraint cust_pk primary key (cust_id)
                      ) 
                      
create table contacts( cont_id    integer generated always as identity 
                     , cust_id    interger 
                     , tel        text
                     , email      text
                     , constraint cont_pk primary key (cont_id)
                     );
                      
create table customer_contacts( cust_id integer
                              , cont_id integer
                              , constraint cust_cont_pk 
                                           primary key (cust_id, cont_id)
                              , constraint cust_cont_2_cust_fk 
                                           foreign key (cust_id)
                                           references customers(cust_id)  
                              , constraint cust_cont_2_cont_fk 
                                           foreign key (cont_id)
                                           references contacts(cont_id)
                              );

For Postgres versions prior to v10, replace integer generated always as identity by serial.

2

solved If two Table’s data are independant, but they have relationship (one Main, another Sub). What is the best way to link them?