If this is your first visit, be sure to check out the FAQ by clicking the link above. You may have to register before you can post: click the register link above to proceed. To start viewing messages, select the forum that you want to visit from the selection below. |
|
|
Thread Tools | Display Modes |
#1
|
|||
|
|||
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. |
#2
|
|||
|
|||
Need clairification
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] |
#3
|
|||
|
|||
Need clairification
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] |
#4
|
|||
|
|||
Need clairification
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] |
#5
|
|||
|
|||
Need clairification
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] |
Thread Tools | |
Display Modes | |
|
|