database design logic

Dose up on your Ridalin. This one's for the patient.


I have the following dilemma. I need to organize data in the most efficient manner. These are the data specifications.

UserData - Table of Users (estimate of 2000 rows)

Each entry in UserData can create objects of "n" varieties (ObjectVariety[1], ObjectVariety[2], .., ObjectVariety[n]) (20,000 anticipated objects)

Each ObjectVariety[1-n] will have approximately 25 attributes that can be ascribed to it (SharedAttributes) (attributes will either be marked as True/False or have an integer value)

Each ObjectVariety[1-n] ALSO will have attributes unque to their Variety (ObjectAttributes[1-n])

Now, I have two ways to organize this database - I'm not sure if either of them is the best way to do it

==== OPTION 1 aka LONG APROACH: ====

TABLE NAME: UserData

TABLE NAME: Object
Foreign Key points to Object Varieties

TABLE NAME: ObjectVarieties

TABLE NAME: ObjectAttributeDescription
Description of EachAttribute Available
Foreign Key for each entry points to ObjectVarieties or is 0 implying its a shared attribute.

TABLE NAME: ObjectAttributes
Value of Each Attribute
Foreign Key points to ObjectAttributeDescription
Foreign Key poinst to the Object itself

==== OPTION 2 aka WIDE Approach: ====

TABLE NAME: UserData

TABLE NAME: Object
foreign key pointing to VarietyIndex

TABLE NAME: VarietyIndex
A column pointing to a tablename (ex: Variety1)

TABLE NAME: AttributesForVariety1
around 25 columns for each attribute
foreign key to Object

TABLE NAME: AttributesForVariety2
around 25 columns for each attribute
foreign key to Object

TABLE NAME: AttributesForVariety3
around 25 columns for each attribute
foreign key to Object

TABLE NAME: SharedAttributes
around 25 columns
foreign key to Object
foreign key to ObjectAttributeDescription

TABLE NAME: ObjectAttributeDescription
Description of Attribute [varchar]


----------------------------------------------------------------
Please tell me if any of these options is at all viable. Please keep in mind about 2000 users. About 10 objects per user. About 5 varieties. Each object will have around 50 Attributes (SharedAttributes + Attributes unique to Variety).

If you can point me in the right direction, i would be very grateful.

Don't do the multiple table approach.

Another option is using a lookup table:Of course, my suggestion id dependent on my reidalin working

helpful indeed. thanks.

I would not use your 2nd approach. It becomes a nightmare when you need that new attribute 6 months later.