SitePoint Sponsor

User Tag List

Results 1 to 10 of 10
  1. #1
    SitePoint Zealot
    Join Date
    Sep 2004
    Location
    Canada
    Posts
    108
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Normalizing City, Province/State and Country relationships

    I have a location table, where I store an address of a business. The address will, of course, include city, province and country. How should I store it? Should I just store it as text values or should I have a separate table for cities, provinces and countries?

    Just how do you do this? I don't want to be too obnoxious, but at the same time I'd like to have a clean database.

    A good example of a proper normalized design would be in this document (PDF) on page 19, but this might get tough on the system, don't you think?

  2. #2
    SitePoint Enthusiast niardica's Avatar
    Join Date
    Sep 2002
    Location
    CA
    Posts
    34
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    My current practice is to separate states/provinces and countries into their own tables and have street address and city as a string (varchar).

    This is all just a matter of preference, but by at least doing this much, you've got a clean database design and some level of normalization.

    This article has been a great resource to some other people that I've had the same argument with..

    http://support.microsoft.com/default...;en-us;Q283878

    hope this helps,
    Niardica

  3. #3
    SitePoint Zealot
    Join Date
    Sep 2004
    Location
    Canada
    Posts
    108
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    Thanks Niardica!

    I know the basics and the theory behind normalization. I am just wondering of the practical implementation for the web. Joining 5 tables just to get an address seems to be a bit over the top. But it adds flexibility and removes redundancy.

    So I am just wondering when enough is enough

  4. #4
    SitePoint Enthusiast niardica's Avatar
    Join Date
    Sep 2002
    Location
    CA
    Posts
    34
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    I agree.. I dont enjoy joining 5 tables just to get an address either.. although if you really want to be a pro, you could have the underlying tables have the 5 relationships that you've described, then create a view that makes everything more horizontal for querying...

  5. #5
    SQL Consultant gold trophysilver trophybronze trophy
    r937's Avatar
    Join Date
    Jul 2002
    Location
    Toronto, Canada
    Posts
    39,347
    Mentioned
    63 Post(s)
    Tagged
    3 Thread(s)
    Quote Originally Posted by moltar
    A good example of a proper normalized design would be in this document (PDF) on page 19, but this might get tough on the system, don't you think?
    aside from performance issues of doing joins, i have a real problem with normalizing addresses

    (it's a nice diagram, and i'm sure it shows properly normalized tables, and i respect that author's credentials, but using addresses as an example of normalizing is extremely unfortunate)

    in the first place, if you don't have a postal code, you're sc3w3d, you will be unable to add a user's address at all!!

    and how do you know you have the right city for a postal code? if the user gives you a postal code that doesn't match the city he gives you, what are you going to do? insert a different city? who's your authority for this?

    for example, i live in East York, Ontario, which was Canada's last borough and which is (now) officially part of a new political structure called Toronto, (which is different and larger than the former city of Toronto, which, along with East York and 5 other cities, used to be part of a municipality called Metropolitan Toronto, which no longer exists, but the post office still delivers mail to me if it's addressed to East York

    are you gonna do address cleansing at the time of insert? how in the heck are you going to know if the postal code is wrong but the user's city is right? are you gonna do a postal code lookup to find the right postal code? you need the correct street address for that, too...

    as with so many other things in life, address normalization is something that you can do, but probably shouldn't

    so, [address normalization] "adds flexibility"? no bleepin way!!

    and what about "removes redundancy"? let's not even go there...

    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL
    "giving out my real stuffs"

  6. #6
    SitePoint Zealot
    Join Date
    Sep 2004
    Location
    Canada
    Posts
    108
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    Hello from O. r937!

    What you said makes perfect sense. So how would you design it then?

  7. #7
    SQL Consultant gold trophysilver trophybronze trophy
    r937's Avatar
    Join Date
    Jul 2002
    Location
    Toronto, Canada
    Posts
    39,347
    Mentioned
    63 Post(s)
    Tagged
    3 Thread(s)
    how would i design it? VARCHAR columns allowing NULL
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL
    "giving out my real stuffs"

  8. #8
    SitePoint Zealot
    Join Date
    Sep 2004
    Location
    Canada
    Posts
    108
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    So something like this?

    Code:
    postal_code CHAR(6)
    city VARCHAR(45)
    province CHAR(2)
    country VARCHAR(255)

  9. #9
    SQL Consultant gold trophysilver trophybronze trophy
    r937's Avatar
    Join Date
    Jul 2002
    Location
    Toronto, Canada
    Posts
    39,347
    Mentioned
    63 Post(s)
    Tagged
    3 Thread(s)
    sort of, but if you're going to accept only 2-char province codes, you must be prepared to handle cases where the user provides province values like 'NWT' or 'Ont.' and translate them on the fly

    and i'd use VARCHAR(9) if i wanted to accommodate US zipcodes

    i18n is a lot trickier (and messier) than it first appears!
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL
    "giving out my real stuffs"

  10. #10
    SitePoint Zealot
    Join Date
    Sep 2004
    Location
    Canada
    Posts
    108
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    Fortunately we are working only with Canada and province will be a drop down, so there won't be any typos...

    Country field is there just in case... Now that I think about it, I probably don't even need it...


Bookmarks

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •