Welcome to the Database Programmer. This is a blog
for anybody who wants to learn about databases.
The entries are meant to be simple and easy to read
but definitely not dumbed down.
Because most of us these days work on web sites of one sort or
another, and since all non-trivial websites
require a database, there is very good reason to
learn how databases really work.
There is a new entry every Monday morning, and the
>complete table of contents is here. We are currently
looking at
"http://database-programmer.blogspot.com/2008/01/table-design-patterns.html"
>Table Design Patterns and how they lead to tight and
efficient code.
The Example: A School Class Schedule
In this series we are using the example of an application that
manages a school of a few dozen faculty and few hundred or
perhaps a couple thousand students. Each year the school
administration must make up the actual class assignments
for each teacher, including what classroom and period each
class will be taught in. Then students must be assigned
into the classes.
There are five rules that must be followed:
- A teacher may not teach a class he/she is not qualified
to teach.
- No two classes can be given in the same classroom in
the same period.
- No teacher can be in two places at once, a teacher can
only teach one class in one period.
- No student can be in two places at once, so no student
can be in more than one class in the same period.
- A student cannot take the same class again after
passing the class once.
Last week's entry showed that rule 1 was an example
of the "http://database-programmer.blogspot.com/2008/01/table-design-patterns-cross-reference.html"
>Cross Reference Validation Pattern, and this week we are
going to see that rules 2 and 3 are an example of the
Limited Transaction pattern. Next week we will look
at rules 4 and 5, which deal with the student.
Sidebar: Discovered Requirements
This week the rules are interesting because they are
the kind that nobody would ever actually tell you. I
call such rules discovered requirements because they are
usually discovered by a programmer or database designer while
the table design or programming is under way.
These rules will not be in the specification because they are
so obvious that the customer would not think to write them down.
It is not that the customer considers putting them in and
decides not to, the rules simply never come into
the customer's mind. They do not tell you these requirements
for the same reason they do not tell you that fish live
in water and people breathe air.
Nevertheless, if you do not seek out these rules and put them
into the application, then you will get a call or an email
that something is wrong because a teacher has been put into
two rooms at the same time.
Make no mistake, the blame always falls
on the programmer, because, after all, what idiot would
write a program that would let a teacher be in two places at
once? It is very very hard to look a customer in the eye and
say, "Well somebody really should have told me about
that requirement."
Later in this series we will see more about this in an
entry I am planning called "The Requirements Will Never
Be Correct", but for now we will move on to the actual
database design.
Looking At The Table
The table is fairly easy to work out:
Rule 2 Primary Key
| |
+-----+-----+ |
| | |
CLASSROOM | PERIOD | COURSE | TEACHER | SCHEDULE_ID
----------+------------+----------+----------+-------------
XXX | XXXX | XX | XXX | XXX
XXX | XXXX | XX | XXX | XXX
XXX | XXXX | XX | XXX | XXX
XXX | XXXX | XX | XXX | XXX
----------+------------+----------+----------+-------------
| |
+--+----------------+
|
Rule 3: No teacher may be in
two places at once
The example above is a simple case of multiple unique
constraints on a single table. The term "unique constraint"
means that one or more columns must have unique values, just
like a primary key. These are sometimes called
"candidate keys" as well.
Identifying The Pattern: Limited Transactions
Th Limited Transaction Pattern occurs when there
are limitations on what
transactions are allowed. To see what I mean by a limitation,
we will look at a counter-example, a transaction table that
has no limitations. A shopping cart is a good example.
We would never
tell a customer that they may only have one order per day,
or that a salepersons may enter only one order per day, or
anything else along those lines. But the school example
is the opposite, there are several limitations on what kind
of transactions are allowed. Right now we are looking at
the limitations that can be addressed with unique constraints.
The SQL
Here is the SQL that will create the table as it is
depicted above:
CREATE TABLE schedule (
classroom char(5)
,period char(5)
,course char(10)
,teacher char(10)
,assign_id int IDENTITY
-- First define the primary key
,primary key (assign_id)
-- Rules 2 and 3 use additional unique constraints:
,constraint unique rooms_xp (period,classroom)
,constraint unique teachers_xp (period,teacher)
-- Every column in this table is actually a foreign key!
,foreign key (classroom) references classrooms (classroom)
,foreign key (period) references periods (period)
,foreign key (course) references courses (course)
,foreign key (teacher) references teachers (teacher)
)
Final Recap Of The Pattern
So let's review one final time how we ended up with this
table.
- We have a transaction table, because we have an
interaction between master elements (teachers,
courses, classrooms and periods), so we have
several foreign keys.
- We used an integer primary key as per "http://database-programmer.blogspot.com/2008/01/database-skills-sane-approach-to.html#rule4"
>Rule of Thumb 4 for Transaction Tables.
- A classroom is limited to only one course per
period, so we add a unique constraint to enforce that.
- A teacher is limited to only one course per
period, so we add a unique constraint to enforce
that.
The pattern we have is the result of a limited
transaction table, a table listing transactions in
which not every conceivable combination is actually
allowed. In this case the limits come from the
physical reality that you cannot be in two places
at once.
Conclusion: Patterns Reduce Application Code
When I first began working with databases, I had no
idea how many problems could be resolved into simple
unique constraints and foreign keys. A big step that
any code grinder takes towards becoming a real
database programmer is realizing how many seemingly
complicated and difficult tasks actually resolve down
to unique constraints and foreign keys.
Every time you can put a constraint into a database
then you reduce the complexity of your application
code. If your framework can trap server errors and
report them then you have a very simple way to enforce
a lot of the business rules of your application.
Next week we are going to look at rules 4 and 5, which
relate to rules about the student's enrollment in
certain courses.
"http://database-programmer.blogspot.com/2008/02/false-patterns-such-as-reverse-foreign.html"
>Next Essay: False Patterns and The Reverse Foreign Key
Update Contact :
No Wa/Telepon (puat) : 085267792168
No Wa/Telepon (fajar) : 085369237896
Email : Fajarudinsidik@gmail.com
No Wa/Telepon (puat) : 085267792168
No Wa/Telepon (fajar) : 085369237896
Email: Fajarudinsidik@gmail.com
atau Kirimkan Private messanger melalui email dengan klik tombol order dibawah ini :
