SQL table normalization: Simple question about restricting record participation in a many to many relation

Question

If you have the following tables and relations:

• A Product table
• A Price Point table (one or more prices for a product)
• A Version table (groups of products sold in the same area)

and the constraint that only one product price can exist in any given version. How would you construct the tables to reflect the constraints?

To illustrate the restriction, I have a product priced at $1, $2, and $3. I have media zones of A, B, C, D, E, F, G, H, I (each representing a place like Columbus OH)

product A price $1 goes to A, B, C
product A price $2 goes to D, E, F
product A price $3 goes to G, H, I

Product A price $1 when it exists in A, B, C cannot have other prices ($2, $3) in A, B, C

Would a Version M2M table to PricePoints, then put a unique index on the M2M table on all fields minus the PricePoint field work? (thought about it while typing this out) Is there a better way to represent the relationships?

Answer 1

I'm having a little trouble understanding your question. I don't understand the statement "Product A price $1 when it exists in A, B, C cannot exist in D, E, F, G, H, I.". I'm going to assume, for the sake of this answer, that "version" and "media zone" are the same thing.

Use an intermediate Pricing table with three fields: product_id, version_id, and price_id. The primary key of this table (or a unique index if you choose to use an incremental non-intelligent key) is (product_id, version_id).

Answer 2

Unless you can definitively say that a product-price combination is only allowed in specific regions, I think it's better to forgo a data constraint and use a business rule constraint at a stored procedure or other business layer to check to see what data exists before attempting to add product-price-region combinations to the database.

Answer 3

Here's how I would construct the tables to reflect the constraints, based on the data supplied:

SQL DDL:

CREATE TABLE Products
(
 product_name CHAR(1) NOT NULL UNIQUE
);

CREATE TABLE ProductPrices
(
 product_name CHAR(1) NOT NULL
    REFERENCES Products (product_name), 
 product_price DECIMAL(19, 4) NOT NULL
    CHECK (product_price > 0), 
 UNIQUE (product_name,  product_price)
);

CREATE TABLE MediaZones
(
 zone_name CHAR(1) NOT NULL UNIQUE
);

CREATE TABLE Versions
(
 product_name CHAR(1) NOT NULL, 
 product_price DECIMAL(19, 4) NOT NULL, 
 FOREIGN KEY (product_name,  product_price)
    REFERENCES ProductPrices (product_name,  product_price), 
 zone_name CHAR(1) NOT NULL 
    REFERENCES MediaZones (zone_name), 
 UNIQUE (product_name, zone_name)
);

SQL DML (succeeds = good):

INSERT INTO Products (product_name) VALUES ('A');

INSERT INTO MediaZones (zone_name) 
   VALUES ('A'), ('B'), ('C'), 
          ('D'), ('E'), ('F'), 
          ('G'), ('H'), ('I');


INSERT INTO ProductPrices (product_name, product_price) 
   VALUES ('A', 1),
          ('A', 2), 
          ('A', 3);

SQL DML (fails = good):

INSERT INTO Versions (product_name, product_price, zone_name) 
   VALUES ('A', 1, 'G');

INSERT INTO Versions (product_name, product_price, zone_name) 
   VALUES ('A', 1, 'A');

INSERT INTO Versions (product_name, product_price, zone_name) 
   VALUES ('A', 1, 'Z');
 INTO Versions (product_name, product_price, zone_name) 
   VALUES ('A', 2, 'A');

etc etc