1

Very basic questions. I have 3 tables, very obvious whats the use of them:

--------
CONTACT
--------
ID (SERIAL) - PK
NAME (VARCHAR)

---------------
CONTACT_DETAILS
----------------
ID (SERIAL) - PK
ID_CONTACT (INT)
ADDRESS (VARCHAR)
BIRTH (DATE)

--------
SALES
--------
ID (SERIAL) - PK
ID_CONTACT) (INT)
AMOUNT (NUMERIC)
DATE_PAID (DATE)

1) Should I create a Foreign Key between CONTACT and CONTACT_DETAILS?
If yes, should be CONTACT_DETAILS (ID_CONTACT) ---> CONTACT (ID) or inverse? And you recommend that its cascade?

2) Same question, should I creat a Foreign Key between SALES table and CONTACT?

3) It is a good pract every table have an "ID" serial column, so its easy to identify or there's no need?

  • 1
    Call them sale_id, contact_id &c. Having all your PKs the same name is cunfusing and makes debugging difficult! – Vérace Apr 12 '16 at 1:23
  • Thanks for the tips. Already changed all ID names and the post was very helpful. – Antonio Apr 12 '16 at 1:39
  • It looks like you could benefit from sitting one or two introductions to database design. Just youtube "database design" and you'll likely get clarity around these issues. Some tables need a serial ID, some tables don't, it's more a matter of purpose of the entity than good practice. Also, cascading foreign keys could be convenient but can also defeat the purpose if the purpose of the FK is avoiding accidental deletion of relations with dependencies. – Ziggy Crueltyfree Zeitgeister Apr 12 '16 at 3:20
2

Foreign keys

Foreign keys are good to keep the database consistent. The common practice is to name the foreign key starting with the name that it refers to then the name of the column: instead of id_contact use contact_id.

ID column

There are a couple of benefits of serial primary key.

  • Faster insert speed
  • Easier to refer to the row if necessary
  • Some frameworks requires single column PK (for example Django)

Also it's possible to use natural primary key like email address or personal identification number or composite keys. The benefits are:

  • Lookup on these are faster

Usually insert speed are lower because insert happens out of order and results in more fragmented table.

This really depends on many things and without context it's hard to give recommendation. You need to take into consideration the application using this database, the relationships, insert/update/select rate, etc. Try and see what works best for your case.

Schema

I would start with something like this:

Since it's unlikely that a contact will have more than one birthday splitting it is unnecessary. You can though have a different address table if it's possible to have two or more address for a single contact. The simplest solution would be:

create table contact (
    id serial NOT NULL,
    name text not null, 
    birth date,
    address text,
    PRIMARY KEY (id)
);

create table sales (
    id serial NOT NULL,
    contact_id integer NOT NULL REFERENCES "contact" ("id") DEFERRABLE INITIALLY DEFERRED,
    amount numeric NOT NULL,
    date_paid date NOT NULL,
    PRIMARY KEY (id)
);

Bare in mind that postgresql doesn't create an index automatically for foreign keys (more info: https://stackoverflow.com/questions/970562/postgres-and-indexes-on-foreign-keys-and-primary-keys). If you need it you have to create it explicitly:

CREATE INDEX sales_contact_fk_idx ON sales (contact_id);

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.