Database Design – Implementing a One to Zero or One Relationship

database-designschema

Suposse I am using a SQL platform and want to implement a relationship of cardinality one-to-zero-or-one (1-0..1). This situation would pertain to a business context in which let us say that:

  • A product can have zero or one price.

So:

  1. What are the differences between the two scenarios (DDL structures) shown bellow?
  2. When should one be preferred over the other?
  3. Is any of them considered a bad design?

Scenario 1:

CREATE TABLE product
(
    product_id       INT      NOT NULL,
    name             CHAR(30) NULL,
    product_price_id INT      NULL,
    CONSTRAINT PK_product       PRIMARY KEY (product_id),
    CONSTRAINT UQ_product_price UNIQUE      (product_price_id)
);

CREATE TABLE product_price
(
    product_price_id INT     NOT NULL,
    price            DECIMAL NOT NULL,
    tax_rate         DECIMAL NOT NULL,
    currency_id      INT     NOT NULL,
    CONSTRAINT PK_product_price            PRIMARY KEY (product_price_id),
    CONSTRAINT FK_product_price_to_product FOREIGN KEY (product_price_id)
        REFERENCES product (product_id)
);

Scenario 2:

CREATE TABLE product
(
    product_id INT      NOT NULL,
    name       CHAR(30) NULL,
    CONSTRAINT PK_product PRIMARY KEY (product_id)
);

CREATE TABLE product_price
(
    product_id  INT     NOT NULL, -- Assume that this column might be set as PRIMARY KEY or UNIQUE.
    price       DECIMAL NOT NULL,
    tax_rate    DECIMAL NOT NULL,
    currency_id INT     NOT NULL,
    CONSTRAINT PK_product_price            PRIMARY KEY (product_id),
    CONSTRAINT FK_product_price_to_product FOREIGN KEY (product_id)
        REFERENCES product (product_id)
);

Best Answer

Scenario 1 could have multiple products linked to the same product_price. The second scenario, where the tables have the same PK (and one is a FK), is forced to being 1 to {0,1}.

Edit: Well now that you've changed Scenario1, my answer doesn't fit any more. Now, your two scenarios are identical, but your first table has two PKs. Scenario2 is fine and achieves what you want. If most products have prices, then you could also have them in a single table, which would look like the results of a Left Join, such that the products without prices just have NULL in those columns.

Related Question