I've run into a bit of a sticky design issue. We have products in
three categories which I will call 'A', 'B' and 'C'. We have "kits"
which contain three products, one from each category.
Below is some sample SQL to set things up, but I need to ensure that
each kit gets three products -- one from each category. Obviously,
this basic SQL doesn't allow that. Any suggestions? Do I need a
different schema design, or is there something else I should be
looking at?
Cheers,
Curtis
CREATE TABLE category (
id int identity primary key,
name varchar(30)
);
CREATE TABLE products (
id int identity primary key,
name varchar(30),
category_id int references category(id)
);
CREATE TABLE kits (
id int identity primary key,
name varchar(30)
);
CREATE TABLE kit_products (
kit_id int references kits(id),
product_id int references products(id)
);>> We have products in three categories which I will call 'A', 'B' and
'C'. <<
... and you declared them as INTEGER.
>> We have "kits" which contain three products, one from each
category. <<
So, do you have only three categories??
>> Do I need a different schema design, ... <<
Oh yeah! You do not have any keys (IDENTITY is never a key by
definition) and "id" is to vague to be a data element name (read
ISO-11179 rules). Category is singular, while the other table names
are plural; ergo, category must have one and only one row? All the
important data is NULL-able.
I am going to assume that you have so many categories that they
require a separate table; if not, put them in a CHECK() clause.
CREATE TABLE Categories
(category_id INTEGER PRIMARY KEY,
category_name VARCHAR(30) NOT NULL);
CREATE TABLE Products
(product_name VARCHAR(30) NOT NULL,
product_id INTEGER NOT NULL UNIQUE,
category_id INTEGER NOT NULL
REFERENCES Categories(id)
ON UPDATE CASCADE
ON DELETE CASCADE,
PRIMARY KEY (product_id, category_id));
CREATE TABLE ProductKits
(kit_id INTEGER NOT NULL
kit_name VARCHAR(30) NOT NULL,
product_id_1 INTEGER NOT NULL,
category_id_1 INTEGER NOT NULL
FOREIGN KEY (product_id_1, category_id_1)
REFERENCES Products (product_id, category_id)
ON UPDATE CASCADE
ON DELETE CASCADE,
product_id_2 INTEGER NOT NULL,
category_id_2 INTEGER NOT NULL
FOREIGN KEY (product_id_2, category_id_2)
REFERENCES Products (product_id, category_id)
ON UPDATE CASCADE
ON DELETE CASCADE,
product_id_3 INTEGER NOT NULL,
category_id_3 INTEGER NOT NULL
FOREIGN KEY (product_id_3, category_id_3)
REFERENCES Products (product_id, category_id)
ON UPDATE CASCADE
ON DELETE CASCADE,
CHECK (category_id_1 = 1
AND category_id_2 = 2
AND category_id_3 = 3));
The sneaky trick is to put both (product_id, category_id) in the
primary key of Products, so both can be referenced. The product_id is
still unique (another assumption, since your original schema allowed a
product to be named NULL or repeated under a thousand different
IDENTITY numbers, making data integrity impossible). I also assume
that the categories for the kits is (1, 2, 3) instead of ('a', 'b',
'c').
If there are onlyn three categories, then use this and no separate
Categories table:
CREATE TABLE Products
(product_name VARCHAR(30) NOT NULL,
product_id INTEGER NOT NULL UNIQUE,
category_id INTEGER NOT NULL
CHECK(category_id IN (3,2,1))
PRIMARY KEY (product_id, category_id));
