Dealing With Addresses 2

[squidster]

I'm wondering what is the best way to deal with recording/storage of peoples addresses.

Scenario:-

Table1:CONTACTS
conId
addressId (FK)
fname
sname
etc

Table2:ADDRESS
addressId
addressLine1
addressLine2
town
county
poCode

1) A contact may or may not have an address.
2) A contact will only have one address.
3) More than one contact can have the same adddress.

Problem:-

How can a contact record be created without first checking to see if an address is already held for them. Logical order of data entry would be to enter the contact details first or at the same time as the address.

How would you check to see if an adress detail being entered does already exist so a to return its ID to be inserted with the contact record. Addresses can be the same but may be entered differently (ie. abreviations used).

How could you amend an address for a contact, which is shared by another contact but whos address should not be altered.

I'm thinking it would be better to store the addresses in the contact table but I think this goes against normalisation rules?

This is hurting my head now!

Please does anyone have any suggestions? (nice ones only!!)

Thanks

 

[psemianonymous]

Here's two answers:

1. Simplicity: put the address fields in your "contact" table and be done with it. Most everyone will have an address, and only a few people could possibly share one address, so the benefits of normalization aren't that big in this instance.

2. Normalization: a) Have your contact table with absolutely no address information except for a link to the Address ID. b) Make an address table with all your address info and have an AddressID as the primary key.
Basically, do it as you have above.


The problem with #1 is that addresses can be entered improperly at a greater probability.

The problem with #2 is that it's more complicated for all sorts of entry/querying/updating. for example:

1. Imagine if one member of a household moves to another address. Do you let the form update the address information directly? NO! This will update the address information in the Address table, which will update the information for all contacts with that address. How are you going to train the users to remember this type of data-centric information that (seemingly) makes no sense?
2. What about duplicate entries in the Address table? If I was a data entry person, and mistyped a letter of the address, the mail still gets there, right? 221B Baker is the same as 221-B Baker is the same as 221B Baker Street is the same as 221 b baKER st. (and so on and so on).


Summary: Do #1, unless you're working with massive amounts of data. And I mean MASSIVE. --
Find common answers using Google Groups:

http://groups.google.com/groups?group=comp.databases.ms-access

 

[MadTown]

Squidster,

If you can guarantee that at no time in the life of the database there will be no need for more addresses per contact, by all means add the address info to the contact table. Your data will still be normalized in this situation.

If there is even the slightest chance that more than address will be needed then you should set up a contact table like (Notice the absense of the addressid)

Table1:CONTACTS
conId (PK)
fname
sname
etc

Your address table should look like (Just like you had it)

Table2:ADDRESS
addressId (PK)
addressLine1
addressLine2
town
county
poCode

and an intermediary table should be used like the following

table 3:ContactAddress
Conid(FK)
addressID(FK)


AS far as validating the address, Unless you feel it absolutely necessary to do, allow duplicate addresses to have their their own addressid. It can be a headache making sure 123 Main Street and 123 Main St. are looked at as the same, not to mention misspelling of cities and such.

Hope this helps

Ken

 

[squidster]

Thanks to both of you for your answers.

I think this sounds like another case for denormalisation?

No, I'm not trying to start another fight (see post 03/11/03 (posted 01/24/03) - A case for denormalisation?) but I am understanding why its not allways the best way to go.

 

[CajunCenturion]

Given that you have the following three requirements and these are complete correct:

1) A contact may or may not have an address.
2) A contact will only have one address.
3) More than one contact can have the same adddress.

There is no need for a third table. A third table would only be necessary is there is a many-to-many relationship, which based on requirement #2, is not a possibility.

Adding the address information to the contact table leads to the potential for redundant data, in the address information will be repeated when multiple contacts have the same address. This leads to a serious update problem should the address need to change. You not only have multiple address to change, but may have difficulty in identifying all the ones that need updating. Mis-spellings may not prevent the mail from arriving, but it may very well prevent updates from happening.

I would recommend that you place an AddressID field in the contact table, which may be null to handle those contacts which have no address. By having only one AddressID, you enforce that a contact may only have one address. This field, although indexed, would not be unique in order to have multiple contacts sharing an address.

To handle the situation described above where only one contact changes addresses, you handle that by adding a new address, rather than updating. This can be checked by querying if any other contact has the same address ID.

