Unanswered: How to design DB to support adding extra attributes
I'm learning to develop an online webstore. The stores sell numerous different types of item. Each type of item requires different set of attributes (i.e different list of specifications). Certainly they have a common set of attributes such as Name, Price and Available quantity.
Specifically, if I sell 3 types of items: Electronics, Clothes and Foods, I need to add attributes that are specific to Electronics such as Power, Voltage... These attributes are not applicable for Clothes or Foods. And for Clothes I surely need other specific info such as Size, Material...
Hence the problem is how to freely add new attributes for an item and the solution should be applicable for a Relational DB.
A tentative solution I have is to create two tables: Items, ItemInfo tables. Items will store common attributes and extra info goes to ItemInfo table. The structure of ItemInfo is: (id_item,attr_name,attr_value). Then to gather info for an item, I need to do a join. The bad thing about this design is the join return multiples with each row for 1 extra attribute. This is not nice for my Java code to parse the result and populate object. It also wastes bandwidth as common attributes will be repeated on every row of the result set.
Sorry for writing so long. I just want to explain it carefully.
Any idea is welcomed!
What you can do, which is absolutely the worst practice (heh and I am recommending it) is to place more than one value in a field. You can have an SpecialFields field which contains virtual fields like what you mentioned (ie Power/Voltage). In order to redeem myself I would suggest you serialize the information before placing it in a field... (this way its actually only ONE value in the field and not multiple, which I don't see being terrible)
The other alternative (more viable imho) is to create a table for each type of item you might sell... then you can create an item table or something and only store common attributes and join the rest.