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

 

[MadTown]

Craig,
I see a shortfall in your suggestion in that the post code is not a unique field and this would effectively link all addresses with that Post Code to the contact (unless I'm missing something here?).

Normalization is not subjective, it is a specific set of rules which a programmer can decide to follow or ignore. In most cases to ignore it is the wrong choice.

Squidster, what they taught you in school may not always be best, but you need to know it so you can determine what is best. As a professor once told me "You need to learn the rules before you can break them"

 

[Craig0201]

"Squidster, what they taught you in school may not always be best, but you need to know it so you can determine what is best. As a professor once told me "You need to learn the rules before you can break them""

Hence my reply, normalisation is subjective. Do it to the degree necessary and sometimes deliberately go back. Isn't how you get the performance subjective?

Also, postcode is in the UK unique to a set of addresses, all of which have the same road name. Therefore PO can act as a key in the address table.

Craig

 

[MadTown]

Craig,
Also, postcode is in the UK unique to a set of addresses, all of which have the same road name. Therefore PO can act as a key in the address table.

A couple of what ifs:

Is there a guarantee that this code will always be unique? Is it even remotely possible that more than one street could have the same PO? Could that change anytime in the life of the system?

What if the user of the system decides to go international? In the US POs or Zip Codes are not unique per street, even with the 9 digit version and all countries don't use the same format.


A good rule of thumb is to not use meaningful data as a primary key, in that the data may change (An address may get moved into a new PO or they could change the PO system all together)and break the system in the future. I have had the misfortune dealing with this in the past.

Also, I think we are saying the same thing about normalization. My point was that the process of normalization itself is a strict set of rules not open to subjectivity, Third Normal Form is Third Normal Form no matter who does the normalization. The subjectivity comes in when you decide if Third Normal form is what best suits the situation. In other words, Normalization is not subjective, but if you want to use it or not is.

 

[Onyxpurr]

Interesting thread guys, but I think the whole point is what exactly is this database for??

I would stick to only one table if my contacts were primarily private addresses. You would not have enough duplicate addresses to matter. (However this does not account for what-ifs such as what if we do decide to track more than one address in the future and I ALWAYS make it a point to plan for what-ifs. The design of this database is relatively simple, or at least the part being discussed, that doing this would not require much more work.)

However if this information is to primarily track businesses and very many multiple contacts per address (I have at least 300 people at my work, and we're small beans compared to the corporate site) then creating more than one table is logical.

Have fun!!!!

My prof put it best, normalize as much as possible then DE-normalize to what is logical.

 

[MissyEd]

Putting in my 2 cents:

You dont have to buy QAS or AFD. You can buy from a company callied Allied I beleive - email me if you want their contact details.... the post office address file (PAF) which lists all the UK postcodes including Northern Ireland. You can buy street level info only for about £350. The final amount depends on: how often you want to update the PAF(monthly, quarterly, annually), number of users to access, and how much detail you require. They can go down to house no., house name and business names etc. HTH.

And please dont flame me, but, I beleive you should create the tables with 2 things in mind: 1 what is it for ? Always K.I.S.S and 2 how will it be used in the nr-ish future ?

We have gone from several tables containing different contacts e.g. solicitors, insurance co's etc each with their own tables to centralised addressing to reduce admin errors which were costing us a huge amount per week. All UK addresses are cross-referenced on the PAF and if the address exists, we put the contacts uniqiue ID and the address ID into a link table. This allows us to link a single organisation to say 7 different addresses as a solicitor could have several branches. So it really is down to what you want the db for and cost of posting must be considered too. If you post a lot of letters, accuracy of the postcodes is vital - so its worth investing in some sort of address verification. Missy Ed - Bolton, UK

 

[squidster]

Thanks Missy Ed,

I think I can definately say if its not £0.00 then its not going to happen.

K.I.S.S?

 

[MissyEd]

Keep It Simple Son Are you sure you dont work for the same co. I do ? Its hard to get them to spend a penny *lol* Yet the marketing dept always has funds to take clients to lunch or golf or buy them mousemats with the company logo *sigh* Such is life. Missy Ed - Bolton, UK

 

[petermeachem]

Missy do you have the url for the paf company you mentioned. I can't find them.

http://www.therockalltimes.co.uk

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

 

[MissyEd]

Lesley Hunt
Sales and Marketing
DD: 01508 491431
ALLIES COMPUTING LTD
Manor Farm Barns, Fox Road, Framingham Pigot, NORWICH, NR14 7PZ
Fax: 01508 494481
Tel: 01508 494488
Website:

http://www.allies-computing.co.uk

Registered Number: 2170222 (England & Wales)
Registered office: 18, Princes Street, NORWICH, NR3 1AE

We paid £441.80 inc VAT for street level. Ive been quoted £860 ex. VAT for premise level. Missy Ed - Bolton, UK

 

[petermeachem]

Thanks for the info. Seems a bit expensive for just the road. Depends on the number of users I suppose but afd looks more cost effective (cheaper) to me.

http://www.therockalltimes.co.uk

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