Professional Web Applications Themes

Best Practice for 3NF - MySQL

I was wondering what is the best practice for dealing with addresses. I have read many articles which suggest taking out postcodes / counties / towns into their own tables to stop duplicates. But then I have also read other articles which state that doing this can create more load on the database because you are querying more tables to get the results you need. Would be grateful for suggestions regarding this. thanks Dave....

  1. #1

    Default Best Practice for 3NF

    I was wondering what is the best practice for dealing with addresses.
    I have read many articles which suggest taking out postcodes /
    counties / towns into their own tables to stop duplicates.

    But then I have also read other articles which state that doing this
    can create more load on the database because you are querying more
    tables to get the results you need.

    Would be grateful for suggestions regarding this.

    thanks
    Dave.

    Dave Guest

  2. #2

    Default Re: Best Practice for 3NF

    Message-ID: <googlegroups.com> from
    Dave contained the following:
     

    Not sure why this is a good idea unless you wanted to store other
    non-key dependent information such as the population of the town.

    --
    Regards,

    Geoff Berrow
    Geoff Guest

  3. #3

    Default Re: Best Practice for 3NF

    Dave wrote: 

    Dave,

    Normalization is always a trade off. The more normalized your database,
    the less duplication of data you have. But it also means you need to
    use more joins.

    OTOH, that lack of data duplication means tables are smaller - and less
    data has to be read. For instance, if you have a separate table for
    postcodes, matching counties and towns, you don't need the county/town
    in each of the data rows, just the zip code.

    There is no "best" way which works in all situations. Personally, I
    tend to error on the more normal form - in this case, a separate table.
    The JOIN overhead is not significant - after all, that's what RDB's
    excel at.

    But YMMV.

    --
    ==================
    Remove the "x" from my email address
    Jerry Stuckle
    JDS Computer Training Corp.
    net
    ==================
    Jerry Guest

  4. #4

    Default Re: Best Practice for 3NF

    Message-ID: <com> from Jerry
    Stuckle contained the following:
     


    I suppose the reason I never think of this is because for some reason
    this data is not in the public domain in the UK AFAIK.
    --
    Regards,

    Geoff Berrow
    Geoff Guest

  5. #5

    Default Re: Best Practice for 3NF

    On Wed, 25 Jul 2007 14:39:06 +0200, Geoff Berrow <co.uk>
    wrote:
     
    >
    >
    > I suppose the reason I never think of this is because for some reason
    > this data is not in the public domain in the UK AFAIK.[/ref]

    Same here. Well, the data itself is in the public domain, you're free to
    build your own table with all the postal codes/towns/streetnames etc.
    Getting one is another matter, and rather costly. Then again, it is still
    a regularly changing set, postal codes added and dropped, so I guess you
    pay for maintanance :)

    --
    Rik Wasmus
    Rik Guest

  6. #6

    Default Re: Best Practice for 3NF

    Rik wrote: 
    >>
    >>
    >> I suppose the reason I never think of this is because for some reason
    >> this data is not in the public domain in the UK AFAIK.[/ref]
    >
    > Same here. Well, the data itself is in the public domain, you're free to
    > build your own table with all the postal codes/towns/streetnames etc.
    > Getting one is another matter, and rather costly. Then again, it is
    > still a regularly changing set, postal codes added and dropped, so I
    > guess you pay for maintanance :)
    >
    > --Rik Wasmus[/ref]

    actually there aren't very many postal codes dropped at all. regardless,
    here's a few things you can benefit from if you look closely:
    http://www.usps.com/ncsc/addressinfo/addressinfomenu.htm

    http://www.usps.com/ncsc/addressmgmt/addressmgmtmenu.htm

    lark Guest

  7. #7

    Default Re: Best Practice for 3NF

    On 25 Jul, 15:32, lark <net> wrote: 
    > [/ref]
    > [/ref]
    > [/ref]


    >
    > actually there aren't very many postal codes dropped at all. regardless,
    > here's a few things you can benefit from if you look closely:http://www.usps.com/ncsc/addressinfo/addressinfomenu.htm
    >
    > http://www.usps.com/ncsc/addressmgmt/addressmgmtmenu.htm- Hide quoted text -
    >
    > - Show quoted text -[/ref]

    Thanks for the advice folks,

    I have another question to ask as I seem to be going round in circles
    and my head is starting to hurt.
    This is how i see it in my head / paper

    So i seperate the postcodes in to its own table
    postcode_tbl (
    postcode_id
    postcode
    postcode_town_id
    )

    I seperate towns in to their own table
    town_tbl (
    town_id
    town_county_id
    )

    and the same for counties.
    county_tbl (
    county_id
    county
    )

    So logically thinking... (i think) this set up should work, after
    joining the tables in the query?


    Dave.


    Dave Guest

  8. #8

    Default Re: Best Practice for 3NF

    On Wed, 25 Jul 2007 16:32:39 +0200, lark <net> wrote: 
    >> Same here. Well, the data itself is in the public domain, you're free
    >> to build your own table with all the postal codes/towns/streetnames
    >> etc. Getting one is another matter, and rather costly. Then again, it
    >> is still a regularly changing set, postal codes added and dropped, so I
    >> guess you pay for maintanance :)[/ref]
    >
    > actually there aren't very many postal codes dropped at all.[/ref]

    Depends on the country I assume. Most keep in practise offcourse.
     

    Thanks for the links, unfortunately, all my business is on another
    continent :P.
    --
    Rik Wasmus
    Rik Guest

  9. #9

    Default Re: Best Practice for 3NF

    On Wed, 25 Jul 2007 17:03:29 +0200, Dave
    <co.uk> wrote: 

    Why? postcode is id enough I'd say...

     

    I'm not sure how your postal system setup is, here we've got nothing to do
    with counties for instance, and a postal code never covers more then one
    street, an mostly ranges (1-200 odd, 2-200 even, or just 1-999 for
    instance).


    My setup for the Netherlands would be something like:

    towns{
    town_id,
    town_name
    }
    streets(
    street_id,
    street_name,
    town_id
    )
    postal_codes(
    postal_code, //(=primary id)
    street_id,
    no_start, //(starting house number
    no_end, //ending house number
    odds //odds=1, evens=2 or both=0
    )
    people(
    .......
    postal_code,
    houde_no, //house number
    house_no_add //add to number, like 124-a, 23-second floor, 45 -
    appt. 12
    )
    --
    Rik Wasmus
    Rik Guest

  10. #10

    Default Re: Best Practice for 3NF

    Rik wrote: 
    >>
    >> actually there aren't very many postal codes dropped at all.[/ref]
    >
    > Depends on the country I assume. Most keep in practise offcourse.

    >
    > Thanks for the links, unfortunately, all my business is on another
    > continent :P.
    > --Rik Wasmus[/ref]
    it doesn't matter really where you are, but aren't the principles the same?
    lark Guest

  11. #11

    Default Re: Best Practice for 3NF

    Dave wrote: 
    >> actually there aren't very many postal codes dropped at all. regardless,
    >> here's a few things you can benefit from if you look closely:http://www.usps.com/ncsc/addressinfo/addressinfomenu.htm
    >>
    >> http://www.usps.com/ncsc/addressmgmt/addressmgmtmenu.htm- Hide quoted text -
    >>
    >> - Show quoted text -[/ref]
    >
    > Thanks for the advice folks,
    >
    > I have another question to ask as I seem to be going round in circles
    > and my head is starting to hurt.
    > This is how i see it in my head / paper
    >
    > So i seperate the postcodes in to its own table
    > postcode_tbl (
    > postcode_id
    > postcode
    > postcode_town_id
    > )
    >
    > I seperate towns in to their own table
    > town_tbl (
    > town_id
    > town_county_id
    > )
    >
    > and the same for counties.
    > county_tbl (
    > county_id
    > county
    > )
    >
    > So logically thinking... (i think) this set up should work, after
    > joining the tables in the query?
    >
    >
    > Dave.
    >
    >[/ref]
    looks like you have it down. FYI:

    Third Normal Form (3NF) requires that all columns depend directly on the
    primary key. Tables violate the Third Normal Form when one column
    depends on another column, which in turn depends on the primary key (a
    transitive dependency).

    One way to identify transitive dependencies is to look at your table and
    see if any columns would require updating if another column in the table
    was updated. If such a column exists, it probably violates 3NF.
    lark Guest

  12. #12

    Default Re: Best Practice for 3NF

    Geoff Berrow wrote: 
    >
    >
    > I suppose the reason I never think of this is because for some reason
    > this data is not in the public domain in the UK AFAIK.[/ref]

    Yes, I do understand. It's not in the public domain in the U.S.,
    either, but you can purchase it from several sources.

    --
    ==================
    Remove the "x" from my email address
    Jerry Stuckle
    JDS Computer Training Corp.
    net
    ==================
    Jerry Guest

Similar Threads

  1. XML Datahandling Best Practice?
    By hexatropic in forum Macromedia Flash Data Integration
    Replies: 1
    Last Post: March 16th, 04:38 AM
  2. best practice / tips
    By ofeet in forum Macromedia Director 3D
    Replies: 3
    Last Post: February 28th, 03:51 PM
  3. Best practice
    By Julia in forum ASP.NET Security
    Replies: 1
    Last Post: May 17th, 11:52 AM
  4. WS-Security Best Practice?
    By Brian Greiwe in forum ASP.NET Web Services
    Replies: 7
    Last Post: February 10th, 03:41 PM

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
  •  

1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 31 32 33 34 35 36 37 38 39 40 41 42 43 44 45 46 47 48 49 50 51 52 53 54 55 56 57 58 59 60 61 62 63 64 65 66 67 68 69 70 71 72 73 74 75 76 77 78 79 80 81 82 83 84 85 86 87 88 89 90 91 92 93 94 95 96 97 98 99 100 101 102 103 104 105 106 107 108 109 110 111 112 113 114 115 116 117 118 119 120 121 122 123 124 125 126 127 128 129 130 131 132 133 134 135 136 137 138 139