mardi 28 avril 2015

Database: A design pattern for category features

I work for a system which has these tables:

products , categories , features

User wants to add some feature like color dynamically.

Which of this following ways are true?

First one:

Product             category                feature
----------------    ----------------        ----------------------------
pro_id (PK)         cat_id (PK)             feature_id (PK)
pro_name            cat_name                fea_name
cat_id (FK)         feature_id (FK)

We add a feature like color, then in category we choose that feature and add it to category table. Then we access for all product in that category.

But there is a pleonasm. Because when two category wants to have color feature, we must repeat feature_id column in category table.

Second one:

Product             category                name_feature
----------------    ----------------        ----------------------------
pro_id (PK)         cat_id (PK)             name_feature_id (PK)
pro_name            cat_name                cat_id (FK)
cat_id (FK)                                 color
                                            ...

When we enter a category, we create a table for that category. For example laptop category with color feature. We create a table called laptop_feature with features in column.

Which way is good? (Categories is limited)

Aucun commentaire:

Enregistrer un commentaire