Nuances of Normalisation (1 Viewer)

[Pauldohert]

Why is it common to store address fields

ie Address1, Address2, Address3, Address4, Town etc in a table, seems to me that is as un normalisaed as

having fields like description1, description2, description3 etc which we would never do and put in another table as seperate records rather than fields.

Whats the differnace that makes address more acceptable to be un normalised?

Confused as ever , Paul

 

[reclusivemonkey]

I've always used;

Address (with multiple lines, so you can have Flat 3<newline>1 Arcacia Avenue)
Postal Town (all UK addresses should have a postal town)
Postcode (easily obtained from the royal mail website)

It always puzzled me when I saw multiple address lines in databases; it would seem that its much easier done with a single multi line address field. There is no longer a need for Counties in UK addresses according to the post office. They also state that for address labels, ideally the Postal Town should be in capitals and emboldened. However this will obviously only be of use for UK addresses.

I'd post the link from the Royal Mail website, but the site is down at the moment...

 

[Pat Hartman]

The address1-4 fields are not alternate addresses. They are parts of the same address that are separated to make it easier to use them to print labels. We just don't have a better way to name them. So rather than storing carriage returns embedded where you want the lines to break (which would be unnormalized), the data is separated into multiple fields. It makes it easier/more obvious for data entry. We have a single word for Name but we also have separate words for its constituent parts so there is never any confusion regarding why we split name into pieces.

Most companins don' t have any need to break address lines down any finer than how they should print for snail mail. However, companies that do mass mailings do break the address lines down into more discrete attributes. They separate house number, street name. Street prefix compass direction, Street type (st, rd, ln, etc.). Street suffix compas direction since some cities use suffixes rather than prefixes. Apartment number, Rural Route, etc. They do this to make it easier to identify duplicate addresses that are close but not identical.

 

[Pauldohert]

Thanks to both of you.

I will maybe understand your answer more Pat if I understand - "alternate addresses".

If you called address - addressdescription and colour - colourdescription (it seems to me only real world knowledge of their use differs them), I still don't get why address would be broken up into fields (I am happy its taken this far, but don't understand why it isn;t taken futher to a differnat record in another table for each line of address), whereas colour would be more likely broken up into records in another table.

Is the Access MVP award new Pat, I have not noticed it in your signature before. Well done anyhow!

 

[Pat Hartman]

Contact1-4 would be alternate contacts. Each contact is separate and not dependent on any other contact. Each contact could have additional related attributes such as telephone number. This is a true repeating group (as is your color example) and therefore should be treated as such by the creation of a many-side table, but Address1-4 are a single address broken into four pieces for convenience. No piece stands alone. All pieces are required to deliver mail to the location. As I said, it is possible to store all pieces in a single string provided you include new line characters. Keep in mind that the new line character is different for the mainframe than for the PC. Most designers do not include this type of formatting character in a string because it limits the usablilty of the string and may require parsing to remove them.

The ONLY reason that the address lines are broken is to facilitate physically printing the address on an envelope or label. Both of these are limited in size - both hight and width so each Address line is generally no more than 40 characters which is all that can fit on a label in a readable font size. Pre-breaking the address eliminates the need to programmatically break it at printing time. Would you like to write the parsing routine that parsed an address string to US (or other) Post Office specifications?

 

[R. Hicks]

To add to what Pat has posted ...

If you have multiple addresses .. such as Home Address, Business Address, Shipping Address .. ect ... you should have an additional child table for these entries with a field to indentify the address type ..

RDH

 

[ScottGem]

I will maybe understand your answer more Pat if I understand - "alternate addresses".

Let me give it a shot.

An address can have multiple lines. Examples:

123 Elm St
Apt 2

345 Wall St
Suite 1234

While you can put those all on one line you wouldn't want to, So you would include multiple fields for each potential part of an address. Personally, I use one field with embedded carriage returns, unless I need to perform some analysis on the address parts. For example, I did some work for a company that did deliveries. To be able to create delivery routes we needs to sort by the street name and then house number.

 

[Pauldohert]

Thanks , I think I have a better idea now.