1

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!

  • 1
    What is a feature? Is it an attribute, described by data? Maybe a few examples would help clarify. – Walter Mitty Aug 9 '18 at 11:13
  • It is an attribute. If you think about vehicles some features could be horsepower, colour, number_of_wheels... – Adrian Aug 9 '18 at 11:18
  • The first option is basically EAV, just using different words. – Michael Green Aug 9 '18 at 11:29
  • I think they are both variants on EAV. – Walter Mitty Aug 9 '18 at 22:49
  • On second thought, it might not be EAV. But it definitely stores metadata in user tables, and that carries the same downside as EAV. The underlying data model can change with no changes to database definitions. Great. But the data is no longer self describing. That means you have to manage it yourself, in the application. Just like the days before databases came along. – Walter Mitty Aug 10 '18 at 15:23
0

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.

  • Would it really make much of a difference separating the features by category? It adds one extra link table that I would not use if I just separate the features by product. – Adrian Aug 9 '18 at 9:44
  • 1
    If there are features that depends on the category but you don't create this table, how do you know which features to add to the product for a specific category? – EzLo Aug 9 '18 at 9:46
  • I don't need to filter by the features of the categories. I just need to filter by categories, ans since a product belongs to one category, as long as the feature is related to the product I get the result I need. It would be something like selecting all features for each product that belongs to a certain category. – Adrian Aug 9 '18 at 9:54
  • 1
    What happens when you need to add or remove a feature from a category? You will have to first find all products of that category then add/remove the feature from every one. It might seem OK but imagine you have millions of products; you will have to add/remove millions of records instead of just 1. Both designs will work, but I see this one more normalized and easier to maintain. – EzLo Aug 9 '18 at 10:00

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.