Database Design – Modeling a Three-Way Association with Optional Relation

database-designmariadbrelations

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 a party.
- Each category is defined by exactly one party.
- Each party may define more than one category.
Product is classified by a party.
- Each product is classified by exactly one party.
- Each party may classify more than one product.
Product is classified in a category by a party.
- Each product may be classified in more than one category.
- More than one product may be classified in the same category.
- A product is classified by a party in a category, then that category is defined by that party.

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?

Best Answer

The party_id for a Category must match the party_id for a Product in order to relate.

Well, according to your wording of the problem, there is no need for party_id in Category.
The confusion likely stems from imprecise wording, you are blending predicate and constraints in one sentence.
For example:

A product is classified by one-to-one party.

A party classifies one-to-many products.

Can be worded more clearly:

Product is classified by party.
Each product is classified by exactly one party.
Each party may classify more than one product.

This wording then directly leads to a usable model (predicate, constraints).

Option 1

-- Party PTY exists.
--
party {PTY}
   PK {PTY}

Each product is classified by exactly one party; for each party, that party may classify more than one product.

-- Product PRO, classified by party PTY exists.
--
product {PRO, PTY}
     PK {PRO}

FK {PTY} REFERENCES party {PTY}

-- Category CAT exists.
--
category {CAT}
      PK {CAT}

For each product, that product may be classified in more than one category. For each category, more than one product may be classified as belonging to that category.

-- Product PRO is classified in category CAT.
--
product_category {PRO, CAT}
              PK {PRO, CAT}

FK1 {PRO} REFERENCES product  {PRO}
FK2 {CAT} REFERENCES category {CAT}

Option 2

It may be that a product is known before the matching party is known. Then a variation:

-- Party PTY exists.
--
party {PTY}
   PK {PTY}

-- Product PRO exists.
--
product {PRO}
     PK {PRO}

-- Product PRO is classified by party PTY.
--
product_party {PRO, PTY}
           PK {PRO}

FK1 {PRO} REFERENCES product {PRO}
FK2 {PTY} REFERENCES party   {PTY}

-- Category CAT exists.
--
category {CAT}
      PK {CAT}

-- Product PRO is classified in category CAT.
--
product_category {PRO, CAT}
              PK {PRO, CAT}

FK1 {PRO} REFERENCES product_party {PRO}

FK2 {CAT} REFERENCES category {CAT}

EDIT

After few comments:

Category is defined by a party.
- Each category is defined by exactly one party.
- Each party may define more than one category.
Product is classified by a party.
- Each product is classified by exactly one party.
- Each party may classify more than one product.
Product is classified in a category by a party.
- Each product may be classified in more than one category.
- More than one product may be classified in the same category.
- If a product is classified by a party in a category, then that category is defined by that party.

Option 3

Party must exists before category and product.

-- Party PTY exists.
--
party {PTY}
   PK {PTY}


-- Product PRO, classified by party PTY exists.
--
product {PRO, PTY}
     PK {PRO}
     SK {PRO, PTY}

     FK {PTY} REFERENCES party {PTY}


-- Category CAT, defined by party PTY exists.
--
category {CAT, PTY}
      PK {CAT}
      SK {CAT, PTY}

      FK {PTY} REFERENCES party {PTY}


-- Product PRO is classified in category CAT
-- by party PTY.
--
product_category {PRO, CAT, PTY}
              PK {PRO, CAT}

      FK1 {PRO, PTY} REFERENCES product  {PRO, PTY}
      FK2 {CAT, PTY} REFERENCES category {CAT, PTY}

Option 4

If product and category can exist independently of party.

-- Party PTY exists.
--
party {PTY}
   PK {PTY}


-- Product PRO exists.
--
product {PRO}
     PK {PRO}


-- Category CAT exists.
--
category {CAT}
      PK {CAT}


-- Product PRO is classified by party PTY.
--
product_party {PRO, PTY}
           PK {PRO}
           SK {PRO, PTY}

      FK1 {PRO} REFERENCES product {PRO}
      FK2 {PTY} REFERENCES party   {PTY}


-- Category CAT is defined by party PTY.
--
category_party {CAT, PTY}
            PK {CAT}
            SK {CAT, PTY}

      FK1 {CAT} REFERENCES category {CAT}
      FK2 {PTY} REFERENCES party    {PTY}


-- Product PRO is classified in category CAT
-- by party PTY.
--
product_category {PRO, CAT, PTY}
              PK {PRO, CAT}

                 FK1 {PRO, PTY} REFERENCES
      product_party  {PRO, PTY}

                 FK2 {CAT, PTY} REFERENCES
      category_party {CAT, PTY}

Note:

All attributes (columns) NOT NULL

PK = Primary Key
AK = Alternate Key   (Unique)
SK = Proper Superkey (Unique)
FK = Foreign Key

Related Solutions

Sql-server – How to model and enforce constraints on a categorical item contained in a collection

Categories and Buckets.

create table categories
(
  category_id int primary key
);

create table buckets
(
  bucket_id int primary key
);

Depending on how to interpret "an item may belong to at most 1 bucket" you need to do some different things with Items.

An item has to belong to one bucket.

create table items
(
  item_id int primary key,
  category_id int not null references categories(category_id),
  bucket_id int not null references buckets(bucket_id),
  unique (category_id, bucket_id)
);

If an item may belong to one bucket but does not have to you need to allow for null values in bucket_id and use a filtered index as the unique constraint.

create table items
(
  item_id int primary key,
  category_id int not null references categories(category_id),
  bucket_id int references buckets(bucket_id)
);

create unique index uq_items on items(category_id, bucket_id) where bucket_id is not null;

There is a collection of items where each item belongs to 1 category.

category_id as a foreign key in items.

an item may belong to at most 1 bucket.

bucket_id as a foreign key in items.

There is a collection of buckets which may contain at most 1 item from each category.

A unique constraint on bucket_id and category_id in items.

SQL Server – Database Design for an Online Baby Shop and Toy Shop

Your design commonly looks good except few mistakes.

1) You had used a wrong field in order to establish a relation between ProductCategory and Product. You have to use ProductCategoryID in Product table instead of using ProductCategoryName. Howeever, some products may belong to more than one product category.
If this is a valid case for your project, you should add additional table something like "ProductVsProductCategory" which will allow you to establish a "many to many" relation between Product and ProductCategory tables if you intend to add some products into multiple categories.

Basic structure of ProductVsProductCategory might look like following:

ProductVsProductCategoryID (primary key)

ProductID (refers to Product table)

ProductCategoryID (refers to ProductCategory table)

2) OrderProductName field in the OrderDetail table is unnecessary. You have a relation to Produt table and you can access ProductName from there anyway. You can cancel it.

3) OrderNumber in the Order table is unnecessary if it is not a special value rather than unique identity number. You can use OrderID field as an identity of the order.

4) Similary, OrderQuantity and OrderTotalPrice fields might be unnecessary if you have not special reason to place them there. You can calculate order quantity and total price values by means of using basic sql statements that sums related OrderDetail rows.

There are many additional tables can be added depending on your project requirements. I also recommend you to follow MSDN articles and specially CodeProject in order to improve your abstraction skills.

Business rules

EDIT

Design proposals

SQL based on latest design

Question

Best Answer

Option 1

Option 2

EDIT

Option 3

Option 4

Related Solutions

Sql-server – How to model and enforce constraints on a categorical item contained in a collection

SQL Server – Database Design for an Online Baby Shop and Toy Shop

Related Question