Take the 2-minute tour ×
Database Administrators Stack Exchange is a question and answer site for database professionals who wish to improve their database skills and learn from others in the community. It's 100% free, no registration required.

I have a table person with attributes:

id
name
city
state
country

id is the primary key. Is this table in 3NF? If 1000 people live in same city it seems redundant to store the same data for state, city, and country.

share|improve this question
add comment

1 Answer

No it is not.

Consider this data

id | name | city        | state      | country
----------------------------------------------
 1 | john | Los Angeles | California | USA
 2 | mary | San Antonio | Texas      | USA
 3 | joe  | Los Angeles | California | USA

It is clear that (city,state,country) is a candidate key for another table. Then, you would quickly realise that the cities belong to the same (state,country) combinations. Eventually, you end up with

Person
------
id
name
city_id

City
--------
city_id
name
state_id

State
--------
state_id
name
country_id

Country
----------
country_id
name
share|improve this answer
    
If it's not in 3NF, there must be a transitive dependency. Where's the transitive dependency? –  Mike Sherrill 'Cat Recall' Apr 23 '13 at 12:21
    
@MikeSherrill'Catcall' - The argument goes that country depends on state and state depends on city. Since the table shows these columns as being the key columns of the presumed geography entities, you're quite right that the strict definition of 3NF is preserved. However, for practical purposes address data is notoriously bad (spelling/typos/etc). The OP's table doesn't need to have geography normalized out as per this answer, but it probably should if the intention is to do any analysis on geography - not because 3NF demands it, but for the sake of sanity. –  Joel Brown Apr 23 '13 at 12:47
    
"The argument goes that country depends on state and state depends on city." That's not true, though, is it? State certainly doesn't depend on city in the USA; we have dozens of Springfields. There are Santa Cruzes all over the world. (Springfields, too.) A foreign key can eliminate spelling errors and typos, but that won't affect the normal form. Any time you introduce an ID number--let alone three different ID numbers--in the name of normalization, you're almost certainly on the wrong track. –  Mike Sherrill 'Cat Recall' Apr 23 '13 at 13:01
    
@MikeSherrill'Catcall' I guess it depends a little bit on your perspective. I've worked in address data quality (amongst many other things) for nearly two decades. Having dozens of Springfields could be considered an argument in favour of city_id. While the string token "SPRINGFIELD" doesn't determine the state, the actual municipality called Springfield (whichever one it happens to be) does. Whether or not that functional dependency matters will vary from system to system. If it doesn't matter in a particular system, then I couldn't agree with you more, city_id is be a bad idea. –  Joel Brown Apr 24 '13 at 2:34
    
Neither normalization nor 3NF depend on my perspective or on my opinion. 3NF depends on eliminating transitive dependencies. Where's the transitive dependency in the original table? Even if there were one, it wouldn't justify the decomposition we see in this answer. No principle of normalization justifies what we see in this answer. –  Mike Sherrill 'Cat Recall' Apr 24 '13 at 12:04
show 1 more comment

Your Answer

 
discard

By posting your answer, you agree to the privacy policy and terms of service.

Not the answer you're looking for? Browse other questions tagged or ask your own question.