Normalisation question

[Stephen Glynn]

This isn't a specific problem I'm trying to crack, but I'd appreciate
some general advice on something that's puzzling me.

Am I correct in thinking that, strictly speaking, it's wrong to have a
tblContacts with ContactID, FirstName, LastName, AddressLine1 and so
forth, as does the Contact Management database that ships with Access?

Since, I reason, someone can have more than one address and several
people can share an address, shouldn't I have one tblPeople (PersonID,
Name, Date of Birth, and other strictly personal attributes), a
tblAddresses and a tblPeopleAddresses containing PersonID and AddressID
to join the two? Presumably, come to think about it,
tblPeopleAddresses should also, to be useful, contain a third field
pointing to a look-up tblAddressType (home, business, weekend address or
whatever).

Similarly, should not both phone numbers and email addresses have their
own tables and linking tables, on the grounds that one person can have
an indeterminate number of both phone numbers and email addresses?

If I am correct,is it common practice to design databases this way?

Steve

 

[Joe Cilinceon]

This isn't a specific problem I'm trying to crack, but I'd appreciate
some general advice on something that's puzzling me.

Am I correct in thinking that, strictly speaking, it's wrong to have a
tblContacts with ContactID, FirstName, LastName, AddressLine1 and so
forth, as does the Contact Management database that ships with Access?

Since, I reason, someone can have more than one address and several
people can share an address, shouldn't I have one tblPeople (PersonID,
Name, Date of Birth, and other strictly personal attributes), a
tblAddresses and a tblPeopleAddresses containing PersonID and
AddressID to join the two? Presumably, come to think about it,
tblPeopleAddresses should also, to be useful, contain a third field
pointing to a look-up tblAddressType (home, business, weekend address
or whatever).

Similarly, should not both phone numbers and email addresses have
their own tables and linking tables, on the grounds that one person
can have an indeterminate number of both phone numbers and email
addresses?
If I am correct,is it common practice to design databases this way?

Steve

I don't know the right or wrong, but when it comes to data for people, I do
like a couple of tables with one for just addresses. I like this method
because it is easier to have save multiple address. I also use lookup tables
for State and Country information. The Address lookup table is a good idea
too in my opinion though I've never used one.

 

[Vincent Johns]

I don't know the right or wrong, but when it comes to data for people, I do
like a couple of tables with one for just addresses. I like this method
because it is easier to have save multiple address. I also use lookup tables
for State and Country information. The Address lookup table is a good idea
too in my opinion though I've never used one.

Sometimes I use one, sometimes the other. For a mailing list, old
addresses are useless (they would waste postage if used, for example)
and need not be kept. Since I keep only one address for a person, that
can just as easily stay in the same record as the person's name.

If I need (or think I might need) multiple addresses, I put them into a
separate Table. Or, if I keep track of multiple people at one address,
I invert the relationship -- an [Addresses] or [Households] Table to
which I can link several [Persons].

If you think you might need to track BOTH several persons in one
household AND several addresses for one person, you'd probably need to
establish a many-to-many relationship, including a linking Table in
which each record associates some human being with some address. But
much of the time, that's a bit more complex than what's needed. And
even if you know that there might be a couple of cases where two persons
in your list share an address, but it's pretty exceptional for that to
happen, you might choose to store the address fields with the names, and
duplicate the address in those exceptional cases. It would involve
extra maintenance, perhaps, assuming that if one person moves the other
one automatically does too, but you'd save yourself the trouble of
maintaining an additional Table.

-- Vincent Johns <[email protected]>
Please feel free to quote anything I say here.

 

[John Vinson]

This isn't a specific problem I'm trying to crack, but I'd appreciate
some general advice on something that's puzzling me.

Am I correct in thinking that, strictly speaking, it's wrong to have a
tblContacts with ContactID, FirstName, LastName, AddressLine1 and so
forth, as does the Contact Management database that ships with Access?

Sometimes... and sometimes it's fine.

Since, I reason, someone can have more than one address and several
people can share an address, shouldn't I have one tblPeople (PersonID,
Name, Date of Birth, and other strictly personal attributes), a
tblAddresses and a tblPeopleAddresses containing PersonID and AddressID
to join the two? Presumably, come to think about it,
tblPeopleAddresses should also, to be useful, contain a third field
pointing to a look-up tblAddressType (home, business, weekend address or
whatever).

If (for your application, for a good business reason) you need to
track multiple addresses, you're absolutely right. However, for many
uses (for instance a customer table) you might only need to know a
mailing address; you don't need to know about the customer's vacation
home in Bermuda or their villa in Sicily, it would just make you feel

Similarly, should not both phone numbers and email addresses have their
own tables and linking tables, on the grounds that one person can have
an indeterminate number of both phone numbers and email addresses?

If I am correct,is it common practice to design databases this way?

I've done both, but the multitable approach is in fact pretty common;
it's just more work, so don't use it unless you have a need to do so.

John W. Vinson[MVP]

 

[tina]

it's not wrong if only one address is relevant to a contact in whatever
business process you're modeling. remember that any template database is a
generic model, and can't be expected to support business processes that
deviate from that generic "standard".

the other setups you describe are appropriate, when they fully support
whatever business process you're building them for. that is why process
analysis, as a part of data modeling, is the vital first step in building a
custom database.

