Context
I was designing a relational database model to store data about a company and its production facilities. I had a facility relation and many other relations referencing it, most of the time with an identifying relationship.
Here is a simplified extract:
-- PostgreSQL
CREATE TABLE facility(
facility_id integer NOT NULL,
designation text NOT NULL,
CONSTRAINT pk_facility PRIMARY KEY (facility_id),
CONSTRAINT uq_facility UNIQUE (designation)
);
CREATE TABLE facility_description(
facility_id integer NOT NULL,
description text NOT NULL,
CONSTRAINT pk_facility_description PRIMARY KEY (facility_id),
CONSTRAINT fk_facility_description_facility FOREIGN KEY (facility_id)
REFERENCES facility (facility_id) MATCH SIMPLE
ON DELETE CASCADE ON UPDATE CASCADE
);
CREATE TABLE facility_area(
facility_id integer NOT NULL,
area_id integer NOT NULL,
designation text NOT NULL,
CONSTRAINT pk_facility_area PRIMARY KEY (facility_id, area_id),
CONSTRAINT uq_facility_area UNIQUE (facility_id, designation),
CONSTRAINT fk_facility_area_facility FOREIGN KEY (facility_id)
REFERENCES facility (facility_id) MATCH SIMPLE
ON DELETE RESTRICT ON UPDATE CASCADE
);
Requirements evolved and it was later decided that each facilities would have their own dedicated database. Put it differently, the database would now be specific to a single facility.
I could keep the exact same model but it seems like premature pessimization to keep this facility_id attribute in the tables since it will always have one single value. So, it would be easy to redesign facility_area as area:
CREATE TABLE area(
area_id integer NOT NULL,
designation text NOT NULL,
CONSTRAINT pk_facility_area PRIMARY KEY (area_id),
CONSTRAINT uq_facility_area UNIQUE (designation)
);
But if I remove facility_id from facility and facility_description then I end up with single-row tables without a primary key, which, I think, does not respect the relational model theory.
CREATE TABLE facility(
designation text NOT NULL,
CONSTRAINT uq_facility UNIQUE (designation)
);
CREATE TABLE facility_description(
description text NOT NULL
);
Moreover, in this proposal, since there is no foreign key referencing facility from facility_description, then nothing prevent a description to exist without a corresponding facility, which would have been considered a problem in the initial model.
Question
So, what is the correct way to store those “global/generic” data such as the designation of the single facility and its optional corresponding description? And by correct I mean by designing relations that respect the Fifth normal form (5NF).