Re: Database design problem - resolving many-many relationships

From: Lennart Jonsson <lennart_at_kommunicera.umea.se>
Date: 24 May 2002 01:02:17 -0700
Message-ID: <6dae7e65.0205240002.4554c6cd_at_posting.google.com>


vrhash_at_yahoo.com (Ramani) wrote in message news:<d49237dd.0205231015.2997317b_at_posting.google.com>...
> ...
> Lennart, Thanks for your response!!I now know where I stand..Here is
> something more..
> The table struct for the hard_disk
> hard_disk (disk_id primary key
> capacity)
> The hard_disk table contains just 1 attribute - the capacity; and
> since this is unique, this could be the primary key too. There are no
> other attributes like the type, make etc.

Are you shure that capacity is the only attribute you will need? Assume you now have IBM disks with 40Gb capacity, and you later discover that Seagate has a cheaper, faster, better disk also with 40Gb capacity. How do you add that one to your database?

> Can I change it into:
> hard_disk ( capacity varchar(20) primary key)
>
> The same goes for the hardware MAC address of each computer. The Mac
> addresses are unique and hence I can just make a table with 1 column -
> the MAC address.
> network_addr (mac_addr varchar(30) primary key)
>
> Is this a good design - A table containing just the primary key??
> Thanks again!
>

You may have a table just containing the P.K. In your case you can think of the table as the "domain of valid mac_addr". The table now guarentees that no one adds a computer with an "invalid" mac_addr.

Another solution is to skip the table and add a check constraint in the table that is using mac_addr

However, in your case I would go for the first one since the "domain" is probably not that easy to express. Once we get regexp into SQL I will have to reconcider :-)

These are just my thoughts. Someone with a deeper theoretical knowledge of the RM might disagree.

/Lennart Received on Fri May 24 2002 - 10:02:17 CEST

Original text of this message