Post reply

seperate city and state/province tables?

  • May 28, 2007 at 4:06 pm

    #371370

    I was wondering if it would be better to create seperate tables for the city and province/state to reduce duplication. I noticed that in the sample Northwind database they do not do this. In your opinion would it be better to create seperate tables or include them in a customer table with the address.

    Thanks

  • June 5, 2007 at 5:32 am

    #710823

    Hi Bobs

    Creating a separate table for city and province/state is probably over-normalization - you will still need a pointer (fk) in your customer table (and of course the pk in the new table), and whenever you need the customer address, you will have to join the two tables.

    You could take the argument one stage further - how about a table of surnames?

    From experience? You're far more likely to need a table to hold the full customer address separate from other customer details...what happens when your customer moves? Or has one address for remittances, and three for deliveries?

    Cheers

    ChrisM

     

    “Write the query the simplest way. If through testing it becomes clear that the performance is inadequate, consider alternative query forms.” - Gail Shaw

    For fast, accurate and documented assistance in answering your questions, please read this article.
    Understanding and using APPLY, (I) and (II) Paul White
    Hidden RBAR: Triangular Joins / The "Numbers" or "Tally" Table: What it is and how it replaces a loop Jeff Moden

  • June 5, 2007 at 3:02 pm

    #710959

    I agreed with Chris.  In most of my database design, city, state / province and zip are all in one table. 

Viewing 3 posts - 1 through 2 (of 2 total)

You must be logged in to reply to this topic. Login to reply