Design question (normalization versus practical)

[David W. Fenton]

I began my
career in such around 40 years ago, and I can assure you that
normalization to 5NF is frequently essential for quality control
purposes.

I don't think what Larry said contradicts your experience.

Make your apps (and your db schemas) as simple as necessary, but no
simpler.

When the app requires something beyond 3NF, obviously, it's
appropriate.

When it doesn't, it's foolish to waste the time on implementing a
data structure that won't provide any benefit beyond the ability to
say your app's db is normalized to 3+xNF, where X is some number
greater than 0.

 

[James A. Fortune]

An example from the days when I worked in the Purchase and Supply department
of a large manufacturing company. This was aeons ago, when relational
databases were just a twinkle in Dr Codd's eye, so we didn't actually do this
in a database, but it, and other purchasing situations like it which we
regularly encountered, rang an immediate bell when I first encountered 5NF in
John Carter's book 'The Relational Database', my first excursion into the
relational world. Its worth noting that Carter taught at a business school
(University of East London) and his book was very much aimed at real world
business situations. In fact he specifically comments in his analysis of 5NF
that the example he gives which demands it is a realistic one. After that I
moved via the shipping industry to government, so the same sort of factors
haven't very often applied in my work since those days.

Anyway, back to specifics. Our largest category of purchases was packaging.
We found that one supplier of a material we used could supply it very much
cheaper than others, saving us many thousands of pound per annum. The snag
was that the quality of the material supplied was rather variable because of
the greater tolerances applied by the manufacture of the material, which is
why it was cheaper of course.

We produced a range of consumer products and while the cheaper material was
acceptable for the lower end of the range, where the occasional minor flaw
was no problem (I think we could have flogged it wrapped in old copies of
the Sun!), the variable quality was unacceptable for the top of the range,
whose packaging had to express its premium quality, so the material from that
supplier could not be used for that range. Now this pretty well identically
reproduces Carter's example of a business model where normalization to 5NF is
required (and Date's for that matter, but I prefer to cite Carter here as he
is writing firmly in a real world business context, rather than that of the
theoretical relational model). If materials supplied by any supplier can be
used for any product then two projections of PMS
(Products_Materials_Suppliers) into PM and SM is fine. But that's not the
case so we need three projections, PM, SM and SP.

Although it’s a long time since I worked in purchase and supply the
constraints which governed us then can't have changed a lot. Would you want
that part supplied by Mickey Mouse Widgets, and perfectly suitable for
securing the ash-tray, to be used in a safety critical position in the
braking system of that new Jaguar you've just bought? Or would you prefer to
be sure its the one from Top Notch Components plc?

Ken Sheridan
Stafford, England

Thanks for such a lucid explanation of your example. I'll skip the
answers to the rhetorical questions. Note that in the U.S., Jaguar is
typically pronounced with two syllables instead of three - something
like Jag-wahr, or even Jag-wire, instead of Jeah-goo-ahr. Peugeot is
usually pronounced Poo-zhoh' or Pew-zhoh' instead of Poh'-zhoh.

James A. Fortune
(e-mail address removed)