PDA

View Full Version : Db design problem - Resolving many-many relation

dbhash
05-23-02, 11:00
I am a student, trying my hand for the first time 2 design a db- database for computer inventory, but with no success. Here is my design...

I need a table containing info about a computer model,the OS running
on the comp, hard drives it contains, the network addresses and the
primary and secondary users. I have a lot of many-many relationships
within this table.

1.Each comp can have one or more OS - as in a Mac running both OS 9
and OS x.
and each OS is present on many comps.
2.A comp can have 1 or more hard drives with varying capacity.
3.Each comp has a slew of network addresses - IP, Mac address of
pccard, MAC address of wireless, Mac address of integrated network
card etc.

The table structure so far:

Comp_details (Comp id primary key
comp_manufacturer
comp_model
comp_name
os id foreign key references comp_os)

comp_os (os_id primary key
os_name
os_version)

comp_user (user_id primary key
user_name
comp_id foreign key references comp_details)

hard_disk (disk_id primary key
capacity)

network_addr (addr_id primary key
addr_type)

Now, what i've thought of is to resolve the many -many relations by
creating a table for the relations.
The part I am most confused about is the hard drives and Network
addresses. A comp may or may not have more than 1 hard disk. Same goes forthe Network addresses. So, it makes sense to create a separate table for the relation -
in the case of hard disc and Network addresses..

comp_hard_disk (comp_id foreign key references comp_details
hard_disk_id foreign key references hard_disk)

comp_network_addr (comp_id foreign key references comp_details
addr_id foreign key references network_addr)


This would be too tiresome since I have almost 600-700 comps to deal
with. Also, the no. of addresses will run into thousands.
Should I just go ahead and create 2 columns for hard disks in
comp_details table as Hard_disk_1 and hard_disk_2 and create 4 columns for addr as mac_1, mac_2, mac_3, ip ?
This violates the normalization rules..

I am at odds as to how to proceed from here...sorry abt the message length.Couldn't put it more succintly than this.
Please help. Thanks in advance for all your pointers.
MattR
05-23-02, 11:56
Well unless your hard drives can be attached to more than one computer at a time (NAS/SAN/etc. but you can't tell with those; fibre channel perhaps) then you don't need a separate table.

Simply place comp_id foreign key in the hard disk table and network address tables since the relationship is one (computer) to many (disk, network, etc.).
dbhash
05-23-02, 15:45
Matt, Thanks for your response. I am now mulling over a new problem..(they never end, do they??)

The network_addr table has just 1 attribute,the mac_addr which is always unique.
Can I make this a primary key??
network_addr (mac_addr varchar(30) ??
comp_id foreign key references comp_details)

A sample of the data in network_addr table:

Mac Address comp_id
00:xx:C0:xx:B8:xx 1
00:xx:C0:xx:B5:xx 1
00:xx:C0:xx:B5:xx 2
00:xx:2D:xx:C8:xx 2
00:xx:2D:xx:C8:xx 2
00:xx:2D:xx:C8:xx 3
00:xx:2D:xx:C8:xx 4

or do I need to create an auto_increment column as my primary key??
Tnaks again!
MattR
05-23-02, 15:49
The MAC address is a perfectly fine primary key.
dbhash
05-23-02, 16:16
Thanks..You've been very helpful. Appreciate it.