TechTips: Is "third normal form" really best? 
Author Message
 TechTips: Is "third normal form" really best?

When you are designing a new database, one of the fundamental questions
you will decide is just how much redundant information will occur in the
database.  This, simplifying slightly, is the essential idea revolving
around the so-called (first vs. second vs. third) "normal forms."

In purist database-design, "nothing is said twice."  For example, an
order-form should properly refer to an item-ordered only by its code;
from this, specifically from an ITEMS table, do we obtain the item's
description and perhaps also its price.  Information that occurs in one
table is (again, simplifying slightly) "supposed to" never be repeated
in another.  Identifying numbers like part-codes are never supposed to
convey information .. only to abstractly identify the records in which
the data can be found.

Problem:  the human world doesn't always work that way.  Sometimes the
person who is filling out an order needs to be able to change the
description "this one time."  A part-coding or inventory-item coding
scheme has to extend beyond the computer, and sometimes the item-code
DOES convey meaning, to the picker in the warehouse or the packer in the
mailroom.  Furthermore, sometimes the assignment or meaning of a
particular code =does= justifiably change over the course of business,
from "then" to "now," and the old orders which were taken "then" must
not lose their meaning "now" when the item-table is changed "now" to
reflect the present business.

Solution:  Recognize that abstract rules like "normal forms" are, on the
one hand, valuable and useful concepts .. BUT on the other hand, not
ones that are carved into stone tablets and carried down a mountainside
by someone whose hair just turned white.  Over time, and no matter what
you do, "the business will change," and it's extremely important that
your system will be able to change with it.

If I may step out from the scope of a pure "tech tip" to a wee bit of
"pure opinion," certainly one of the faults I have seen in database
systems that we took-over from someone else (and much of our service
business -is- built around "inherited" databases that we rebuild) ... is
that they =are= too rigid.  Consequently they are scrapped, or gutted
and reworked, long before their useful service life should have ended.

An overly-normalized database can easily be too-rigid for the purpose of
a particular business scneario =because= it focuses so much information
into "one place and one place only."  A change made "now" has unexpected
or unacceptable consequences "now."  The system demands that the
business change to meet the system's needs, vs. the other way around.

I do not mean this to imply that there is a certain level of
normalization that is, de facto, more or less desirable than any other.
In my twenty years of experience no such absolute-laws exist (although
plenty of textbooks and seminars espouse them).  The key is flexibility
even if the price -is- redundancy.  A business system should have a
twenty-year life span; unfortunately they rarely do.

------------------------------------------------------------------
Sundial Services :: Scottsdale, AZ (USA) :: (480) 946-8259

Quote:
> Fast(!), automatic Paradox table-repair with two clicks of the mouse!
> ChimneySweep(R):  "Click click, it's fixed!" {tm}
> http://www.*-*-*.com/



Fri, 17 Oct 2003 00:11:30 GMT  
 TechTips: Is "third normal form" really best?
On Sun, 29 Apr 2001 09:11:30 -0700, Sundial Services

Quote:

>Problem:  the human world doesn't always work that way.  Sometimes the
>person who is filling out an order needs to be able to change the
>description "this one time."  A part-coding or inventory-item coding
>scheme has to extend beyond the computer, and sometimes the item-code
>DOES convey meaning, to the picker in the warehouse or the packer in the
>mailroom.  Furthermore, sometimes the assignment or meaning of a
>particular code =does= justifiably change over the course of business,
>from "then" to "now," and the old orders which were taken "then" must
>not lose their meaning "now" when the item-table is changed "now" to
>reflect the present business.

A verygood example is "list  price"
(for current transaction) for an line item.
Over a period of time, the price does change therfore storing the
[price] on the orders form is inportant for historical purposes.
The description OTOH does not change, therfore can be normalised
so I heartily endorse "justifiable" "De-Normalization" in the proper
circumstances (actually, since [selling price] IS declared to be
variable over time by business rules, this use would NOT be
necessarily a case of improper normalization.

Quote:

>If I may step out from the scope of a pure "tech tip" to a wee bit of
>"pure opinion," certainly one of the faults I have seen in database
>systems that we took-over from someone else (and much of our service
>business -is- built around "inherited" databases that we rebuild) ... is
>that they =are= too rigid.  Consequently they are scrapped, or gutted
>and reworked, long before their useful service life should have ended.

-AMEN-

Quote:
>------------------------------------------------------------------
>Sundial Services :: Scottsdale, AZ (USA) :: (480) 946-8259

>> Fast(!), automatic Paradox table-repair with two clicks of the mouse!
>> ChimneySweep(R):  "Click click, it's fixed!" {tm}
>> http://www.sundialservices.com/products/chimneysweep

Steve U
,        _
,       | \                           Steve Urbach
,       |  )erek

