Store similar types of records all in one table or separate tables?

[tryit]

Suppose you want to have four different types of records.

Each of these records have numerous fields in common, and a few fields
that are unique to each type of record.

Most of the fields are related to other tables, but a few are simply
text fields or Booleans.

Which is better?:

Keeping track of all 3 types of records in a single table.

Or

Creating separate tables for each type of record.

Is one solution clearly better or is it just a matter of opinion?

Thanks in advance,
Tom

[John W. Vinson]

You can get into some fine old quasi-religious arguments over this issue. The
"purists" would say that this is a case of Subclassing, one of the few
instances where one-to-one relationships are appropriate. Let's say you have a
table of ComputerComponents; Monitors have width, height, resolution, etc.,
disk drives have diameter, capacity, speed, cabletype, etc. - but these
attributes only apply to the particular category.

Using subclassing you would have one master table of ComputerParts, with the
common fields; it would be related one-to-one to a table of Monitors (with the
monitor-specific fields), and to a table of Diskdrives (with those fields),
etc. You would need some programmatic (not referential integrity) constraint
to ensure that you don't put a record in the Monitors table when the part in
question is a disk drive!

Alternatively you can use a wider table and leave the irrelevant fields NULL.

Both methods do work, and do have their place; which is better depends both on
the application (how MANY subclasses? how many fields each? how will the data
be used?) and on the biases and preferences of the developer.
--

John W. Vinson [MVP]

[tryit]

On Nov 6, 10:55 am, John W. Vinson
<jvinson@STOP_SPAM.WysardOfInfo.com> wrote:

Thank you, John. You've given me food for thought.

Best,
Tom