This is not, IMHO, a valid scenario for denormalization. Good Luck
--------------
As a circle of light increases so does the circumference of darkness around it. - Albert Einstein

 

[squidster]

Thanks CajunCenturion

One thing though, if you go with the two seperate tables, how would you overcome the problem where a contact already exists with an address and you need to add a new contact who also has the same address.

Say the address data is collected along with the contact data, this would have to be checked/inserted first so the addressId can be carried over to the contact table.

How would you carry out a check and obtain the ID if address is held, can this be done in one statement?

How do you check an address if it is spelt differently (for what ever reason). If you can't you essentially end up with redundant data anyway?

Can't focus anymore, so I'll catch your response tomorrow?

cheers

 

[CajunCenturion]

There are several approaches that might work.

1) The first field to enter is the street number. Once entered, you then populate a combobox with all of the street names which have an address with the same number. Of course, you allow for entry, and you might even provide some intellisense as characters are being entered to get to a selection. Repeat again for city, and so on.

2) Enter the Zip Code first. That significanly reduces the selecton set. You can futher restricts by then entering the city, and so on.

Although the 2nd option is more efficient, it does have the drawback of not being the normal order of address entry, and could put more of an onus of user training. Good Luck
--------------
As a circle of light increases so does the circumference of darkness around it. - Albert Einstein

 

[petermeachem]

Not sure how zip codes work, but in the UK the post code and the first line of the address identify the property. So if you use a product like Quick Address, you would definately enter the Post Code first and select the actual address from a list. As I say, don't know how it works in the US, but often if someone needs your address on the phone they will ask for the post code and the first address line.

http://www.therockalltimes.co.uk/

And particularly

http://www.therockalltimes.co.uk/2003/03/10/war-warning.html

and

http://www.therockalltimes.co.uk/2003/03/24/geneva-convention.html

 

[squidster]

QAS - sounds expensive!!

My project budget definately would not accomodate the license fee. I'm stuck with no adress database facility for this one

 

[petermeachem]

Depends. I suppose you are going to send letters to these people? If so it is a very good idea to get the address correct. There are much cheaper solutions, afd postcode for one.

http://www.therockalltimes.co.uk/

And particularly

http://www.therockalltimes.co.uk/2003/03/10/war-warning.html

and

http://www.therockalltimes.co.uk/2003/03/24/geneva-convention.html

 

[MadTown]

CajunCenturion,
Given that you have the following three requirements and these are complete correct:

1) A contact may or may not have an address.
2) A contact will only have one address.
3) More than one contact can have the same adddress.

There is no need for a third table. A third table would only be necessary is there is a many-to-many relationship, which based on requirement #2, is not a possibility

My view is that even if the user vows to me on a stack of bibles that a contact will never ever have more than one address, sometime down the line they are going to come back to me and ask for multiple addresses. Why not plan ahead?

I do like your thoughts of using an intellisense on the address but even this won't prevent redundant and incorrect data. The possibility of 123 N Main Street, 123 S Main Street and 123 Main Avenue all being valid addresses and in the same city and zipcode can cause problems when the contact gives there address as 123 Main. Then there is always the possiblity of the contact or the person entering the address with an incorrect zipcode. Aren't addresses fun ;-)

 

[squidster]

MadTown,

The three table solution would cater for the enevitable "but we actually do need this now" scenario. But without the use of something like QAS or afd postcode, how do you deal with inserting a new address and checking that it doesn't already exist, bearing in mind the different representations of the same address, as you have pointed out as well?

Dare I say it, I would imagine that the number of occurances where contacts would actually share the same address would be pretty minimal so data redundency would be low, so is all the extra work and possibly cost worth it?

How about every contact just gets a new address irrespective of if it is already held or not (in which case the address may as well go in with the contact and of course there can be only one!)

Not sure about fun? (well not right now anyway!) Definately not as straight forward as I first thought though.

 

[petermeachem]

