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

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.

Are you referring to thread titled "RE: Boring" (http://www.dbforums.com/t1004407.html)? I'd like to read it.

Yes that is the thread I'm referring to.

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