Results 1 to 5 of 5
  1. #1
    Join Date
    May 2002
    Posts
    3

    Db design problem - Resolving many-many relation

    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.

  2. #2
    Join Date
    Mar 2001
    Location
    Lexington, KY
    Posts
    606
    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.).
    Thanks,

    Matt

  3. #3
    Join Date
    May 2002
    Posts
    3
    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!

  4. #4
    Join Date
    Mar 2001
    Location
    Lexington, KY
    Posts
    606
    The MAC address is a perfectly fine primary key.
    Thanks,

    Matt

  5. #5
    Join Date
    May 2002
    Posts
    3
    Thanks..You've been very helpful. Appreciate it.

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •