Results 1 to 4 of 4
  1. #1
    Join Date
    Jun 2010
    Posts
    18

    Unanswered: Simple question about tables/best practice

    Hi folks, I joined this forum to end a friendly debate between myself and a colleague. I'm a programmer, my colleague is the DBA.

    We have a table of parts, some of which are electronic/computer parts. A request has come in to add a column/field to this table to store "firmware" information. Generally, this column will only get data for about 5% of the records in this table. The rest of the rows will have a null for this column as they don't have firmware.

    So, do we add a field to this table of parts called "firmware" - with a bunch of nulls for the items that don't have firmware (my idea)? OR, Do we create a separate table that stores firmware information, and join it to the parts table based on the part's ID (his idea)?

    Let's also say that this table has about 10,000 rows. DB is Oracle...

    Hope this makes sense, thanks for the help!

  2. #2
    Join Date
    Jun 2004
    Location
    Liverpool, NY USA
    Posts
    2,509
    If you have only one possible firmware code I would add the column to the existing table. Nulls take no space in row or index. If there is any possible way you might have more then one firmware per part or you want to keep history I would add the second table.
    Bill
    You do not need a parachute to skydive. You only need a parachute to skydive twice.

  3. #3
    Join Date
    Jun 2003
    Location
    West Palm Beach, FL
    Posts
    2,713

    It depends...

    What if you do add a column called "firmware" to the table and later on someone requests you add another column "transfer_rate" and later add "Memory_type" and later ... etc ...

    The better would be to create a table "properties" where you can add any property/value combination.

    Just My 2c.
    The person who says it can't be done should not interrupt the person doing it. -- Chinese proverb

  4. #4
    Join Date
    Jun 2004
    Posts
    796
    Provided Answers: 1
    As an ex-Designer, I agree with beilstwh and LKBrwn_DBA - create a new table. Any further modifications to the schema will be much 'cleaner', and having a new table will retain the concept of relational data.
    90% of users' problems can be resolved by punching them - the other 10% by switching off their PCs.

Posting Permissions

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