Database design problem - resolving many-many relationships

From: Ramani <vrhash_at_yahoo.com>
Date: 22 May 2002 11:17:45 -0700
Message-ID: <d49237dd.0205221017.7b26bb03_at_posting.google.com>

I am a database newbie and am trying hard to design a 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 make 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... Please help.Thanks in advance for all your pointers.

Regards,
Ramani Received on Wed May 22 2002 - 20:17:45 CEST