This is the first in a series of essays that
will help you develop your database skills from the
beginning to the advanced.
This essay is part of the Keys, Normalization and Denormalization series.
There is also a Complete Table of Contents and a >Skills-oriented Table Of Contents.
Since all
non-trivial websites require a database,
there is plenty good reason to
master the principles of database programming.
Database skills are very different from coding skills.
In many cases, the reflexes and intuition that lead an
experienced programmer into great code will just as
easily lead his database efforts into one classic blunder
after another. To avoid these blunders, and indeed to
produce masterful database sites, the programmer
must learn something of the database in its own terms.
The recurring theme of these essays will be that
good database design makes for good and efficient
code, the two ideas do not oppose each other.
Another basic thrust of the entire series will be
that databases should be learned and understood
in their own terms, instead of trying to gloss over
the differences between database design and
coding.
The Example
During the series we are going to build a school database,
and in this first essay we are going to look only
at the start and end times for the class periods.
Our school administrator,
Dr. Ann Clayborne, explains the schedule to us in plain
English like so:
Each day is divided into 8 periods, where period 5 is always
lunch. The day begins promptly with homeroom at 8:00 am which
lasts for 10 minutes. Period one begins at 8:15 am. All periods
are 45 minutes and there is a five minute break between each
period. On assembly days the periods are 35 minutes each. If
we are going to have a short assembly the periods are 40
minutes. Assemblies are at the end of the day and fill out
the remainder of day. Sometimes we have the short assembly
after lunch.
Figure it Out or Spell it Out?
Our assignment is to code up a screen that displays
the start and end times of each period, including
homeroom, lunch, and an assembly if necessary. The
start and end times of periods will vary based on
what kind of day it is.
For this introduction, I want to begin with the
different mindsets that can be used to get the job
done. In a nutshell, a code grinder is going to
figure it out, while a database programmer
is going to spell it out. Let us look at how
the coder thinks of the problem, and then we will look
at how the database programmer thinks of the problems.
The coder says to himself something like, "Well let's see, I'll
hardcode homeroom at the start of each day. Then I'll need a loop
that goes from one to eight. I'll hardode 8:15 as the start of
period one, and depending on the day, I'll keep adding 35, 40, or
45 minutes to figure out the end of the period. Then I'll add
5 minutes to get the start of the next period. If there's an
assembly I'll just have to add some if/else stuff in there. Oh,
wait, I'll also have to calculate ahead of time the end of the
day so I can work out the length of an assembly, since they
did not tell me that."
Now let us consider what the database programmer might do.
He says to himself, "Hmmm, I think I'll make a table
that lists the start and end times of
each period for each kind of day. I will put the assemblies
and homeroom in there. On the scheduling page I'll just
pull the schedule out of the database and put it on the screen."
The coder has cast the problem in terms of actions,
which is what programs are all about.
This may seem so obvious
that there is no reason to bring it up, but
but we do need to make this point strongly. No matter what
language or orientation
(procedural, object, aspect, functional), every program
always comes down to a series of actions take in sequence.
It is not so much knowing this that is important as
remembering this when making design decisions, as
we shall see in this series.
The database programmer, on the other hand, has cast the
problem in terms of information he can store for later
retrieval. Our example, while very simple, illustrates
very clearly how
different mindsets approach a problem. The coder thinks in
terms of a series of actions while the database
programmer thinks in terms of storing and retrieving facts.
If we need a bumber sticker for this or a T-shirt slogan, we
restate our starting point, which is that the
coder wants to figure it out while
a database programmer wants to spell it out.
Conclusion: the Tables and the Code
So we know that the database programmer is going to
create tables and populate them with the data that
he can then read out of the screen. A portion of
the table would look like so:
DAY_TYPE | PERIOD | SEQUENCE | START | END
-----------+---------+-----------+-----------+----------
NORMAL | HOME | 0 | 8:00 am | 8:10 am
NORMAL | 1 | 1 | 8:15 am | 9:00 am
NORMAL | 2 | 2 | 9:05 am | 9:50 am
NORMAL | 3 | 3 | 9:55 am | 10:40 am
NORMAL | 4 | 4 | 10:45 am | 11:30 pm
NORMAL | LUNCH | 5 | 11:35 pm | 12:20 pm
NORMAL | 6 | 6 | 12:25 pm | 1:10 pm
NORMAL | 7 | 7 | 1:15 pm | 2:00 pm
NORMAL | 8 | 8 | 2:05 pm | 2:50 pm
SHORT | HOME | 0 | 8:00 am | 8:10 am
SHORT | 1 | 1 | 8:15 am | 8:50 am
SHORT | 2 | 2 | 8:55 am | 9:30 am
SHORT | 3 | 3 | 9:35 am | 10:10 am
SHORT | 4 | 4 | 10:15am | 10:50 am
SHORT | LUNCH | 5 | 10:55 am | 11:30 am
SHORT | 6 | 6 | 11:35 am | 12:20 pm
SHORT | 7 | 7 | 12:15 pm | 12:50 pm
SHORT | 8 | 8 | 12:55 pm | 1:30 pm
SHORT | ASMB | 9 | 1:35 pm | 2:50 pm
....
.... same for other two schedules
....
Returning now to the main theme, that
good database design makes
for good and efficient code, we look at the
code that
would display the periods for a single day (assume
that $DAY is sanitized):
$rows=SQL_AllRows(
"SELECT * FROM scheduleperiods
where day_type=$DAY
ORDER BY sequence"
);
foreach($rows as $row) {
// Display start and end times
}
I will contend here with little argument that you cannot
reduce the code to anything simpler than that. Any
program that does not already know the period start
and end times will have to figure them out, and will
be more complicated, therefore harder to code, and
more likely to contain errors.
In the next essay we will look at this table again,
along with two other tables, and we will
demystify primary and foreign keys.
Appendix: Fixing A Mistake
There is a deliberate mistake in the data listed
above. How easy is it to fix, and how does this
compare with fixing a mistake in code?
Other Posts
This essay is part of the Keys, Normalization and Denormalization series.
There is also a Complete Table of Contents and a >Skills-oriented Table Of Contents.