hth

 

[Guest]

The classic case for multiple addresses for one person (and I'm sure we've
all encountered this when ordering online) is Delivery address and Billing
address. Is this always needed? No, but sometimes it is. As for two persons
at the same address, the classic case is bank statements. If I have a bank
account in my name and my wife has an account in her name, the bank better
have the ability send out a statement to each of us in separate envelopes.
These are some of the possibilities you need to ponder when designing your
application.

David

 

[Stephen Glynn]

That's three main tables, isn't it? Customers, accounts and addresses,
all liked in a joining table with AccountID, CustomerID and AddressID.

An account can belong to one or more people, people can own,
individually or jointly, one or more accounts, and people can also want
some of their statements to go to their home address and some to their
business address(es).

Steve

 

[Vincent Johns]

That's three main tables, isn't it? Customers, accounts and addresses,
all liked in a joining table with AccountID, CustomerID and AddressID.

An account can belong to one or more people, people can own,
individually or jointly, one or more accounts, and people can also want
some of their statements to go to their home address and some to their
business address(es).

Steve

You could use three main Tables for that, as you suggest, but you could
also easily justify combining the [Customers] Table with the [addresses]
Table, with each record in the [Customers] Table including the address
fields. Unless you typically have 2 or more [Customers] living at the
same place, and they typically all move to the same new address at the
same time, it's probably easier to combine them. OTOH, for a church
directory that lists parents and children, you'd probably want to link
all members of one family to one [addresses] record. There are probably
intermediate situations, where neither system is obviously better.

What you need to try to determine is how much work (in either case) it
will be to make changes and be sure they're consistent, and then choose
the method that you expect will minimize the trouble you'll have to go to.

-- Vincent Johns <[email protected]>
Please feel free to quote anything I say here.

 

[Stephen Glynn]

That's three main tables, isn't it? Customers, accounts and
addresses, all liked in a joining table with AccountID, CustomerID and
AddressID.

An account can belong to one or more people, people can own,
individually or jointly, one or more accounts, and people can also
want some of their statements to go to their home address and some to
their business address(es).

Steve

You could use three main Tables for that, as you suggest, but you could
also easily justify combining the [Customers] Table with the [addresses]
Table, with each record in the [Customers] Table including the address
fields. Unless you typically have 2 or more [Customers] living at the
same place, and they typically all move to the same new address at the
same time, it's probably easier to combine them. OTOH, for a church
directory that lists parents and children, you'd probably want to link
all members of one family to one [addresses] record. There are probably
intermediate situations, where neither system is obviously better.

What you need to try to determine is how much work (in either case) it
will be to make changes and be sure they're consistent, and then choose
the method that you expect will minimize the trouble you'll have to go to.

I don't quite follow your point about 'unless you typically have 2 or
more [Customers] living at the same place, and they typically all move
to the same new address at the same time, it's probably easier to
combine them'. I'd have thought the opposite was the case.

Take, for example, a student at university who lives on campus during
term time and with his parents during the vacation. He'll want his bank
statements and credit card bills to go to wherever he's living at the
time. With the three table model, all the operator needs to do is to
change the AddressID field in the joining table (either with an update
query or manually, using a list box on a form) for the accounts with his
CustomerID each time he tells them to. Far simpler, I'd have thought,
than having to change all his address fields in a table each time he moves.

Steve

 

[Vincent Johns]

Vincent Johns wrote:
[...]
I don't quite follow your point about 'unless you typically have 2 or
more [Customers] living at the same place, and they typically all move
to the same new address at the same time, it's probably easier to
combine them'. I'd have thought the opposite was the case.

What I meant was that one address for several persons is a good model
for families all of whose members share an address and change addresses
at the same time. If the persons are merely roommates, and they are
likely to move to different places when they move, you probably don't
gain much by having one address apply to both -- when one moves, you'll
have to break at least one of the links instead of just updating a data
field.

Take, for example, a student at university who lives on campus during
term time and with his parents during the vacation. He'll want his bank
statements and credit card bills to go to wherever he's living at the
time. With the three table model, all the operator needs to do is to
change the AddressID field in the joining table (either with an update
query or manually, using a list box on a form) for the accounts with his
CustomerID each time he tells them to. Far simpler, I'd have thought,
than having to change all his address fields in a table each time he moves.

Steve

In the case of the college student, assuming the address formats are
similar, it probably would make sense to store the addresses for home,
dormitory, on-campus job, and off-campus job in an [addresses] Table,
with a field indicating whose address it is and a field indicating which
type of address it is.

Where it would be even less clear would be if the data are phone numbers
or email addresses, which would be even shorter than mailing addresses,
and maybe not as likely to be shared by several persons. Storing the
entire phone number in a suitable field (one field for home, another for
business hours, another for cell phone, another for emergency) might be
easier than keeping a separate Table in which each record contains a
field identifying the person, one containing the phone number, and one
containing the type of number it is. Either design would work, but
which is better you might have to determine through experience. For
example, you might count how many types of phone numbers (on average)
each person in your list has, or how often they're updated, or you might
need to analyze what types of Queries you most often run against your
lists. Some of those answers you may not be able to determine before
the fact.

-- Vincent Johns <[email protected]>
Please feel free to quote anything I say here.