Design Database table for Product with Variable number of Attributes

Asked By 0 points N/A Posted on -
qa-featured

Hi,

I have a requirement to create a denormalised database, to store the information about the products to be sold on an e-Commerce site.  I need to store all the product attributes in the same database table.
 
The challenge is that, different Products can have different number of attributes and the maximum number of attributes that a product can have is not known in advance. For example, Product A can have 10 attributes while Product B may have 15 attributes and so on.
 
Please let me know if anyone is aware of any approach for optimal design of this type of database. Any pointers will help.
 
Regards.
SHARE
Answered By 0 points N/A #84146

Design Database table for Product with Variable number of Attributes

qa-featured

Well, if you want to store every information about a product in a single table there is only one way, that your table should have common attributes for all products. However, you can add the attributes in the table through programming at run time, but the records in this type of table will occupy more space in your table and the extra space is a wastage for the table. This type of table causes many errors about data retrievals.

For example

If you store information about your product A which has 10 attributes and product B which has 15 attributes in a single table then 5 attributes for product A will be empty.

So you have to think about the common attributes for every product, if you want to store information about your product in a single table.

Besides this I want to give you one more suggestion, that you should use two or more tables for storing information. There are several techniques available to retrieve data from different tables at the same time. Like you can join records from different tables as a query.

Hope this helps you.

 

Related Questions