,  / / /                              get rid of the J U N K


Fri, 17 Oct 2003 02:34:23 GMT  
 TechTips: Is "third normal form" really best?
An adverti{*filter*}t by any other name is still an adverti{*filter*}t.
--
Don P. Mellon
Take the pig out of my wigwam, please.

= = = = =


<ADVERTI{*filter*}T DELETED>



Fri, 17 Oct 2003 03:03:27 GMT  
 TechTips: Is "third normal form" really best?

Quote:

> An adverti{*filter*}t by any other name is still an adverti{*filter*}t.

how can you be so unkind?.. Mike slaves over these TechTip pieces.. and
does a *big* service to the community by posting them, and letting them
serve as "topic" issues for a few days at a time..

and, yes, in a round-about way, Mike's messages are an advertisment for
himself and his business.. as are my posts, in a round-about way, an
adverti{*filter*}t for myself.. as are yours, of course, intentionally or
otherwise <smile>..

a hearty "thanks, Mike!" goes out from me, every time he posts another..

--

Steve Green
Corel CTech - Paradox
Diamond Software Group, Inc.
Waldorf, Maryland  USA

http://www.*-*-*.com/



Fri, 17 Oct 2003 03:27:32 GMT  
 TechTips: Is "third normal form" really best?
On Sun, 29 Apr 2001 13:03:27 -0600, "Don P. Mellon"

Quote:

>An adverti{*filter*}t by any other name is still an adverti{*filter*}t.

Even when it is being distributed GRATIS?
A number of us make an ocassional dollar doing paradox work. Thats
what keeps the food on the table.
OTOH these tips are the results of what was learned along the way
(sometime a *very* bumpy way).
For someone to take time to formalise these tidbits and make them
public deserves only praise.

Steve U

Quote:
>--
>Don P. Mellon
>Take the pig out of my wigwam, please.

>= = = = =



><ADVERTI{*filter*}T DELETED>

,        _
,       | \                           Steve Urbach
,       |  )erek

,  / / /                              get rid of the J U N K


Fri, 17 Oct 2003 22:22:54 GMT  
 TechTips: Is "third normal form" really best?


Quote:
>An adverti{*filter*}t by any other name is still an adverti{*filter*}t.

I have found these tips very useful and always mark them interesting. I
have found the quality of information from Mike Kennedy, Steve Green and
one or two of the regular pundits most helpful and always interesting.

Keep them coming!  
--
Tom Marshall



Sat, 18 Oct 2003 00:35:26 GMT  
 TechTips: Is "third normal form" really best?
On Sun, 29 Apr 2001 09:11:30 -0700, Sundial Services

[snippage throughout]

Quote:
>Problem:  the human world doesn't always work that way.  Sometimes the
>person who is filling out an order needs to be able to change the
>description "this one time."  

I'm not sure I understand you.  Are you saying you would let the
person who takes orders record an order for part 11305, which is an
engine mounting flange, and later record an another order for part
11305, but overtype "engine mounting flange" with "styrofoam cup"?

Quote:
>Furthermore, sometimes the assignment or meaning of a
>particular code =does= justifiably change over the course of business,
>from "then" to "now," and the old orders which were taken "then" must
>not lose their meaning "now" when the item-table is changed "now" to
>reflect the present business.

I'm not sure I follow you.  Are you suggesting that a well-designed
database in 3NF can't cope with that?

--
Mike Sherrill
Information Management Systems



Sat, 18 Oct 2003 03:27:35 GMT  
 TechTips: Is "third normal form" really best?


[snippage throughout]

Quote:
>A verygood example is "list  price"
>(for current transaction) for an line item.
>Over a period of time, the price does change therfore storing the
>[price] on the orders form is inportant for historical purposes.

What is this an example of?  A good reason to store a single attribute
in two places, or a good reason to distinguish between two different
attributes that happen to have the same value once in a while?  (Think
of them as "default price" or "current price" and "price actually
charged to <SomeCustomer> for <SomeProduct> on <SomeDate>", if that
helps.)

