3

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)
);
4

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.

  • good catch! I added a unique constrain to the product table. Mind commenting again ? :D – Bart Calixto Apr 12 '14 at 6:36
  • 1
    @Bart, now in scenario 1, product_price_id is a useless column (as scenario 2 shows, you can use the product_id instead.) Plus, the combination NULL UNIQUE constraints will fail miserably in SQL-Server. – ypercubeᵀᴹ Apr 12 '14 at 13:08

Your Answer

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

Not the answer you're looking for? Browse other questions tagged or ask your own question.