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

[Arthur Fuller]

Gustav,

We've been friends for decades, but you've got this one all wrong. The
point of 5NF is to eliminate the problems  your model introduces. Let me
try to express it another way. There is only one London, England, and there
is only one London, Ontario, Canada. These are distinct entities. We could
in the FE reprsent them with an amalgam of three fields concatenated, but
the point is that London, England is a different object than London,
Ontario, It is not required to ask the user for the ancillary fields, nor
should they be stored. That is what BCNF and 5NF are all about. There is
only one London, Ontario. Canada. We do not need to store the nation and
the province.

On Fri, Jan 29, 2016 at 3:40 PM, Dan Waters <df.waters at outlook.com> wrote:

> 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
>
>
> _______________________________________________
> dba-SQLServer mailing list
> dba-SQLServer at databaseadvisors.com
> http://databaseadvisors.com/mailman/listinfo/dba-sqlserver
> http://www.databaseadvisors.com
>
>


-- 
Arthur