|
|
Posted by Geoff Muldoon on 06/05/07 06:54
MadDiver says...
> I need to create an application that handles several product types/
> categories. Each product type can have totally different fields to
> describe it. For instance a car would have Year, Make, Model, and
> Miles, whereas a house would have Year, Address, SQ Feet, Amenities,
> etc.
> What would be the best DB schema?
The following basic design will achieve what you want, but it is an
inefficient way of doing things as you have to store all attribute values
in a (largish) character field, even if they are numbers/dates/etc.
But if you must, then ....
Table of PRODUCT_TYPE
- primary key PT_ID
PT_ID PT_DESC
1 Car
2 House
Table of PRODUCT_TYPE_ATTRIBUTE
- primary key PTA_ID, foreign key PT_ID
PTA_ID PT_ID SORT_ORDER PTA_DESC
1 1 1 Year
2 1 2 Make
3 1 3 Model
......
7 2 1 Year Built
8 2 2 Street Address
9 2 3 Town/City
Table of PRODUCT
- composite primary key on P_ID, PTA_ID, foreign key PT_ID, depending on
what database a constraint based on PT_ID/PTA_ID would be good
P_ID PT_ID PTA_ID P_VALUE
88 1 2 Porsche
88 1 3 Carerra
88 1 1 2006
.....
93 2 7 1980
93 2 9 Nerdsville
93 2 8 10 Somewhere Street
If you want to make some fields mandatory/optional, add another flag
column to the PRODUCT_TYPE_ATTRIBUTE table.
GM
Navigation:
[Reply to this message]
|