Re: separate table or no
Posted by: Peter Brawley
Date: February 03, 2021 11:35AM

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.

Options: ReplyQuote


Subject
Written By
Posted
January 22, 2021 11:29AM
January 22, 2021 11:36AM
January 22, 2021 06:50PM
January 23, 2021 01:15PM
January 25, 2021 10:50AM
January 26, 2021 11:39AM
January 28, 2021 11:59AM
January 29, 2021 11:13AM
February 02, 2021 12:33PM
Re: separate table or no
February 03, 2021 11:35AM


Sorry, you can't reply to this topic. It has been closed.

Content reproduced on this site is the property of the respective copyright holders. It is not reviewed in advance by Oracle and does not necessarily represent the opinion of Oracle or any other party.