Tables and Relationships

[Dan K]

I am creating a database for computer parts, and systems.

So Far my thoughts are to create several tables for the different parts.

Tbl_Motherboard
Tbl_HardDrives
Tbl_Video
Tbl_SoundCard
Tbl_Memory
Tbl_Systems
Tbl_Vendor

For the Motherboard I will have fields for the Make, Model, Date Of Purchase
(DOP), as well as info like how many PCI Slots, CPU Type and speed, and
Memory Slots.

For HardDrives I will have fields for Make, Model, Size, DOP and so on.

For the Memory I will have the Size, and Speed, DOP etc.

When I get down to the setting up the relationships, I am wanting to use the
Systems table to hold the info on where each part is. So System #101 can
have a MotherBoard, Soundcard, HardDrive Etc.

Regarding the Memory, since there is a relation of One to Many for the
Mother Board, Should I set up a seperate table for the Memory to
MotherBoard? Or should I use one table and use the System to hold all the
parts?

What is the best way to set up the relationships?


Thanks.

 

[John Vinson]

I am creating a database for computer parts, and systems.

So Far my thoughts are to create several tables for the different parts.

Tbl_Motherboard
Tbl_HardDrives
Tbl_Video
Tbl_SoundCard
Tbl_Memory
Tbl_Systems
Tbl_Vendor

For the Motherboard I will have fields for the Make, Model, Date Of Purchase
(DOP), as well as info like how many PCI Slots, CPU Type and speed, and
Memory Slots.

For HardDrives I will have fields for Make, Model, Size, DOP and so on.

For the Memory I will have the Size, and Speed, DOP etc.

When I get down to the setting up the relationships, I am wanting to use the
Systems table to hold the info on where each part is. So System #101 can
have a MotherBoard, Soundcard, HardDrive Etc.

Regarding the Memory, since there is a relation of One to Many for the
Mother Board, Should I set up a seperate table for the Memory to
MotherBoard? Or should I use one table and use the System to hold all the
parts?

What is the best way to set up the relationships?

This is probably a good case for a technique called "Subclassing".
Motherboards, hard drives, etc. etc. are all special cases of
Components. They have some features in common (i.e. they'll all have a
Manufacturer, DOP, maybe a few other things; and then they'll have
their own distinct attributes.

One way to handle this is to have a Tbl_Components related one to many
to Tbl_Systems, and related one-to-one to each of the specific
component tables. Tbl_Components would have only the few fields common
to all components; the other tables would have the specific fields. A
system will have only one motherboard, I'd assume, but might have
multiple hard drives, memory chips, maybe even sound and video cards;
so you might have multiple records in tbl_Components each linked to a
single (say) hard disk record in Tbl_Harddrives.


John W. Vinson[MVP]
Join the online Access Chats
Tuesday 11am EDT - Thursday 3:30pm EDT
http://community.compuserve.com/msdevapps