Results 1 to 3 of 3
  1. #1
    Join Date
    Feb 2004
    Posts
    134

    Unanswered: 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:

    Code:
    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?

    Code:
    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?

    Code:
    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 B

  2. #2
    Join Date
    Nov 2002
    Location
    Jersey
    Posts
    10,322
    http://www.databaseanswers.com/normal_forms.htm
    http://databases.about.com/cs/specif...malization.htm
    http://portal.acm.org/citation.cfm?i...IDE&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
    Brett
    8-)

    It's a Great Day for America everybody!

    dbforums Yak CorralRadio 'Rita
    dbForums Member List
    I'm Good Once as I ever was

    The physical order of data in a database has no meaning.

  3. #3
    Join Date
    Feb 2004
    Posts
    134
    Originally posted by Brett Kaiser
    http://www.databaseanswers.com/normal_forms.htm
    http://databases.about.com/cs/specif...malization.htm
    http://portal.acm.org/citation.cfm?i...IDE&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

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •