Jamie Collins said:
It may make you feel better to learn this secret: not everyone posting
such answers understands normalization themselves. There is a reason
why people say vague things like 'properly normalized'...
Jamie.
OK, point taken. I'll quote from my big fat Access 2003 Inside Out book:
The Four Rules of Good Table Design
Rule 1: Each field in a table should represent a unique type of information.
Rule 2: Each table must have a unique identifier, or primary key, that is
made up of one or more fields in the table.
Rule 3: For each unique primary key value, the values in the data columns
must be relevant to, and must completely describe, the subject of the table.
Rule 4: You must be able to make a change to the data in any field (other
than to a field in the primary key) without affecting the data in any other
field.
Even though this doesn't describe normalization per se, they are good rules
for helping you to make normalized tables.
There are several reasons why people keep harping on normalization, but
without giving you a concrete, this-is-what-it-looks-like answer.
Normalization is a little like learning to do sums. If I ask you what 2 + 2
is, you would probably answer 4 without having to think about it. If asked
for definite proof of why 2 + 2 = 4, you would have to think a few moments,
and then you would probably hold up two fingers on both hands, and push them
together. Once you understand normalization, and can use it easily, it
becomes so natural that it is difficult to verbalize how you are doing it.
This is why entire books are written on the subject, because it takes a lot
of verbalizing to cover the subject.
Another reason that people can't give you a definite answer, is because we
don't know all of the variables or problems that you are facing. We have not
spent three months interviewing people and figuring out all of the little
details that need to be part of this database. Sometimes those little
details are precisely what is needed to have not just a normalized table
structure, but one which is truly useful for what you are trying to do.
You may want to ask yourself why you are doing this as a database, and not
just using an Excel worksheet. Usually people need to go to a database
design because they require the extra flexibility. But there is a price for
that flexibility, and that is that you have to learn how to put a database
together. I can understand your frustration, and it must seem like we are
all very stubborn and just not listening to you. You must have noticed that
we are getting rather bored and frustrated with all of this, also. You do
not seem to listen to what advice we give, you reject the ready-made
templates that are available, and you keep reposting virtually the same table
structures that you started with. None of this is helping you get a database
written.
