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

Dan Waters df.waters at outlook.com
Fri Jan 29 14:40:05 CST 2016


Hi Arthur,

I haven't looked at 'normal forms' for tables for a long while.

However, in any situation like the one you describe where you are trying to
record a location, then you will need a tblCountries, tblStates (or
provinces or similar), and tblCities.  I don't know what form level this
would be, but it is necessary.  And, you may want 3 lookup tables to support
this scheme.

Good Luck!
Dan

-----Original Message-----
From: dba-SQLServer [mailto:dba-sqlserver-bounces at databaseadvisors.com] On
Behalf Of Arthur Fuller
Sent: Friday, January 29, 2016 12:23 PM
To: Discussion concerning MS SQL Server
Subject: [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
_______________________________________________
dba-SQLServer mailing list
dba-SQLServer at databaseadvisors.com
http://databaseadvisors.com/mailman/listinfo/dba-sqlserver
http://www.databaseadvisors.com




More information about the dba-SQLServer mailing list