I really need a debate! Type - Attributes vs Super Type - Sub Types

I have extensively revied both of the design methodologies and I cannot come up with a single clear reason to use one over the other!

Type - Attributes is where you have a table holding the type categories, type, a table holding the type attributes expected and then a table holding the type attribute value:

tbAutombbileCategories
CategoryID | Category
----------
1 | Car
2 | Truck
3 | Motorcycle

tbAutomobileAttributes
AttributeID | fkCategoryID | Attribute
--------------
1 | 1 (car) | Doors
2 | 2 (truck) | Cab
3 | 2 (truck) | Capacity

tbAutomobile
VIN | Category | Make | Model
------------
1 | 1 | Honda | Accord
2 | 2 | Ford | F150

tbAutomobileAttributeValues
fkVIN | fkAttributeID | Value
----------
1 | 1 | 2
2 | 1 | 0
2 | 2 | 1000

Now the above sure is flexible in the sence that a type of automobile can be added without affecting the database schema, but was if some attributes do not take a numeric value? How do you handle computations on attributes specific attributes? Why would I use this structure as opposed to the super type - sub type as shown below?

tbCategories
CategoryID | Category
--------
1 | Cars
2 | Trucks

tbAutomobile (Super Type)
VIN | fkCategoryID | Make | Model
------------
1 | 1 |Honda | Accord

tbCars
fkVIN | Doors |
------
1 | 2

tbTrucks
fkVIN | Cab | Capacity
--------
2 | 0 | 1000

Now, adding new sub types probably isn't very flexible but, now you can specify data types for each attribute instead of using sql_variant, which by the documentation cannot be used in aggregate functions and may render poor result when used with ADO.

Regardless of the method used, alot of back end coding is required for computations, what table to send the attributes, etc...

Can anyone please help me clarify. What method is best and why. So far I am leaning for option 2. More work but seems to be more flexible in the sence of customization of each datatype.

E.G., what if you wanted to specify attributes about the cap that can be supplied to trucks?

tbTrucks
fkVIN | Cab | Capacity | fkCapID
------------
2 | Y | 1000 | 1

tbCaps
CapID | Vendor | Price | et...

Any thoughts at all? I thought this would have been a pretty damn hot topic!

Mike Bhttp://www.databaseanswers.com/normal_forms.htm
http://databases.about.com/cs/specificproducts/g/normalization.htm
http://portal.acm.org/citation.cfm?id=809996&dl=GUIDE&coll=GUIDE
http://databases.about.com/library/weekly/aa091601a.htm

This one is cool

www.bus.tu.ac.th/usr/surat/is304/normal.ppt

This one too...talksing about supertype and subtype

facweb.cs.depaul.edu/yele/Course/IS421/ S6/H10%20ERD%20Advanced%20Concepts.ppt

Used to be Primary Entity and Attributive Entity...whatever|||Originally posted by Brett Kaiser
http://www.databaseanswers.com/normal_forms.htm
http://databases.about.com/cs/specificproducts/g/normalization.htm
http://portal.acm.org/citation.cfm?id=809996&dl=GUIDE&coll=GUIDE
http://databases.about.com/library/weekly/aa091601a.htm

This one is cool

www.bus.tu.ac.th/usr/surat/is304/normal.ppt

This one too...talksing about supertype and subtype

facweb.cs.depaul.edu/yele/Course/IS421/ S6/H10%20ERD%20Advanced%20Concepts.ppt

Used to be Primary Entity and Attributive Entity...whatever
Your last link is invalid.

Anyway, why did you post all the articles on BCNF? Aren't both examples I have illustrated normalized? If they aren't what rules are broken?

I am a newbie, so any explanation would be appreciated!

Also, what method would you use Brett?

Mike B