#1
  1. No Profile Picture
    Contributing User
    Devshed Newbie (0 - 499 posts)

    Join Date
    Mar 2003
    Location
    Cleveland OH USA
    Posts
    44
    Rep Power
    17

    Question Better to bloat table with mostly unused columns, or create new table?


    Suppose you have a table with a large number of rows (e.g., M = 10,000), but now you need to unexpectedly add N more columns to the table to accommodate a very small number of special-case rows in the table (e.g., N = 6).

    The other option, of course, would be to leave the existing table alone, and create a new table just for the special cases. The new table would have the extra N columns but the existing table wouldn't.

    The former option would bloat the original table with M*N nulls for all the non-special-case row entries.

    The latter option would involve a lot of extra work on the business logic side of things to make sure the overall software system recognized the old table and the new, very small table (for the special cases).

    Which would you do? Would the bloat incurred to the original table by Option #1 slow the system down?
  2. #2
  3. Lord of the Dance
    Devshed Specialist (4000 - 4499 posts)

    Join Date
    Oct 2003
    Posts
    4,206
    Rep Power
    2012
    Number of rows and columns does not say much, without knowing what datatypes you work with. Is it int? dates? Blobs?
    How often does it read and/or update the tables?

    As you only need the new columns for "very small number", I would put them into its own table.
    The extra works on business logic is already required as you add more information, although an update will require an extra step with the new table.

IMN logo majestic logo threadwatch logo seochat tools logo