Addresses stored in a database should you normalize?

2
votes

quick question.

consider the following table (UK):

  • CustomerID (PK)
  • First Name
  • Surname
  • House_No/name
  • street
  • City
  • Postcode

Would you split off address into another table? basic business assumption is that a customer cannot have more than one address.

originally i seperated this off to look something like this:

Customer Table

  • CustomerID (PK)
  • FirstName
  • Surname
  • AddressID (FK)

Address Table

  • AddressID(PK)
  • Postcode(FK)
  • House_Number_name

Postcode Table:

  • Postcode (PK)
  • StreetName
  • CityID(FK)

City Table

  • CityID (PK)
  • CityName

unless i have my assumptions wrong that a postcode uniquely identifies a streetname and city is this not in 3NF?

4
mysqlsqldatabasems-access
MySQL and MS-Access? Don't tag products not involved. - jarlh
What country is this for? At least in the U.S., the postcode does not indicate a street or even necessarily a city. I can't be sure of other countries though. - Tom H
Hi mate, this is for UK. - satkin859

4 Answers

2
votes

personally, i would put address in another table, and link them together.

the business assumption/rule may change and when you split these things you have the best chance of accommodating any possible business rule without a major redo.

for instance - oops, the customer has a different billing address than their shipping address, or oops, we need to know where something actually shipped last year even though the customer changed their address for this year, etc.

0
votes

basic business assumption is that a customer cannot have more than one address.

If this is an actual rule and not an assumption, I'd just keep them in the one table.

However, assume puts the 'ass' in 'u' and 'me'.

So play safe and sperate the address into another table. But it looks like you are taking normalisation too far with that from your eample.

0
votes

Yes, I would split off the address into a separate table.

However, the reason is not normalization per se (under most circumstances). The primary reason is that it is a slowly changing dimension and it might be useful to look up a previous addresses.

Whether you go ahead an normalize things like postal code is a matter of taste. In a more "amateur" database, I don't think it is necessary. However, for a large database of real customers, I would be inclined to split it off. It helps ensure that the postal codes are accurate. Also, they change over time. And, you might be purchasing additional information at the postal code level, for instance.

0
votes

It all depends to your requirements, but as you mentioned above customer can't have more than one address so there's no need to another one to one relationship because you can put it in the same relation. But I suggest you break it into another one to many relationship because of future requirements.