Need clairification

I am currently reading a book entitled "Database Design for Mere Mortals" and
the author mentions using subset tables. However he does not fully explain
them nor how they are used. Can someone please expound on this topic further?

The sample that he gives is for an inventory database. The main table is
acalled Items. Linked to this are three 1:1 subset tables called Software,
Office Furniture, and Office Equipment.

On Sat, 9 May 2009 17:24:01 -0700, John F
wrote:

I am currently reading a book entitled "Database Design for Mere Mortals" and
the author mentions using subset tables. However he does not fully explain
them nor how they are used. Can someone please expound on this topic further?

The sample that he gives is for an inventory database. The main table is
acalled Items. Linked to this are three 1:1 subset tables called Software,
Office Furniture, and Office Equipment.

This technique is also called "Subclassing", and is one of the rather rare
cases where one to one relationships are appropriate.

The idea is that you have different kinds of Items, each with differnt
Attributes. They're all items, and they're all in the inventory, so you want a
(master) Items table with fields like purchase date, cost, etc.; but each
class of item has information that only applies to that class. For instance,
Access 2007 (a software item) would have a Version, ServicePack, etc.; an
office desk wouldn't, but might have NumberOfDrawers; office equipment might
have PowerSupplyVoltage which wouldn't apply to the other classes.

You could of course just have a lot of nullable fields in the Items table but
there are good reasons not to (for one, you might end up trying to figure out
how to get Service Pack 4.0 for your clipboard!)
--

John W. Vinson [MVP]

Ok, I sort of figured out that much but how would you implement this?

"John W. Vinson" wrote:

On Sat, 9 May 2009 17:24:01 -0700, John F
wrote:

I am currently reading a book entitled "Database Design for Mere Mortals" and
the author mentions using subset tables. However he does not fully explain
them nor how they are used. Can someone please expound on this topic further?

The sample that he gives is for an inventory database. The main table is
acalled Items. Linked to this are three 1:1 subset tables called Software,
Office Furniture, and Office Equipment.

This technique is also called "Subclassing", and is one of the rather rare
cases where one to one relationships are appropriate.

The idea is that you have different kinds of Items, each with differnt
Attributes. They're all items, and they're all in the inventory, so you want a
(master) Items table with fields like purchase date, cost, etc.; but each
class of item has information that only applies to that class. For instance,
Access 2007 (a software item) would have a Version, ServicePack, etc.; an
office desk wouldn't, but might have NumberOfDrawers; office equipment might
have PowerSupplyVoltage which wouldn't apply to the other classes.

You could of course just have a lot of nullable fields in the Items table but
there are good reasons not to (for one, you might end up trying to figure out
how to get Service Pack 4.0 for your clipboard!)
--

John W. Vinson [MVP]

On Sat, 9 May 2009 19:19:01 -0700, John F
wrote:

Ok, I sort of figured out that much but how would you implement this?

Set up your Items table with an ItemID primary key (Autonumber or other
datatype) and the fields pertinant to all items; and set up each subclass
table with an ItemID primary key. This should be a Long Integer if
Items.ItemID is an autonumber, othewise it should match. Each subclass table
would have fields for the proper attributes of that subclass.

For data entry you'ld use a Form with a Subform; one useful technique is to
change the subform's Recordsource to the (say) Software table if Software is
selected on the main form as the item type.
--

John W. Vinson [MVP]

Ok, the lightbulb has illuminated. Thanks I have been doing this kind of
database occasionally alll along, but I did not realize that this was the
term used to describe it.


"John W. Vinson" wrote:

On Sat, 9 May 2009 19:19:01 -0700, John F
wrote:

Ok, I sort of figured out that much but how would you implement this?

Set up your Items table with an ItemID primary key (Autonumber or other
datatype) and the fields pertinant to all items; and set up each subclass
table with an ItemID primary key. This should be a Long Integer if
Items.ItemID is an autonumber, othewise it should match. Each subclass table
would have fields for the proper attributes of that subclass.

For data entry you'ld use a Form with a Subform; one useful technique is to
change the subform's Recordsource to the (say) Software table if Software is
selected on the main form as the item type.
--

John W. Vinson [MVP]