Treating addresses in 3NF?
Date: Tue, 12 Nov 2002 16:26:40 +0000
Message-ID: <Pine.LNX.4.44.0211121539240.8050-100000_at_demon.ecs.soton.ac.uk>
I know that in a lot (i.e. almost all?) databases, a customer list which included an address is usually stored as CustomerID, Customer Name, Address1, Address2, City, Post/Zip code, blah blah. But as a purely, uh, "theoretical exercise", i.e. I don't have a bet on it with anybody at all ;), when this is normalised as far as 3NF, I come up with the tables; Customers (CustID, CustName, AddressID) Addresses (AddressID, Zip/Postcode)
AddressLines (SequenceNo, AddressID, Data)
I'm not entierly sure how the zipcode system works in the states, but here in sunny Britain our quaint post code system works such that from the postcode, you can work out a small (say, less than 20) number of dwellings that the code applies to. The road, area and city are fully determined by it.
The problem with embedding the address in the table is clearly that (Address1, Postcode) --> (Address2, City), which is a dependency that shouldn't be in the table, and needs to be normalised out to another one.
So that was exactly what I did first, but then came up against the problem that you can easily get multi-lined addresses, and sticking in extra columns into my new separate address table would be just so wrong that it hurts =)
So I ended up with the situation that I have a table of singleton lines from an address, which have a sequence number (i.e. 1 for first line, 2 for second line, etc. up to as many lines as the address needs) and are linked to the AddressID (as I have now lost access to the house number which would be in address line 1).
My point? Is this really "The One True And Correct Way"(tm) of storing address information in 3NF? I know fully that nobody in their right mind would ever consider doing it this way. That probably says quite a bit about me, in fact. Heh. :)
Also, how would I now do a search for all customers in, pick a place... Plymouth? select customername from customers inner join addresses A on customers.address=addresses.id, address_lines L where A.id = L.address and l.data = 'Plymouth'; ? Is there any way to speed this up so it doesn't query every single line of all the addresses? Especially as I bet some nutter would call his house "Plymouth" like, Plymouth, No 23, Some Road, London. Would a table of Postcode->City, and then check that the Postcode for the addressID was in the list of postcodes that matched the city work? Or could my data can now get out of sync with itself, since the city is also stored as a line in the table of single address lines? Aaaaagh!
-- Mmmm. Richard Leigh <rot13>egy101 ng rpf qbg fbgba qbg np qbg hx</rot13>Received on Tue Nov 12 2002 - 17:26:40 CET