Results 1 to 8 of 8
  1. #1
    Join Date
    Mar 2006
    Posts
    36

    One big table or many related? Whats the best choice??

    I have to collect a lot of data for an entity.
    I have decided to split the table up into sub-tables and form relationships all with the same primary key to the parent. Is it better to do this , or should I try and store as much data as possible in as few tables as possible. My only concern is that some tables may have 20 or so columns if I use few big tables. is that OK?

  2. #2
    Join Date
    Sep 2002
    Location
    UK
    Posts
    5,171
    20 columns sounds pretty small to me - unless you are using a "toy" DBMS? What DBMS are you using?

  3. #3
    Join Date
    Mar 2006
    Posts
    36

    SQL Server 2005

    SQL Server 2005 (not express). Just concerned about managing them afterwards really.

  4. #4
    Join Date
    Sep 2002
    Location
    UK
    Posts
    5,171
    SQL Server should handle 20 columns very comfortably I'm sure. I wouldn't consider vertical partitioning (which is what you are proposing) merely to reduce the number of columns of a 20 column table - a 200 column table maybe.

  5. #5
    Join Date
    Mar 2006
    Posts
    36

    Thanks for the tip mate

    Sounds like a good idea. At the moment, my diagram looks like a bit of a birds nest. I'm going to reduce the number of tables. Thanks for the advice!

    To add to that, would I be correct in saying that any one-to-one relationships I have with the parent can be removed and those attributes from the child table placed in the parent table and the child table could be discarded?
    Last edited by grooverinthesouth; 03-24-06 at 08:24.

  6. #6
    Join Date
    Mar 2006
    Posts
    36
    To add to that, would I be correct in saying that any one-to-one relationships I have with the parent can be removed and those attributes from the child table placed in the parent table and the child table could be discarded?

    I currently have 51 tables for this entity
    Last edited by grooverinthesouth; 03-24-06 at 08:29.

  7. #7
    Join Date
    Sep 2002
    Location
    UK
    Posts
    5,171
    They could, if you wish. OTOH, if there is a child table that has many columns and most parent rows don't have such a child, then you may prefer to keep all those rarely-used columns out of the parent table. But I'd err in favour of the simpler approach (one table, may columns) unless there is a compelling reason (e.g. poor performance) to do otherwise - and unless you can verify that splitting into more tables reduces that problem.

    This is very much a trade-off between simplicity on the one hand and performance on the other - there is no one right answer.

  8. #8
    Join Date
    Mar 2006
    Posts
    36
    Awesome. Thanks a lot for your help. This dbforum is a really good source of info.

Posting Permissions

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