Hi,

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!

Thanks,

Luong Nguyen.

Try an 'extension' table for each Item. The application should be designed to insert into the correct extension table for that item type.

ITEM
item_id, item_desc, item_type

ITEM_CD
item_id, cd_artist, cd_title

ITEM_BOOK
item_id, book_author, book_title

etc....

If on the other hand their are so many different item types, and these will change over time it may be better to have an ATTRIBUTES table, where any item can have multiple rows in that ATTRIBUTES table. This will make your queries and forms more difficult to design.