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

[Darryl Collins]

This is a fair point. Actually dealing the American websites can be a right PITA as they often require some crazy US centric data entry to continue / buy / proceed / whatever.  Which is totally useless and meaningless for anyone outside of the country (and that is much of the planet too I might add).

Post / zip codes are a great example, as are Social security numbers.  Ironically you can often put in a fake one (or a seemingly looking real one) and continue which must screw their data up.

Anyway, I can feel rant mode ramping up so I will sign off about now..

Like all of these things I can see pros and cons on both sides.  Love what I learn here though.

Cheers
Darryl


 



-----Original Message-----
From: dba-SQLServer [mailto:dba-sqlserver-bounces at databaseadvisors.com] On Behalf Of Gustav Brock
Sent: Saturday, 30 January 2016 5:50 AM
To: Discussion concerning MS SQL Server
Subject: Re: [dba-SQLServer] The point of 5NF and BCNF

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

_______________________________________________
dba-SQLServer mailing list
dba-SQLServer at databaseadvisors.com
http://databaseadvisors.com/mailman/listinfo/dba-sqlserver
http://www.databaseadvisors.com