Modelling a database for products, categories and features

database-design

Good day everyone. I am building a database for an application and I have thought of two different approaches for achieving the same result, and I would like your opinion.

Is basically the classical product database, so at the moment I have products, each of theme belonging to a category. Each product has different features but the products that fall in the same category all share some common features.

At the moment I thought of this two structures:

  • A products table, just to store the name.
  • A product_categories table, to store the categories, and an intermediate table to link both.
  • A features table, to store the different features.
  • A product_features to link the product with it's features and store their value.

Since the products that fall on the same category have some shared features, I also thought of extracting the common features and creating a table for each product, so the result would be something like this:

  • A table for each product, to store their name and common features.
  • A features table, to store the different features that are not common.
  • A product_features to link the product with it's features and store their value.

I believe the first structure will be easier to maintain if I need to add newer products down the line as I would not need to create any new tables, but I kind of feel that the second approach would be more correct.

Let me know your thoughts!

Best Answer

If a category has related features then you have to link them independently from product. If a feature is only present through a category then you can omit the link between feature and product. If not, you have to link feature with both category and product, or else you might have inconsistencies between a product category's features and the actual features for that product.

Assuming that a category has a set of features and a product might have specific features (aside from those from it's category), I would suggest the following scheme:

CREATE TABLE Feature (
    FeatureID INT PRIMARY KEY,
    Name VARCHAR(100),
    Description VARCHAR(1000))

CREATE TABLE Category (
    CategoryID INT PRIMARY KEY,
    Name VARCHAR(100),
    Description VARCHAR(1000))

CREATE TABLE Product (
    ProductID INT PRIMARY KEY,
    Name VARCHAR(100))

-- Each product has a set of categories (N to M)
CREATE TABLE CategoryByProduct (
    ProductID INT,
    CategoryID INT,
    PRIMARY KEY (ProductID, CategoryID),
    FOREIGN KEY (ProductID) REFERENCES Product (ProductID),
    FOREIGN KEY (CategoryID) REFERENCES Category (CategoryID))

-- Each category has a set of features (N to M)
CREATE TABLE FeatureByCategory (
    CategoryID INT,
    FeatureID INT,
    PRIMARY KEY (CategoryID, FeatureID),
    FOREIGN KEY (CategoryID) REFERENCES Category (CategoryID),
    FOREIGN KEY (FeatureID) REFERENCES Feature (FeatureID))

-- Each product might have additional features aside from the one's from it's category (N to M)
CREATE TABLE FeatureByProduct (
    ProductID INT,
    FeatureID INT,
    PRIMARY KEY (ProductID, FeatureID),
    FOREIGN KEY (ProductID) REFERENCES Product (ProductID),
    FOREIGN KEY (FeatureID) REFERENCES Feature (FeatureID))

So if you want to retrieve a specific product's features you have to traverse through it's categories and particular features:

-- Features by product's category
SELECT
    P.ProductID,
    P.Name,
    F.FeatureID,
    F.Name
FROM
    Product P
    LEFT JOIN CategoryByProduct CP ON P.ProductID = CP.ProductID
    LEFT JOIN FeatureByCategory FC ON CP.CategoryID = FC.CategoryID
    LEFT JOIN Feature F ON FC.FeatureID = F.FeatureID
WHERE
    P.ProductID = 123 -- Particular product ID

UNION ALL

-- Stand-alone features
SELECT
    P.ProductID,
    P.Name,
    F.FeatureID,
    F.Name
FROM
    Product P
    LEFT JOIN FeatureByProduct FP ON P.ProductID = FP.ProductID
    LEFT JOIN Feature F ON FP.FeatureID = F.FeatureID
WHERE
    P.ProductID = 123 -- Particular product ID

Following this approach, you just have to add a category to a product to "inherit" it's features, and you have the possibility to add custom features to particular products. Also changing the features of a category will automatically impact all products related to that category.

Related Question