Quote:
>The description OTOH does not change, therfore can be normalised
>so I heartily endorse "justifiable" "De-Normalization" in the proper
>circumstances (actually, since [selling price] IS declared to be
>variable over time by business rules, this use would NOT be
>necessarily a case of improper normalization.

Ah.  Ok.  What's your example again?  You lost me.

--
Mike Sherrill
Information Management Systems



Sat, 18 Oct 2003 03:27:37 GMT  
 TechTips: Is "third normal form" really best?
List price is "the selling price right now."  A stored list price is "the
selling price at the time the order was placed."  This is not technically an
example of denormalization, since, in order to work correctly, a database
and code that uses it would need this information to produce the order
amount correctly.
--
Joseph Misko
Just my format("E" + chr(36) + "L1,W.2", (number(chr(45) + chr(50)) / 100))
--

Quote:
> A verygood example is "list  price"
> (for current transaction) for an line item.
> Over a period of time, the price does change therfore storing the
> [price] on the orders form is inportant for historical purposes.
> The description OTOH does not change, therfore can be normalised
> so I heartily endorse "justifiable" "De-Normalization" in the proper
> circumstances (actually, since [selling price] IS declared to be
> variable over time by business rules, this use would NOT be
> necessarily a case of improper normalization.



Sat, 18 Oct 2003 04:40:11 GMT  
 TechTips: Is "third normal form" really best?
Sharing experience and opinion is not an adverti{*filter*}t.  Anyone who would
post such a statement doesn't have the least concept of how newsgroups work.
Newsgroups work because folks who could use their valuable time raking in
more money for themselves (or taking time off) instead use that time to
benefit the entire developer community.  This is done at no charge to the
benefactors, and loss only to themselves.  Everyone knows all of the regular
posters here are paradox developers.  Do you consider all of the
contribtions to be adverti{*filter*}ts?  The contributions of Sundial Services
are not unique in this regard.  It is wrong to label their contributions as
adverti{*filter*}ts.  Instead, you are the recipient of expert experience and
advice, and they don't invoice you -- what a deal!  The television technique
also works with newsgroups -- if you don't like the adverti{*filter*}ts, it is
the viewer's prerogative to stay off of that channel.
--
Joseph Misko
Just my format("E" + chr(36) + "L1,W.2", (number(chr(45) + chr(50)) / 100))
--


Quote:
> An adverti{*filter*}t by any other name is still an adverti{*filter*}t.
> --
> Don P. Mellon
> Take the pig out of my wigwam, please.

> = = = = =



> <ADVERTI{*filter*}T DELETED>



Sat, 18 Oct 2003 05:00:54 GMT  
 TechTips: Is "third normal form" really best?
"I'm not sure I understand you.  Are you saying you would let the
person who takes orders record an order for part 11305, which is an
engine mounting flange, and later record an another order for part
11305, but overtype "engine mounting flange" with "styrofoam cup"?"

That's exactly what he is saying, except he is hoping it will be something
less radical like, "engine mounting flange w/gasket" versus "engine mounting
flange wo/gasket"   I get customers all the time who want to do this sort of
thing, and I warn them against it because nobody knows where a flange turns
into a cup, as you pointed out.

--
Don P. Mellon
Take the pig out of my wigwam, please.

= = = = =


Quote:
> On Sun, 29 Apr 2001 09:11:30 -0700, Sundial Services

> [snippage throughout]
> >Problem:  the human world doesn't always work that way.  Sometimes the
> >person who is filling out an order needs to be able to change the
> >description "this one time."

> I'm not sure I understand you.  Are you saying you would let the
> person who takes orders record an order for part 11305, which is an
> engine mounting flange, and later record an another order for part
> 11305, but overtype "engine mounting flange" with "styrofoam cup"?

> >Furthermore, sometimes the assignment or meaning of a
> >particular code =does= justifiably change over the course of business,
> >from "then" to "now," and the old orders which were taken "then" must
> >not lose their meaning "now" when the item-table is changed "now" to
> >reflect the present business.

> I'm not sure I follow you.  Are you suggesting that a well-designed
> database in 3NF can't cope with that?

> --
> Mike Sherrill
> Information Management Systems



Sat, 18 Oct 2003 05:02:45 GMT  
 TechTips: Is "third normal form" really best?
Uh huh ...



Quote:
>Sharing experience and opinion is not an adverti{*filter*}t.
>Anyone who would post such a statement doesn't have the least
>concept of how newsgroups work. Newsgroups work because folks
>who could use their valuable time raking in more money for
>themselves (or taking time off) instead use that time to
>benefit the entire developer community.

--
Lyle
http://www.*-*-*.com/


Sat, 18 Oct 2003 05:44:00 GMT  
 TechTips: Is "third normal form" really best?
When I received this bisyllabic reply, I was quite surprised at the low
level of discussion, since the Paradox groups is normally so well conducted.
Then I noticed that this message was not only sent to Paradox, but to Access
and Delphi as well.  If I had noticed that to start, I would not have
bothered to respond.  I expect these replies came from one of those groups,
and I am not surprised.
--
Joseph Misko
Just my format("E" + chr(36) + "L1,W.2", (number(chr(45) + chr(50)) / 100))
--

Quote:
> Uh huh ...



> >Sharing experience and opinion is not an adverti{*filter*}t.
> >Anyone who would post such a statement doesn't have the least
> >concept of how newsgroups work. Newsgroups work because folks
> >who could use their valuable time raking in more money for
> >themselves (or taking time off) instead use that time to
> >benefit the entire developer community.

> --
> Lyle
> http://www.*-*-*.com/



Sat, 18 Oct 2003 06:07:47 GMT  
 TechTips: Is "third normal form" really best?
Oh ...



Quote:
>When I received this bisyllabic reply, I was quite surprised
>at the low level of discussion, since the Paradox groups is
>normally so well conducted. Then I noticed that this message
>was not only sent to Paradox, but to Access and Delphi as
>well.  If I had noticed that to start, I would not have
>bothered to respond.  I expect these replies came from one of
>those groups, and I am not surprised.
>--
>Joseph Misko
>Just my format("E" + chr(36) + "L1,W.2", (number(chr(45) +
>chr(50)) / 100)) --


>> Uh huh ...



>> >Sharing experience and opinion is not an adverti{*filter*}t.
>> >Anyone who would post such a statement doesn't have the
>> >least concept of how newsgroups work. Newsgroups work
>> >because folks who could use their valuable time raking in
>> >more money for themselves (or taking time off) instead use
>> >that time to benefit the entire developer community.

>> --
>> Lyle
>> http://www.*-*-*.com/

--
Lyle
http://www.*-*-*.com/


Sat, 18 Oct 2003 06:12:41 GMT  
 TechTips: Is "third normal form" really best?
While you are over there reading adverti{*filter*}ts disguised as "TechTips," we
are over here answering real questions from real people.  Cross-posted
TechSpam is just an annoyance.

Now count those syllables.

--
Don P. Mellon
Take the pig out of my wigwam, please.

= = = = =


Quote:
> When I received this bisyllabic reply, I was quite surprised at the low
> level of discussion, since the Paradox groups is normally so well
conducted.
> Then I noticed that this message was not only sent to Paradox, but to
Access
> and Delphi as well.  If I had noticed that to start, I would not have
> bothered to respond.  I expect these replies came from one of those
groups,
> and I am not surprised.
> --
> Joseph Misko
> Just my format("E" + chr(36) + "L1,W.2", (number(chr(45) + chr(50)) /
100))
> --


> > Uh huh ...



> > >Sharing experience and opinion is not an adverti{*filter*}t.
> > >Anyone who would post such a statement doesn't have the least
> > >concept of how newsgroups work. Newsgroups work because folks
> > >who could use their valuable time raking in more money for
> > >themselves (or taking time off) instead use that time to
> > >benefit the entire developer community.

> > --
> > Lyle
> > http://www.*-*-*.com/



Sat, 18 Oct 2003 06:29:01 GMT  
 
 [ 22 post ]  Go to page: [1] [2]

 Relevant Pages 

1. The keys "-"/"+"/"*"

2. TechTips: "Hot Backups?"

3. TechTips: "Table is full?"

4. ""256 Color Mouse""

5. D2-Form "modeless" yet modal

6. TechTips: "Index out of date" ... best suggestions

7. What are "Denormals" and "Nans"

8. Utility to convert "asm" to "inline( )"

9. "delete from" followed by "insert into" = AV

10. "Replace Connection", "Unable to connect to: PARADOX".

11. HELP!!: "Multiple net files","Lock file has grown too large"

12. A problem in creating equal sized "packets" from a set of "packages".

 

 
Powered by phpBB® Forum Software