Same table or different

[Josh]

Both for an Inventory DB I'm working on, and for general knowledge for future,
wondering about the following situation -

Intentory Item (parts) table, approx 2000 records at this time, having the usual
fields; Vendor, Manufact., partNo., etc. For about 25-50 of these
records(parts/items), will also print separate labels which will use three
fields that are different. For example, while these 25-50 will have Description
field in common (among about 22 other fields in common), they will also have a
LabelDescription Field (a very shortened version of the description, to fit on
label), in addition to one other LabelNumber and Catagory which all the rest of
the records/parts/items won't have.

Because it seems illogical to have fields in which you know 98% will be blank,
should I make a separte table for these, using the 3 fields plus the PK key from
the Item Table? That doesn't seem right, either.

Thanks, Josh

 

[Rod Plastow]

Josh,

This is a 'classic' dilemma. In the old days when space was at a premium,
speeds were slower and compression techniques were less advanced the answer
would have been to create a separate table ('file' in those days). However
today everything is much more relaxed and modern compression techniques
(particularly for text fields) mean that although you may think you are
squandering 98% of the allocated space, in reality you are not.

So there is no definitive answer. If the additional fields/attributes
pertain to a sub class then there is a strong case for creating separate
tables. For example if you have a vehicle entity and have sub classes of
motorcycle, car, truck, trailer, etc. all with their own unique data then
separate tables may be the way to go. However your case is that an inventory
item may have additional, optional data; I would lean towards one table and
insist that it has to be 'proved' that I need two tables. After all dealing
with a single table is going to be less complex.

If you do choose two tables then design a query that joins the two tables
and use it as a view, don't deal with the tables directly.

No doubt you will get other and perhaps conflicting advice. This will just
further demonstrate that this is a dilemma.

Regards,

Rod