Foreign key: in which table

foreign keytable

I made the tables blood_types and persons. Should the foreign key be created in the blood_types or in the persons table like I did?

A person can have 1 blood type. A blood type can be assigned to multiple persons.

CREATE TABLE blood_types
(
 blo_id serial NOT NULL,
 blo_name character varying(10),
 CONSTRAINT blood_types_pkey PRIMARY KEY (blo_id)
)

CREATE TABLE persons
(
  per_id serial NOT NULL,
  per_passport character varying(50),
  per_name character varying(50),
  per_blood_type integer, 
  CONSTRAINT persons_pkey PRIMARY KEY (per_id),
  CONSTRAINT persons_per_blood_type_fkey FOREIGN KEY (per_blood_type)
      REFERENCES per_blood_types (blo_id) MATCH SIMPLE
)

Or maybe should the foreign key be created in the blood_type table?
I have more similars and simples examples but I can't understand.
Should I think as weak and strong entities (I think both are strong) or maybe having in mind the cardinalities?

Best Answer

In your blood_types table,

CREATE TABLE blood_types
(
 blo_id serial NOT NULL,
 blo_name character varying(10),
 CONSTRAINT blood_types_pkey PRIMARY KEY (blo_id)
)

You correctly have blo_id as a PRIMARY KEY.

And then in the persons table, have the per_blood_type integer as a FOREIGN KEY referencing blo_id in table blood_types (as you appear to have - can't check exact syntax - don't have running PostgreSQL system at the minute). The way you have have it appears to be the correct way - blood type depends on the person and only the person - not the other way round.

Related Question