Prev: (Object invalid or no longer set)
Next: Need a little help with syntax
From: tryit on 6 Nov 2009 12:13
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

From: John W. Vinson on 6 Nov 2009 13:55
On Fri, 6 Nov 2009 09:13:14 -0800 (PST), tryit <tryit.ca(a)gmail.com> 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]
From: tryit on 6 Nov 2009 16:07
On Nov 6, 10:55 am, John W. Vinson
<jvinson(a)STOP_SPAM.WysardOfInfo.com> wrote:
> On Fri, 6 Nov 2009 09:13:14 -0800 (PST), tryit <tryit...(a)gmail.com> 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
 | 
Pages: 1
Prev: (Object invalid or no longer set)
Next: Need a little help with syntax