Database Design – Schema for Parent Table Referenced in Multiple Child Tables

database-designnormalizationschema

I have three entities in my database: Category, SubCategory, and Product. Now the thing is SubCategory is dependent on Category, whereas Product can be dependent on SubCategory or directly either on Category.
How should I design the database for such a situation?

As of now, I have created these tables as below:

Category:

CategoryId
CategoryName
HasSubCategory

SubCategory:

SubCategoryId
CategoryId
SubCategoryName

Product:

ProductId
CategoryId
SubCategoryId
ProductName

Now, while creating a Product, if the selected Category's HasSubCategory is true then SubCategory would be selected by the user. Now consider a situation that the user selects a Category whose HasSubCategory is true, and based on the selected Category, SubCategories will be loaded and after selecting a SubCategory, the user submits the Product record. Now, what if the user modifies the SubCategory record which was selected in the Product, and changes its dependent Category. In this case, I need to update the Product record as well and change its Category as per the new Category selected in the SubCategory record.

Is there any better approach to handle such a situation or I shall manage like this only?

Best Answer

My approach in a model:

-- Categories and subcategories table
CREATE TABLE category (id INT PRIMARY KEY, 
                       name VARCHAR(255), 
                       parent_id INT,
                       FOREIGN KEY (parent_id) REFERENCES category (id));

-- Products table
CREATE TABLE product (id INT PRIMARY KEY,
                      name VARCHAR(255),
                      category_id INT,
                      FOREIGN KEY (category_id) REFERENCES category (id));

How it works: DEMO (MySQL used).

The queries are converted to procedures - only for simplifying their execution call.

If some section is empty we don't display it.

If we want to get the categories list with the info does they have subcategories we simply execute

SELECT *, EXISTS ( SELECT 1
                   FROM category t2
                   WHERE t1.id = t2.parent_id ) has_subcategory
FROM category t1;

Related Solutions

Database schema for a product with multiple categories and hierarchical categories

What you are proposing is a good solution for your requirement of M:N products to categories and hierarchical categories.

To avoid exposing yourself to numerous updates: You need to do two things to ensure that you don't have a lot of updates in your intersection table.

First, you need to be sure that your categories have a stable, persistent primary key.

Second, you need to link food items to leaf categories. Don't join Cherry to Red, Healthy, Fruit and Food - just join it to Red and Healthy. Your nested sets take care of all of the secondary (and higher level) associations.

Designing Simple Schema for Disaggregation of Demand Forecast

Okay, so here's the data model I came up with.

PRODUCT -- to store product information and maintain parent-child hierarchy

id  NUMBER  "Primary Key Not Null"                  
level_code  VARCHAR2    Not Null                    
name    VARCHAR2    Not Null                    
description VARCHAR2                        
parent_id   NUMBER  Foreign Key references PRODUCT(id)

ORDERS -- to store orders for products

id  NUMBER  "Primary Key Not Null"                  
prod_id     NUMBER  "Foreign Key references PRODUCT(id) Not Null"                   
order_type  VARCHAR2    "Not Null Default 'Default'"
order_qty   NUMBER  Not Null
order_date  NUMBER  Foreign Key references DATE_INFO(date_key)
delivery_date   NUMBER  "Foreign Key references DATE_INFO(date_key)
Check delivery_date >= order_date"

FORECAST -- to store forecast value for products (store value for higher levels, store value for lower levels after disaggregation from a parent)

id  NUMBER  "Primary Key Not Null"
product_id  NUMBER  "Foreign Key references PRODUCT(id) Not Null"
forecast_value  NUMBER  Not Null
week    NUMBER  "Foreign Key references DATE_INFO(date_key) Not Null"

DISAGGREGATION_RULES -- to store which method was used for disaggregating a value from a higher level to lower level and how much percentage got distributed to lower level

id  NUMBER  "Primary Key Not Null"
parent_product_id   NUMBER  "Foreign Key id references PRODUCT(id) Not Null"
child_product_id    NUMBER  "Foreign Key id references PRODUCT(id) Not Null"
method  VARCHAR2    Not Null                    
from_week   NUMBER  "Foreign Key references DATE_INFO(date_key) Not Null"
to_week NUMBER  "Foreign Key references DATE_INFO(date_key) Not Null Check end_week >= start_week"
percent_distribution    NUMBER  Not Null

DATE_INFO -- date dimension, has information about start date (has to be Saturday) and end date corresponding to the week in which a particular date falls

date_key    NUMBER  "Primary Key
Not Null"                   
full_date   DATE    Not Null                    
week_begin_date DATE    Not Null                    
week_end_date   DATE    Not Null

As for the bucket number .. I am calculating week start date (date on Saturday, in my case) with the following function

CREATE OR REPLACE FUNCTION get_week_start_date(v_bucket_num IN NUMBER)
  RETURN DATE
IS
  week_start_date DATE;
BEGIN
  SELECT (TRUNC(SYSDATE+2, 'IW')-2) + ((v_bucket_num-1) * 7)
  INTO week_start_date FROM dual;
  RETURN week_start_date;
END;

Best Answer

Related Solutions

Database schema for a product with multiple categories and hierarchical categories

Designing Simple Schema for Disaggregation of Demand Forecast

Related Question