Address - Entity or Attribute, Table, or Fields in Addressee Tables?

[Brendan Reynolds]

This question has come up once or twice in the newsgroups recently. I've
read those posts without coming down strongly on either side, but now I'm
faced with a situation where I have to make a decision.

I'm designing a new database that will store information, including
addresses, about school students and their parents. On average, there will
be at least three people at each address. Users should not have to enter or
edit the same address multiple times, but on the other hand if one person's
address changes the addresses of the other people at that address should not
change without the user confirming that this is what should happen.

When one person's address is edited, I'll need to ask the user something
like: 'Do you want to apply this change to all persons at the old address?"
If I store the addresses in their own table, then when the user answers
"Yes" I'll have no extra work to do, but when the user answers "No" I'll
need to create a new address instead of updating the existing address, and
update the foreign key of the addressee record with the PK of the new
address record. On the other hand, if I store the addresses in fields within
the student and parent tables, then when the user answers "No" I'll have no
extra work to do, but when the user answers "Yes" I'll need to update all
records where the address fields match the old values, replacing them with
the new values.

The pros and cons seem about evenly balanced to me. I can't see that either
approach is significantly better than the other. Does anyone disagree?
Anyone think that one approach represents better practice than the other?

 

[Brendan Reynolds]

I've just been discussing this with a colleague, and we believe that
situations in which a) one person moves out of the home and b) the school is
informed of that person's change of address probably occur less frequently
than situations in which an address needs to be corrected and the correction
applied to all persons at that address. We're leaning toward the view that
the separate address table is therefore the better choice, as it minimizes
the work that the database engine needs to do in the more common scenario.

 

[Craig Alexander Morrison]

Brendan you should always be driven by the true representation of the data
as it applies to the specific problem domain. There is no right answer that
can apply to all problem domains.

You would appear to have Persons and Addresses. Many designs in the
commercial environment would suggest that an Address is always related to a
Business. However there are also commercial designs that maintain address
data and then relate it to businesses such as logistics (trucking), property
tax, real estate management.

A design that involves People at their Home Address say for a Local Council
or School may actually be concerned about the Address as well as the People.

I would suggest you have a design that has a table for Person and one for
Address and link them in the standard way for a Many to Many relationship.
You may also have a 1 to 1 relationship with Person to a table for Students
and one for Parents, but that is another matter.

This approach will allow you to change an address, although, you may really
only be adding addresses as the other address may be used by another family
in the future say when families move house, the address will still exist.
Some maintenance on Addresses not related to families may be required,
although unless the address is no longer physically in existence you may
find it more user friendly to maintain it should another family move there.

The only thing you will need to handle is updating the PersonAddress table
depending upon whether all members of the family have moved or not.

So the answer is...

....it depends what the data and how it will be used in your system is
telling you.

I see so many people talking about denormalisation when what they are
actually doing is designing their database by first modelling the "real
world" and then trying to get it to fit the system they are developing. What
you should do is decide how your system sees the data and then create a
model that represents that "reality". Some systems could almost get away
with a large text/memo field for address if they really don't care about it
except to stick it on labels and envelopes.

 

[Jeff Boyce]

Brendan