We once had twins with the same first name (for heaven's sake) who shared a house. De-duplicate yourself out of that one.

http://www.therockalltimes.co.uk/

And particularly

http://www.therockalltimes.co.uk/2003/03/10/war-warning.html

and

http://www.therockalltimes.co.uk/2003/03/24/geneva-convention.html

 

[squidster]

AAAAAAARGH!!!! my head .

 

[CajunCenturion]

MadTown - your points are certainly valid. I agree that one should plan ahead, but if in fact the requirement for only one address per contact is a requirement, then adding the table at this point would actually increase the development effort because you now need to have extra code to insure that the many-to-many relationship does not creep into the equation. Its a trade-off decision, and without fully understanding the application, and the environment, its a tough call to make either way.

You are correct that when dealing with user-entered data, you cannot guarantee the complete correctness of the data. Such is the nature of the beast. Even with intellisense, or other tools, you still can't force the user to make the correct selections.

That being said, anything that you can do to help alleviate the problem, within reasonable budgetary constraints, has to be considered a positive move. Good Luck
--------------
As a circle of light increases so does the circumference of darkness around it. - Albert Einstein

 

[skiflyer]

I just want to weigh in on the side of "this is not a case for denormalization"

That's the last approach you want to take here.

As far as multiple contacts having the same address, I'd say... wait and think a second. People have the same address when a) They live together, b) They work in the same building.

I'm not sure I'd be going about looking for an already existing address by whether or not it's already in the database, but rather by looking to see if they want to be "tied" to another contact.

After all the whole idea here is to reduce errors in data and minimize updates, right?

So, I have 5 roommates right now. If we all signed up at the local whatever, I wouldn't want to be linked to them, because when Ben changes his address next year, I don't want mine to change to that.

Not to mention, imagine how horribly wrong this could go if you linked all members of an apartment building together (pretending you didn't have unit # somewhere in the address record), and someone got a new lease, you could put 100's of people into one home.

However, if I were married, I would want my address linked to my spouses, so when I change my address next year, hers gets updated as well... hence the reason I think denormalizing is wrong (of course odds are I'm going to think denormalizing is wrong just about anywhere someone puts it, unless they're also posting query execution times at which point I may stop to think about it)

In other words, I wouldn't think of the data as normalized around how many addresses are in the database (it's very unlikely the government is going to redo street numbers and force you to modify your db based on this), but rather on logical units of people who exist at any given address.

-Rob

 

[squidster]

skiflyer it sounds as though your contradicting yourself a bit there, first your saying that you wouldn't want to be tied to your room mates so each of you should have your own seperate address, even though they are the same (isn't this denormalisation?). Then again your saying you want your partners address to be the same as yours and with only one instance of it?

If the contacts are business contacts then the chances of there being multiple occurances of the same address would be fairly minimal, and anyway if everyone has their own address record surely this makes it easier to update etc, even if you had to do a seperate manual update for each contact who does share an address.

 

[skiflyer]

No, I wouldn't consider it denormalization... that was the whole part about considering it a logical unit of people who live together say.

Business contacts exactly would have this problem... send me, my supervisor, my peers, or anyone else I work with a package, and we all share the same address. If the company moves, I would expect you to only have to update it in one place, not several hundred. However, if my roommate gets a new address, and you change it in the database that shouldn't affect me because when I signed up I didn't link our contact addresses.

So what I'm saying is, when you register a new contact, they choose whether or not they're part of an existing "group" you already know of. If so, they'll share that record in the database, if not, they get a new one.

I guess you're right, in some way this is denormalizing the data, because you may have the same address stored more than once in your database... but by including this "group (represented by the addressID)" you've added information which makes the table row unique (not the 4th normal form or anything).

Though that normalization is kind of like saying you should setup a table to store

Grades
1 A
2 B
3 C
4 D
5 F

Basically my point is (and I'm sorry I'm being unclear)... don't worry about keeping the addresses as the unique fields to match, it's all about "groups" or "families" or "guarantor" relationships here. Assume each group shares an address, but everyone elses is different (because it should be)

-Rob

 

[Craig0201]

Table1:CONTACTS
conId
PoCode(FK)
HouseNumber
fname
sname
etc

Table2:ADDRESS
addressLine1
addressLine2
town
county
poCode(PK)

Now the job is simple. Enter a postcode and see if it matches an address. If so, great.....if not, new address time!!!

PS Normalisation is a matter of subjectivity. Do what feels right!

Craig

 

[squidster]

Thanks Craig looks as though this could be a be a good alternative in some circumstances.

Trouble is if you don't have a postcode to enter then you can't enter the record

I'm new from the world of academia so am finding out that not all they teach is best (well I kind of suspected that anyway!)