Re: separate table or no

[Peter Brawley]

The set of columns b is functionally dependent on the set of columns a if there is no more than one value of b for any a. We say a determines b, or a→b. If the city is Toronto, the province is Ontario.

A table is in Boyce-Codd Normal Form (BCNF) if for every functional dependency, a→b is trivial since a is in b, or a is unique, ie a is a superkey. The city→province dependency meets the BCNF criterion if and only if every value of city is unique. If this uniqueness fails, then updates WHERE city='Stratford' will apply to cities of that name in Ontario, Quebec and Prince Edward Island---not likely the intention.

If you think about it, you could follow that logic down so many real-world dependency chains, your client finally gets tired of waiting and finds herself a less obsessive developer. So the question usually comes down to finding the point where the efficiency gains from normalisation are outweighed by their unimportance or rarity.