Re: Treating addresses in 3NF?

"Rich Leigh" <rtl101_at_ecs.soton.ac.uk> wrote in message news: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 have a less erudite comment than the other magnificent posts (I have printed Jan's out to fully appreciate it later).

AddressLines.Data is a field that can apparently contain address lines or City. This means that the domain has to be a simple string data type. Following on from this logic, you only need one table to express the whole address. I think it is better to keep the address lines and city in separate domains and in that case they must be in separate columns to be in 1NF.

A different point. I think the main reason to store vague column names like Address1 and Address2 is to be able to count lines on labels and restrict input to a certain line width. I use bigger labels and allow a single Address field to have returns embedded in them. Received on Thu Nov 14 2002 - 14:20:46 CST