DBAsupport.com Forums - Powered by vBulletin
Results 1 to 7 of 7

Thread: Columns vs. rows

  1. #1
    Join Date
    Aug 2000
    Posts
    132
    Data design question - which takes up more space having several tables with many columns or one table with many rows?

  2. #2
    Join Date
    Oct 2000
    Location
    Saskatoon, SK, Canada
    Posts
    3,925
    It depends on the application that you are about to design. Row is a composition of columns. So I couldn't get what you are looking for.

    Sam
    Thanx
    Sam



    Life is a journey, not a destination!


  3. #3
    Join Date
    Nov 2000
    Location
    Baltimore, MD USA
    Posts
    1,339
    Here is a previous link that touched on that - seems just as relevant here:

    [url]http://www.dbasupport.com/forums/showthread.php?threadid=5155[/url]


    sambavan - also contains my thoughts on the singular code table, as alluded to in the other thread :) - just thought that was kinda coincidental.


    - Chris


  4. #4
    Join Date
    Aug 2000
    Posts
    132
    we sell products so we have a product table with attributes generic to all products - we add tables for specific types of products and their attributes eg cd_table which contains the attirubutes values of a compact disc (title, artist, label, etc.). My boss has recently redesigned his java code so changes to the java are minimized for adding new products. For the database this meant going from several tables with many columns to one table with many rows. I'm really just wondering what the storage implications are - not which is better.

  5. #5
    Join Date
    Nov 2000
    Location
    Baltimore, MD USA
    Posts
    1,339
    Sounds like your first design was the correct one - I'm assuming it was a subtype relationship between the Base table and the sub-type tables.

    If you are going to a single table with a lot of rows, then I'm assuming you decided to make some sort of generic set of columns, such as

    AttribName, AttribType, AttribNumericValue, AttribStringValue, AttribDateValue...

    ...and have multiple rows per entry. so, a CD has the same generic attribs as the rest, then the additional ones you mentioned. Instead of having a single record in the base table and a single record in the CD table, you now have that base record repeated multiple times, each with a separate attribute title, artist, label, etc.). in the other fields. This is called de-normalization and is a very bad idea for an OLTP database. Since you are increasing the rows in your table:

    - The table will grow
    - The indexes will grow
    - Queries will suffer
    - DML will suffer
    - Maintenance of the database will suffer

    Since you are duplicating a lot of fields multiple times:
    - You are introducing the possibilities for data inconsistencies
    - DML will suffer

    Since you are making generic fields:
    - You have lost the ability to do RI on these fields
    - You have made the model and data more confusing

    Since you are mixing the existence of attributes with the main data rows:
    - What do you do for a type that has no 'extra' attributes? You must now have an entry with blank attributes.
    - How do you control the requirement of an attribute value? If an artist attribute used to be a NOT NULL field, you used to be able to be positive that this value was filled. You now have no way to do that. They can delete the 'row' that represents just that attribute and you could not (easily or automatically) stop them.


    There's probably more, but that's off the top of my head. Your boss may want to *seriously* re-think this decision.

    Just my .02,

    - Chris

  6. #6
    Join Date
    Aug 2000
    Posts
    132
    Thx Chris for your .02 - I agree with your observations, from the database point of view this gives us more headaches. I hope that it seriously improves the java coding b/c I don't think I can get the powers that be to change their minds.

  7. #7
    Join Date
    Oct 2000
    Location
    Saskatoon, SK, Canada
    Posts
    3,925
    Point out to them the performance is going to suffer badly. And it would be next to impossible to tune the performance once it goes into production.

    Sam
    Thanx
    Sam



    Life is a journey, not a destination!


Posting Permissions

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


Click Here to Expand Forum to Full Width