2

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

soliz
  • 293
  • 1
  • 10

0 Answers0