A simple question on design

[Mark H.]

Hello ppl,

I d like some help designing a customized contact list.
I have a table for people and a table for addresses.
In 99% of the cases one person will have one or more addresses so we have 1
to many there.
In very very rare cases one address will belong to more than one person. For
example, a husband and wife may be individual contacts but have the same
address.

I could either make a junction table PersonAddress just to accomodate the
rare cases (which will create an additional join when querying for
addresses), or I could make Address a weak entity whose primary key will be
the AddressID + PersonID. From a practical standpoint, which is the best way
to do it?

Many thanks folks

M

[Mark H.]

Thinking and reading a bit more on this, I believe that the correct
assessment is that both solutions represent the same semantics, or the same
facts.
In one of them an address is an independent entity that may belong to many
people. In the other, the address must have an owner. Therefore, we should
normally choose the most economical solution, which I believe is to
represent address as a weak entity.
One less table and one less join.
What do you think?

M

"Mark H." <no*****@noemail.net> wrote in message
news:10*************@corp.supernews.com...

Hello ppl,

I d like some help designing a customized contact list.
I have a table for people and a table for addresses.
In 99% of the cases one person will have one or more addresses so we have
1 to many there.
In very very rare cases one address will belong to more than one person.
For example, a husband and wife may be individual contacts but have the
same address.

I could either make a junction table PersonAddress just to accomodate the
rare cases (which will create an additional join when querying for
addresses), or I could make Address a weak entity whose primary key will
be the AddressID + PersonID. From a practical standpoint, which is the
best way to do it?

Many thanks folks

M

[PC Datasheet]

Hi Mark,

There's no point in making this complicated. You just need a table for
contacts with ContactID for the primary key and since any contact may have
more than one address a table for addresses with a primary key of AddressID.
The address table would also contain a ContactID field as a foreign key to
identify who the address record is for. The fact that two contacts may have
the same address is irrelevant. Addresses are typically treated as specific
to a person.

--
PC Datasheet
Your Resource For Help With Access, Excel And Word Applications
re******@pcdatasheet.com
www.pcdatasheet.com
"Mark H." <no*****@noemail.net> wrote in message
news:10*************@corp.supernews.com...

Hello ppl,

I d like some help designing a customized contact list.
I have a table for people and a table for addresses.
In 99% of the cases one person will have one or more addresses so we have 1 to many there.
In very very rare cases one address will belong to more than one person. For example, a husband and wife may be individual contacts but have the same
address.

I could either make a junction table PersonAddress just to accomodate the
rare cases (which will create an additional join when querying for
addresses), or I could make Address a weak entity whose primary key will be the AddressID + PersonID. From a practical standpoint, which is the best way to do it?

Many thanks folks

M

[Mark H.]

This is exactly the solution I was advocating.
Thanks for confirming.
A Person is essentially an "owner" of an address.
In formal terms, an Address becomes thus a weak entity.

Many thanks,

M.

"PC Datasheet" <no****@nospam.spam> wrote in message
news:j9******************@newsread3.news.atl.earth link.net...

Hi Mark,

There's no point in making this complicated. You just need a table for
contacts with ContactID for the primary key and since any contact may
have
more than one address a table for addresses with a primary key of
AddressID.
The address table would also contain a ContactID field as a foreign key to
identify who the address record is for. The fact that two contacts may
have
the same address is irrelevant. Addresses are typically treated as
specific
to a person.

--
PC Datasheet
Your Resource For Help With Access, Excel And Word Applications
re******@pcdatasheet.com
www.pcdatasheet.com
"Mark H." <no*****@noemail.net> wrote in message
news:10*************@corp.supernews.com...

Hello ppl,

I d like some help designing a customized contact list.
I have a table for people and a table for addresses.
In 99% of the cases one person will have one or more addresses so we have

1

to many there.
In very very rare cases one address will belong to more than one person.

For

example, a husband and wife may be individual contacts but have the same
address.

I could either make a junction table PersonAddress just to accomodate the
rare cases (which will create an additional join when querying for
addresses), or I could make Address a weak entity whose primary key will

be

the AddressID + PersonID. From a practical standpoint, which is the best

way

to do it?

Many thanks folks

M