Results 1 to 5 of 5
  1. #1
    Join Date
    Aug 2004
    Posts
    17

    To have a separate table for optional items?

    I have a database design question. Let's say I have a table with a list of people. Each person can have a number of associated items. The items are optional but their maximum number is predetermined.

    Theory of database design recommends having a separate table with those items, in which each item is related to its owner (a person) by a key. I think this is a good idea if the number of items is unknown. But if the max number of them is preset and is rather small (say, 5), is it more efficient to have 5 attributes for the optional items added to the main table? I think accessing all data items pertaining to a particular record would be faster in this case. There MAY be some space wasted, but do modern DBMSs preallocate space and leave it empty until optional items are assigned value?

    Thanks in advance

  2. #2
    Join Date
    Jul 2004
    Location
    UK
    Posts
    43
    This one relates to a super/sub type thread we had going.
    The vetrens were saying stick with the one table, have null values's isnt such a hit in your case, plus only 5 fields wont pup much strain on the DBMS. Querying would be alot easier in the single table deisgn..

    If people could be placed into subtypes, ie there are only a set number of combinations of people n' items, then you might have an argument to support tabling them off (purist view) otherwise go with whats easy.

  3. #3
    Join Date
    Aug 2004
    Posts
    17
    Are you referring to thread titled "RE: Boring" (http://www.dbforums.com/t1004407.html)? I'd like to read it.

  4. #4
    Join Date
    Jul 2004
    Location
    UK
    Posts
    43
    Yes that is the thread I'm referring to.

  5. #5
    Join Date
    Apr 2002
    Location
    Toronto, Canada
    Posts
    20,002
    roman2, don't worry about the wasted space for NULLs, it is minimal

    jwab, that was a gorgeous thread, wasn't it? it made me think!

    as for "items are optional but their maximum number is predetermined" be careful and ensure that they are all different, otherwise it might be better, if they are the same, to have a real one-to-many relationship

    example: persons table, one row per person --

    id, name, etc, homephoneno, cellphoneno, faxphoneno, ...

    there was a thread about phone numbers, too
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL

Posting Permissions

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