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);
