Design Help
-
I think I posted this in the wrong forum first...
I have a project that consists of members and categories. There's a main category, that has associated sub-categories. The subcategories will have fields based on the main category. For instance, if the main category is shoes, the sub-cat might have size,color,brand; but if the main category is bikes, the sub-cat might have make,height,type,color.
The members fill out the categories based off their selections.
I tried creating a members, maincat & subcat tables, but I couldn't adjust for the varying fields. I then thought I'd make a table for each main category type, but I couldn't figure out how to store the member selections or even present the fields dynamically.
I'm hoping someone can provide me with a solid database structure that I can use, and build this project off of, or at least some ideas to get me going.
Thanks
Pete
-
will these subcategories have values? if so, you are veering dangerously close to the EAV model, which is a nightmare IMO.
quantities such as make, height, type, and color do not want to be stored in the same column of a table.
read this for an amusing critique of the EAV: http://weblogs.sqlteam.com/davidm/articles/12117.aspx
---------------------------------------
elsasoft.org -
The only real values to the subcategories would be their name and ID. The maincategories all have different fields in which I have to figure out how to store the data into.
-
you could try somtin like this:
Members(Mem_ID,...........) Main Table
Item_Type(Type_id (S) (B), Item_Types) Lookup_Table> (S) shoes,(B) bikes
Item_Value(Item_Value_Id,Mem_ID,Type_id, IV_ID, Item_Attributes_Values)
Attributes_Values(AV_id, Type_ID, IV_Values) Lookup
>>lookup table Contains all possible Item Attributes arranged by Item Type
this should do it -
Umm, don't let these guys scare you away from an EAV model. I use them all the time and I've never had a problem. If you just sit still for 15 minutes and read a little, they are common sense.
Basically, for your design you do need a small EAV model. You'll need 4 tables to perform this. I can design this whole structure in under an hour. I'm just not sure how to post it :P.
1.) Categories
a.) ID
b.) ParentID - points to it's Parent CategoryID
c.) Name
2.) Attributes
a.) ID
b.) Name
c.) DataType - NOTE, this is NOT a SQL data type, just something you can use for you're application so that it knows what is expected to go in the values for this attribute. It should probably be a TinyInt which relates to an Enum in your application (ex. 0 = Text, 1 = Number, 2 = Decimal, 3 = Date, 4 = Time, 5 = DateTime, 6 = whatever).
3.) CategoryAttributes - Cross-reference table between Attributes and Categories.
a.) CategoryID
b.) AttributeID
4.) CategoryValues - this stores all the values for each Sub-Category
a.) CategoryID
b.) AttributeID
b.) Value (sql_variant) - Nullable, used for the majority of attributes
c.) ValueBinary (image or varbinary(MAX)) - Nullable, only used if you need to store an Image
d.) ValueText (text or varchar(MAX)) - Nullable used for maybe a long description
Example:
(Categories)
1 - Shoe
2 - Bike
3 - Schwinn (ParentID = 2)
4 - Nike (ParentID = 1)
(CategoryAttributes)
Row1 (CategoryID = 1, AttributeID = "Color")
Row2 (CategoryID = 1, AttributeID = "Size")
Row3 (CategoryID = 2, AttributeID = "Wheel Size")
Row4 (CategoryID = 2, AttributeID = "Color")
(CategoryValues)
Row1 (CategoryID = 3, AttributeID = "Color", Value = "Red")
Row2 (CategoryID = 3, AttributeID = "Size", Value = "10")
Now, here's how it's used. You will insert all your Categories and SubCategories into the Categories table. A Sub-Category is denoted by the fact that it's "ParentID" is NOT NULL. You ONLY need to setup CategoryAttributes on the Root-Level Categories (i.e. any Category where ParentID IS NULL).
The easiest way to implement all this from a WebSite point of view is the new ASP.NET 2.0 DetailsView control. This will allow you to dynamically define different controls for each Attribute (ex. the colors should be an INT with a ColorPicker drop-down box).
If you need to see how to implement pre-defined Lookups per attribute, that is easily accomplished as well.
Good luck, I hope this helps.
Viewing 5 posts - 1 through 4 (of 4 total)
You must be logged in to reply to this topic. Login to reply