Business rules
I have three tables (Parties, Categories and Products) which representing the following relationships:
- A product is classified by zero-one-to-many categories
- A category classifies zero-one-or-many products
Then, I have the party relationships:
- A product is classified by one-to-one party
- A party classifies one-to-many products
In other words, a product doesn't have to be assigned a category, but they have to be assigned a party respectively.
The party_id
for a Category must match the party_id
for a Product in order to relate.
EDIT
Following is a correction to the business rules above, based on @damir-sudarevic's solution proposal:
Category
is defined by aparty
.- Each
category
is defined by exactly oneparty
. - Each
party
may define more than onecategory
.
- Each
Product
is classified by aparty
.- Each
product
is classified by exactly oneparty
. - Each
party
may classify more than oneproduct
.
- Each
Product
is classified in acategory
by aparty
.- Each
product
may be classified in more than onecategory
. - More than one
product
may be classified in the samecategory
. - A
product
is classified by aparty
in acategory
, then thatcategory
is defined by thatparty
.
- Each
Design proposals
I have based my first design on the proposal found here, but it's not entirely applicable since I want to enforce party_id
for both Products and Categories respectively and in the relation.
I have made a second proposal that simplifies the design somewhat, but I'm not sure how to enforce the party_id
to the Product-Category relation.
SQL based on latest design
Based on the comments and solution proposals, I have added a simplified SQL to create the tables and their relations.
CREATE TABLE IF NOT EXISTS parties (
id INT(10) UNSIGNED NOT NULL AUTO_INCREMENT,
PRIMARY KEY (id));
CREATE TABLE IF NOT EXISTS categories (
id INT(10) UNSIGNED NOT NULL AUTO_INCREMENT,
name_key VARCHAR(255) NOT NULL,
party_id INT(10) UNSIGNED NOT NULL,
parent_id INT(10) UNSIGNED NOT NULL DEFAULT 0,
PRIMARY KEY (id, party_id),
INDEX fk_categories_parent_category_idx (parent_id ASC),
UNIQUE INDEX name_key_UNIQUE (name_key ASC, party_id ASC),
INDEX fk_categories_party_idx (party_id ASC),
CONSTRAINT fk_categories_parent_category
FOREIGN KEY (parent_id)
REFERENCES categories (id)
ON DELETE NO ACTION
ON UPDATE NO ACTION,
CONSTRAINT fk_categories_party
FOREIGN KEY (party_id)
REFERENCES parties (id)
ON DELETE CASCADE
ON UPDATE CASCADE);
CREATE TABLE IF NOT EXISTS products (
id INT(10) UNSIGNED NOT NULL AUTO_INCREMENT,
party_id INT(10) UNSIGNED NOT NULL,
product_code VARCHAR(50) NOT NULL,
PRIMARY KEY (id, party_id),
UNIQUE INDEX product_code_UNIQUE (product_code ASC, party_id ASC),
INDEX fk_products_party_idx (party_id ASC),
CONSTRAINT fk_products_party
FOREIGN KEY (party_id)
REFERENCES parties (id)
ON DELETE CASCADE
ON UPDATE CASCADE);
CREATE TABLE IF NOT EXISTS product_category (
product_id INT(10) UNSIGNED NOT NULL,
category_id INT(10) UNSIGNED NOT NULL,
party_id INT(10) UNSIGNED NOT NULL,
PRIMARY KEY (product_id, category_id),
INDEX fk_product_category_product_idx (product_id ASC, party_id ASC),
INDEX fk_product_category_category_idx (category_id ASC, party_id ASC),
CONSTRAINT fk_product_category_product
FOREIGN KEY (product_id , party_id)
REFERENCES products (id , party_id)
ON DELETE CASCADE
ON UPDATE CASCADE,
CONSTRAINT fk_product_category_category
FOREIGN KEY (category_id , party_id)
REFERENCES categories (id , party_id)
ON DELETE CASCADE
ON UPDATE CASCADE);
Question
How can I setup the three-way association table correctly to avoid the risk of having an application layer assigning a product to a category without enforcing the party_id
?