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

  • "... then I end up with single-row tables without a primary key" Why without a PK? If the table is to be restricted to a single row, then the (ideal) PK is the empty set (of columns). And I say ideal because no SQL DBMS has implemented such a feature. You can however, in most DBMS, enforce the restriction with a different way. See: How to limit maximum number of rows in a table to just 1 – ypercubeᵀᴹ Jan 25 '18 at 9:19
  • I did not know a PK could be the empty set. – soliz Jan 25 '18 at 9:28
  • I could indeed keep the initial version of facility and facility_description by constraining the tables to a maximum of 1 row with constraints on PK facility_id. However, it does not feel logical that the other tables would lose their reference to facility while the first 2 would keep it. – soliz Jan 25 '18 at 9:43
  • Yes, from a constraint point of view, it would make more sense to keep the initial design and the FKs in all tables. If you remove the facility_id from the area table, nothing forbids a row to be added there while the facility table is empty. – ypercubeᵀᴹ Jan 25 '18 at 9:53
  • "I did not know a PK could be the empty set." It cannot in SQL. But the relational theory allows it I think. – ypercubeᵀᴹ Jan 25 '18 at 9:55

Your Answer

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

Browse other questions tagged or ask your own question.