JOPO (just one person's opinion)...

Long ago and far away, I created a table design to handle the requirement
imposed by the world our agency deals in. The structure had to handle one
person at many addresses and one address for many persons (actually, quite a
bit more complex than this, but to the point...).

I created a join table to handle the m-m relationship, and, as you've
discussed, all the 'handling' routines. Perhaps the hardest part of getting
this to work was NOT the data design, nor the handler routines, but the
education. Until the folks using the application understood that their real
world is more complex (i.e., the m-m relationship), they had some difficulty
understanding why the application seemed complex.

At the time (long ago, far away), I created as user-friendly a UI as I knew
how to. I'm sure I would do it differently now. On the plus side, this app
has been running for over six years, and every "new" situation/requirement
the users come up with, the flexible data structure has been able to handle.

And although the agency has subjected the design to at least a half dozen
reviews, looking for a way to replace the app with shrink-wrap or a custom
web design, the agency is still using the app...

My vote is for the more flexible design.

 

[peregenem]

We're leaning toward the view that
the separate address table is therefore the better choice, as it minimizes
the work that the database engine needs to do in the more common scenario.

In this type of model, an 'address' is usually an attribute of the
person, being the thing you have to put on an envelope for the mail
service to be able to deliver it to that person. This is inconvenient
for the database developer because there is no easy way of saying one
address attribute is the same/not the same as another address
attribute. In some models, an address itself is used as a person
identifier (I'm reminded this when I read in the newspaper, 'The
defendant spoke only to confirm his name and address').

What do you plan to use to key your Addresses table? There's no easy
answer this side of the water:
http://www.govtalk.gov.uk/gdsc/html/frames/default.htm

If going with a separate table, you are affectively modelling an
'address' as something which exists independent of the people who live
there. From this perspective, it is very rare for an address to change
(e.g. they build a new road through North Street and you now have North
Road East and North Road West and they reassigned the house numbers
etc). So, assuming house demolition is outside the scope of your model
<g>, I'd suggest you only admin role to edit to correct typos and then
only when an address in not in use e.g. its key is in use in a
'PersonsAddresses' relationship table. Otherwise, and address must be
deleted and created a new. When a person moves to a new house, a new
address row must be created and the person row associated with the new
address. I can envision a dialog listing other person entities linked
to the old address as a means of changing thethem to the new address in
one action.

Is there scope to model a 'household' entity, being a collection of
person entities? This way, the address entity is associated with the
household and not the person(s) directly. A person could leave a
household to join another or start a new household on their own etc
etc.

 

[John Vinson]

I'm designing a new database that will store information, including
addresses, about school students and their parents. On average, there will
be at least three people at each address. Users should not have to enter or
edit the same address multiple times, but on the other hand if one person's
address changes the addresses of the other people at that address should not
change without the user confirming that this is what should happen.

again, just chiming in with another opinion...

I've got a church membership database which has the same issue. My
solution was to have a Families (you could call it Households) table
related one to many to a Members table. The address information is
stored in Families, and reports can be based either on the family
table (i.e. to send a newsletter to the whole family) or on the people
table (i.e. for a letter to an individual); in either case a query
picks up the address from Families.

On the data entry/editing form for families, I have a "Move Out"
button which (after prompting and confirmation) creates a new Families
record and assigns that person's FamilyID to that of the newly created
record, effectively removing that person from their current household.


John W. Vinson[MVP]

 

[Brendan Reynolds]

Thanks to everyone who responded. I believe I found something of value in
every one of the responses.

I'll try to respond briefly to a few of the questions raised and suggestions
made ...

As far as I can see the only possible natural key is the combination of all
fields in the address table. In the majority of cases, a single phone number
can be associated with a domestic address and can serve as a convenient
short-cut for looking up existing addresses, but addresses with no phone,
though rare, are not unknown, so phone number can't be the key. This does
mean that in some cases the user may end up having to enter the whole
address, even though that address is already on record, as only after the
complete address has been entered will it be possible to determine that the
address entered matches an address already on record. But these will be
exceptions. In most cases, if an address is on record, the user will only
have to enter the phone number to look up that record.

Changes to addresses due to renaming of streets etc., while rare, are not
entirely unknown, but I do expect that the majority of changes to existing
address records will be corrections of typos and spelling errors, and I
expect that those changes will not be rare at all - I expect they will be
quite frequent. It will have to be possible for users to make those changes
promptly and easily. But it will also be necessary to distinguish between
those changes and a person moving to a different address, which may or may
not indicate that other people at the old address are also moving to the new
address - which can only be determined by asking the user.

I'm not sure that I need a many-to-many relationship between people and
addresses, I think a one (address) to many (people) will meet the needs of
this application. Yes, it's possible for someone to have more than one
address, but I think very few people, especially people with children of
school-going age, don't have one 'usual place of residence', and I think
that's probably the only address that the schools care about. It's something
to ask about at the next meeting with the clients, though.

Similarly, I'm not sure that I need to subclass (a persons table related
one-to-one to students and to parents tables) in this scenario. The
information recorded about parents is quite different to the information
recorded about students, and the information recorded about both is quite
different from the information recorded about other roles such as teachers.
There'd be very few fields in the persons table - name and gender is about
it, I think.

--
Brendan Reynolds

I've just been discussing this with a colleague, and we believe that
situations in which a) one person moves out of the home and b) the school
is informed of that person's change of address probably occur less
frequently than situations in which an address needs to be corrected and
the correction applied to all persons at that address. We're leaning
toward the view that the separate address table is therefore the better
choice, as it minimizes the work that the database engine needs to do in
the more common scenario.