Best Practice for 3NF

Dave wrote:
> On 25 Jul, 15:32, lark <ham...@sbcglobal.net> wrote:
>> Rik wrote:
>>> On Wed, 25 Jul 2007 14:39:06 +0200, Geoff Berrow <blthe...@ckdog.co.uk>
>>> wrote:
>>>> Message-ID: <DNOdnZLj-tpsrzrbnZ2dnUVZ_sKqn...@comcast.com> from Jerry
>>>> Stuckle contained the following:
>>>>> 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.
>>>> 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.
>>> 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
>> 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...ssinfomenu.htm
>>
>> http://www.usps.com/ncsc/addressmgmt...smgmtmenu.htm- Hide quoted text -
>>
>> - Show quoted text -
>
> 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.
>
>
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.

Geoff Berrow wrote:
> Message-ID: <DNOdnZLj-tpsrzrbnZ2dnUVZ_sKqnZ2d@comcast.com> from Jerry
> Stuckle contained the following:
>
>> 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.
>
>
> 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.

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.
jstucklex@attglobal.net
==================