Re: A question of DB Design.

"Mark A. Framness" wrote:

> Hi!
>
> I am designing a personal contacts management system using an Oracle 7.3 DB
> server, Lotus Domino Go Pro Webserver and CGI along with Netscape.
>
> I have done some things right already but am starting over. A couple of
> issues have come up.
>
> First off I am assuming each person will have one address, though it is
> possible for two people to have identical addresses (e.g. the person and
> their spouse who may or may not end up as a separate entry in my persons
> table). The question I have is would it be best to have a separate table
> for addresses and reference the persons table or should I just lump address
> information into the persons table?

Bad assumption. Maybe the people I know are exceptions, but many of them have two addresses, for example, suburban Washington DC in the summer and Florida in the winter or suburban Boston in the winter and Cape Cod in the summer. Since a person can have more than one address and an address can have more than one person you have a many-to-many situation. One way of handling it is to have three tables; one for persons, one for addresses, and one to associate each person with his/her addresses and each address with its person(s).

>
>
> Second the issue of a spouse. Now if two of my friends are married then
> each would have a separate entry in the persons table. Now I am thinking
> that if the spouse is also a friend then I would like to refer back to the
> spouses entry. Essentially I would have a foreign key referencing a field
> in the table itself. Of course I could just put a name in their and be
> done with it. Now would I gain anything by referencing the spouse in the
> table itself?
>
> table
> ========
> name spouse Link key
> AB CD CD1 AB1
> CD AB AB1 CD1
>
> Link would be a foreign key that references the key field of the same
> table.
>
> Would a circular situation illustrated above cause problems?

What about other relationships, e.g. sibling, parent, child, etc.? If you think you might want to account for other relationships in the future it would be best to create a lookup table now.

hth

>
>
> Thanks
>
> From: Mark A. Framness
> http://netnet.net/~farmer/
> mframness_at_lakefield.net
>
> Go Pack Go!
> Oh balsam tree oh balsam tree I see you when I deer hunt!
> Brian Framness

--
Once I figured out how to spell DBA I became one -- Jerry Gitomer Received on Tue Dec 21 1999 - 23:15:53 CST