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

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

On Fri, 6 Nov 2009 09:13:14 -0800 (PST), tryit wrote:

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

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]

On Nov 6, 10:55*am, John W. Vinson
wrote:
On Fri, 6 Nov 2009 09:13:14 -0800 (PST), tryit wrote:
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

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]

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


Best,
Tom