[dba-SQLServer] The point of 5NF and BCNF

[Gustav Brock]

Hi Arthur

Problem is that ”as far as you need” is a soft statement, so ”what you need” is based on an opinion.
Thus the discussion can go on forever.

A practical example of an issue, that may force you to something else than what you think is your need, is postal codes. If you wish to validate these and look up the associated city or town name on an international scale, you must have access to all countries’ postal codes and cities. Not only is this a major and potentially costly task, but it is also dynamic. So, if your application is not targeted this task, you will probably settle with the information entered and believe it is true; why, for example, should a customer give you a delivery address with a wrong postal code/city combination? You will move the validation to the customer.

/gustav


Fra: Arthur Fuller<mailto:fuller.artful at gmail.com>
Sendt: 29. januar 2016 19:23
Til: Discussion concerning MS SQL Server<mailto:dba-sqlserver at databaseadvisors.com>
Emne: [dba-SQLServer] The point of 5NF and BCNF

I confess that I'm growing a bit tired of this topic, but I've recently
read a dozen or so topics that suggest that 3NF (third normal form) is as
far as you need to take your normalization design. I emphatically disagree,
and pose the suggestion that BCNF (Boyce-Codd Normal Form) and 5th Normal
Form are the intelligent paths to follow when designing databases.

Let us consider an entry such as "London". The first one that comes up in
my broweser, given that it knows where I live. is London, Ontario, Canada,
with about a dozen entries. Then comes London, England, including (way
cool) a  site for renting apartments in London, England.

In a 3NF implementaition, it would be possible to select London, Denmark,
both of which individual choices make sense, but not in the real world.
There is no London in Denmark. So the choice facing the developer is a) on
the front end, refresh the dropdown box to show only the recognized cities
in Denmark, or b) to let the user play fast and loose with the rules.
Nither choice is acceptable to me. I want London, Ontario to be
distinguished from London, England -- and that's why we have BCNF and/or
5NF.

--
Arthur