Showing posts with label table design. Show all posts
Showing posts with label table design. Show all posts

Monday, January 14, 2008

Database Skills: A Sane Approach To Choosing Primary Keys

Welcome to the Database Programmer. This blog is for anyone who wants to learn about databases, both simple and advanced. Since all non-trivial websites require a database, and since database skills are different from coding skills, there is very good reason for any programmer to master the principles of database design and use.

Every Monday morning there is a new entry. The complete table of contents for the Monday morning series is here.

This week's entry is rather long. I strongly considered splitting it into two weeks, but decided to keep it as one so that it would be an easier source of reference in the future.

There is no One True Primary Key

There are several competing theories out there on how to choose primary keys. Most of them tell you to use a single kind of key for all tables, usually an integer. In contrast to those theories I have found that a robust application uses different kinds of keys for different kinds of tables. In the last 15 years I have worked on projects large and small, simple and complex. Sometimes I had total technical control, and sometimes I had to work with what others gave me, and sometimes it was a little of both. Today's essay reflects what I have worked out in those years, and how I build my tables today. My goal is to report what actually works, not to promote a particular theory about how everybody should do something.

This week we will see "rules of thumb". A rule of thumb is a guiding idea that will tend to hold true most of the time, but which you may decide to change in certain circumstances.

Rule of Thumb 1: Use Character Keys For Reference Tables

A reference table is one that tends to be strongly constant over time and has relatively few columns. Sometimes a reference table may come already populated by the programmer. Examples include tables of country codes (perhaps with international telephone prefixes), a table of provinces or states within a country, or a table of timezones. In this series I have been using the example of a school management program, for that program we might give the user a reference table of school subjects like history, math, physics and so forth.

For these tables it is best to make a character primary key, which we often call a "code", as in "timezone code" or "country code" or "subject code." The strategy is to make a code which can be used on its own as a meaningful value that people can understand. This gives us tables that are easier to use for both programmer and end-user.

Let's consider our school management program. We have a table of teachers (populated by the school staff), and a table of subjects which we have provided as a reference table. When a teacher joins the faculty, somebody must enter the subjects that that teacher is qualified to each. The tables below show two examples of what this table might look like, which is easier to read?

TEACHER - SUBJECT CROSS REFERENCE

EXAMPLE 1: INTEGER KEYS           EXAMPLE 2: CHARACTER KEYS

Teacher | Subject                 Teacher     |  Subject
--------+----------               ------------+-----------
72      | 28                      SRUSSEL     |  PHYSICS
72      | 32                      SRUSSEL     |  CALCULUS
72      | 72                      SRUSSEL     |  HISTORY
45      | 28                      ACLAYBORNE  |  PHYSICS
45      | 29                      ACLAYBORNE  |  CELLBIOLOGY
45      | 45                      ACLAYBORNE  |  RUSSIAN

The table of character keys is much easier to work with, for the simple reason that many times you can just use the codes themselves, so you can avoid a lot of JOINs to the main tables. With integers you must always JOIN to the master table so you can get a meaningful value to show the user. But not only is the table itself easier to read when you are debugging, it is easier to work with when writing queries:

-- The character key example is pretty simple:
SELECT teacher,subject FROM teachers_x_subjects

-- The integer key absolutely requires joins
SELECT x.teacher_id,x.subject_id
       t.name,s.description
  FROM teachers_x_subjects x
  JOIN teachers t ON x.teacher_id = t.teacher_id
  JOIN subjects s ON x.subject_id = s.subject_id

I often hear people say they do not like SQL because it is so complicated and they hate doing so many JOINs. It makes me wonder if the person is lost in a JOIN jungle caused by very bad advice about always using integer primary keys.

If you are using some kind of ORM system that tries to protect you from coding any SQL, that basic problem of over-complicated tables will still appear in your code. One way or another you must enter details that tell the ORM system how to get the descriptions, which would not be necessary if the keys were meaningful character values.

We can now see the surprising fact that the integer keys will slow us down in many situations. Not only do they have no performance advantage, but they actually hurt performance. The reason is because they require joins on almost every query. A 3-table query with two joins will always be much slower than a 1-table query with no joins. If you are using an ORM system that does not do JOIN's, but instead does separate fetches, then you have 3 round trips to the server instead of 1, and heaven forbid you have queries in a nested loop, the performance will simply crash and burn. All of this is kind of ironic since you so often hear people blindly repeat the dogmatic phrase "We will use integer keys for performance reasons..."

Rule of Thumb 2: Use Character Keys for Small Master Tables

Many database programmers use the term "master table" to mean any table that lists the properties of things that have some permanence, like customers, teachers, students, school subjects, countries, timezones, items (skus), and anything else that can be listed once and used many times in other places. Generally a master table has more columns than a simple reference table.

Some master tables are small and do not change often. In our ongoing example of a school management application, the list of teachers is a good example of a small master table. Compared to the list of students, which is much larger and changes every year, the table of teachers at most schools (except for huge state universities) will have only a few changes each year.

For tables like this it is good to allow the user to enter character keys if they want to. Some schools will insist on being allowed to choose their own codes like 'SRUSSEL' for "Saxifrage Russel", while others will say, "Why should I have to make up a code, can't the computer do that?"

For these tables I have found it useful to always define the primary key as a character column, and then to allow some flexibility in how it is generated. Common ways of generating codes include:

  1. Letting the user make up their own code
  2. Generating a code out of some other column or columns, like first letter of first name, plus 5 letters of last name, plus three numeric digits. (This used to be very popular in decades past).
  3. Generate a number.

The key idea here is to follow the needs of your users. Option #2 above is one of the most useful because it gives you the best of both worlds.

Rule of Thumb 3: Use Integers For Large Master Tables

Some master tables are large or they change often, or both. In our ongoing example of a school management application, the list of students will change every year, with many students coming and going. Another example is a doctor's office that has patients coming and going all of the time. I have found it best to use plain integer keys here because:

  • Unlike small master tables (like teachers) or reference tables (like school subjects), a code is not likely to have any meaning for the end-user, so the biggest argument for using it does not hold.
  • Unlike reference tables, the master table is likely to have many more columns and you will probably end up JOINing to the table many times. This means our other big reason for using codes, which is to avoid JOINs, does not hold either.
  • It is not realistic to expect end-users to be making up codes for large tables, and since the codes will have no value, why should the end-user be troubled with the job?
  • Writing algorithms to generate unique codes will run into more difficulties, and since the code has no value why bother?

Rule of Thumb 4: Use Integers For Transaction Tables

Many database programmers use the term "transaction table" to mean any kind of table that records some kind of interaction or event between master tables. In an eCommerce program the shopping cart tables are all transaction tables, they record the purchase of items by customers. In our school management program the actual classes taken by students are transactions, because they record specific interactions between students and teachers.

For these tables the auto-generated integer key tends to be the most useful. I am not going to present any arguments for this because most programmers find it self-evident. It should be enough to say that any attempt to use a compound key (like customer + date ) always ends up causing a problem by limiting what can be entered, so the meaningless integer key is the way to go.

Rule of Thumb 5: Use Multi-Column Keys In Cross References

A useful database will end up with a lot of cross reference tables in it. A cross-reference table is any table that lists various facts about how master tables relate to each other. These tables are extremely useful for validating transactions. In fact, next week's entry will be all about these tables and how to use them.

For now the important point is that the primary key of a cross-reference is a combination of the foreign keys. We do not make up an extra column, either integer or character.

TEACHER-SUBJECT CROSS REFERENCE

Teacher     |  Subject
------ -----+-------------
SRUSSEL     |  PHYSICS
SRUSSEL     |  CALCULUS
SRUSSEL     |  HISTORY
ACLAYBORNE  |  PHYSICS
ACLAYBORNE  |  CELLBIOLOGY
ACLAYBORNE  |  RUSSIAN

The SQL for this table would resemble something like this:

CREATE TABLE teachers_x_subjects (
    teacher char(10)
   ,subject char(10)
   ,primary key (teacher,subject)
   ,foreign key (teacher) references teachers(teacher)
   ,foreign key (subject) references subjects(subject)
)

The reasons for this are rather complex, and next week the entire entry will be devoted to this and similar ideas. For now we will note that this approach lets us validate teacher-class assignments so that no teacher is assigned to teach a class she is not qualified for. Using a new column as a primary key does not allow that, and therefore leads to more complicated and error-prone code.

Rule of Thumb 6: Use Given Keys For Non-Insert Imports

Many systems today that we create will interact with systems that already exist. A typical eCommerce program will get a list of items and maybe even customers from the company's main computer system.

For some of these tables, your own system will absolutely never make new rows. A very common example is a table of items on an eCommerce site that is loaded up from some other computer system.

For these tables, the simplest route is to use whatever key exists on the table as it is given to you. Any other route involves more work with no clear motivation for putting out the effort.

Rule of Thumb 7: Use Integer Keys for Import/Export Tables

Sometimes you may have a table whose original values come from another system, but unlike the previous case your own system is generating new rows for the table, and you may have to send these rows back to the original system.

One classic example of this is a list of customers. I created a website a few years ago where the list of customers is updated from a different system from time-to-time. However, new customers can also sign up online. Both systems are handing the customer list back and forth from time to time to keep them reconciled.

In these cases I have an integer primary key for the table because it follows Rule of Thumb 3, it is a large master table. The most important concept here is that you must not try to combine your key and the key from the original table. Keep the key from the original table in its own column, index on it, and use it for updates, but do not try to enforce it as a unique column. The other system must take care of its own key, and your system must take care of yours.

Rule of Thumb 8: Use An Object Id On All Tables

Back when people were getting excited about the concept of "Object-Relational Databases", they came up with the term "object id" to denote a column that contains some unique value but otherwise has no meaning. The same idea exists with different names, but Object ID is now the term that most people understand so that is the term I will use.

Your programs can be made simpler in many cases if you add an object id to every single table in addition to the primary key. An object id is useful specifically for user interface code. If you use an object id, then it is easier to write UPDATE and DELETE statements, and it is easier to write framework or ORM code that does these things for you.

If you are following these rules of thumb closely in your project then it is important not to use the object id as a primary key, and therefore you may never use it as a foreign key either. If you use an object id as the primary key then you lose a lot of the benefits of the character keys listed above.

Also if you follow these rules in your projects it means that your transaction tables have both an auto-generated primary key like CART_ID and an auto-generated object id. Some programmers are bothered by this because we don't like the idea that two columns appear to be doing the same thing, and we try to save a column. But personally this does not bother me because it helps me write robust applications, and this is not 1985 where a 10MB hard drive cost hundreds of dollars.

Absolute Rule 1: Only Atomic Values

This is not merely a "rule of thumb" but a rule that I follow absolutely. It is actually part of First Normal Form, which is that column values must be atomic, or indivisible. Another way to say it is that the column must not have "subvalues" buried in it.

I have included this rule here instead of with First Normal Form because when most programmers violate this rule they are making primary keys by combining different values together. In our example of a school program, if we have a list of the actual students taking classes in a given school year, you might have a squashed-up primary key column like this:

CLASS_CODE                | STUDENT
--------------------------+---------------
SRUSSEL-2007-PHYSICS      | NAI
SRUSSEL-2007-MATH         | PCLAYBORNE
ACLAYBORNE-2007-RUSSIAN   | JBOONE
ACLAYBORNE-2007-MATH      | NAI

There are two practical problems with doing this:

  1. You cannot use a foreign key to validate the sub-values, so you must code validation manually.
  2. Retrieving the sub-values requires extra code, either in the SELECT or in your client code. If the values were in separate columns this would not be necessary.

Absolute Rule 2: No Magic Values

Another rule that I follow is to absolutely never have magic values. A magic value is a value in a column that causes some non-obvious result. I have included this is in this essay because most programmers who break this rule do so by hard-coding special actions to occur based on values of keys in reference tables and master tables.

An example might be a table of teachers, where one of the teacher values is something like "SUBSTITUTE", and the program is hardcoded to do a lot of different things when it sees this value. Magic values are bad because the code is harder to debug. It may not be obvious to a programmer that some special value of the TEACHER column would cause special actions to occur. But if you have a column called FLAG_SUBSTITUTE then any programmer who must maintain code written by somebody else will have a much easier time of it.

Magic numbers also confuse end-users. It may seem obvious to us that the value "SUBSTITUTE" in the teacher column means substitute, but if this value causes other things to occur, and we are in the regular habit of having these values in lots of tables, then the compound effect can be lots and lots of phone calls from confused users, and big trouble for the software developer's bottom line.

Finally, magic numbers limit you. If you use the value "SUBSTITUTE" as a single teacher in the teachers file, then how do you keep track of the dozen-odd substitutes the school may hire in a year? The end-user is stuck here, they must use pen and paper. It is much better to allow them to enter the substitute as a regular faculty member with a FLAG_SUBSTITUE column to check off.

Magic numbers have plagued programming since long before databases came around. Here is a link to The Jargon File, which talks about magic numbers in other contexts.

Conclusion: Many Kinds of Tables, Many Kinds of Keys

This week we have seen that there can be many practical benefits to using different kinds of keys for different kinds of tables. Using the right kind of key for the right kind of table leads to simpler code and better performance, whether you code SQL directly or use an ORM system.

Remember always that your application will always follow the same structure as your tables. If the tables are designed well, the code will be lean, tight, efficient, and robust. Because table design is so important, it is best to know well the different kinds of tables there are: reference, master, cross-reference, and transaction, and to build the keys wisely.

Next week we will zoom into cross references and find out how important they are in good table design and how they will improve your applications.

Next Essay: Cross Reference Validation Pattern

Monday, January 7, 2008

Database Skills: Third Normal Form and Calculated Values

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.

Update: There is a new essay on Business Logic that also goes into how calculated values affect your code paradigm.

Third Normal Form Defined

Third normal form is pretty simple. In plain terms it means that no column can depend on a non-key column. In technical terms we say that there are no "transitive dependencies."

The example below shows a table that is not in third normal form. The problem is that the letter grade depends on the numeric grade:

SUBJECT  |  YEAR | TEACHER |  STUDENT    | GRADE |  LETTER
---------+-------+---------+-------------+-------+-----------
HIST-101 |  2008 | RUSSELL |  NIRGALAI   | 80    |   B
HIST-101 |  2008 | RUSSELL |  JBOONE     | 90    |   A
HIST-101 |  2008 | RUSSELL |  PCLAYBORNE | 95    |   A

To repeat: The key is SUBJECT + YEAR + TEACHER + STUDENT, there are no duplicate values of this combination of columns. In plain terms we mean that no student can be listed in the same class twice, which only makes sense after all. We note next that there is only one actual property of the table, the GRADE column. This is the student's final grade in a particular class.

The violation is the last column, LETTER, which is not a property of the key but is functionally dependent upon the GRADE value. The term "functionally dependent" means that column LETTER is a function of GRADE.

As always, normalization is meant to help us. The problem with the table as-is is that you cannot guarantee that when the GRADE column is updated that the LETTER column will be correctly updated. The non-normalized table can lead to what we call UPDATE ANOMALIES.

Problem: Calculated Values Are a Fact of Life

We have seen in prior essays that enforcing first normal form and second normal form leads to more tables and simpler code. We saw in both cases that a programmer's natural tendency to use a "simple" solution of fewer non-normalized tables leads to more complicated and error-prone code. But when we come to third normal form the benefits do not seem so clear-cut.

Third normal form tells us to leave out the LETTER column, but the plain fact is that the LETTER column is very useful! The situation will only get worse if there are a few dozen (or a few hundred!) tables. If we leave out all calculated values then we must provide for them outside of the database, but if we put them into the tables we risk all kinds of errors in live data.

This is where things get sticky. In this week's essay I am going to explain the classic solutions to this problem, and include my own as the last one. It is my personal opinion that the problem of calculated values is at the heart of many of the flame wars, arguments and battles that are fought in the arena of database application development. You can't live with 'em but you can't live without 'em. Some kind of logical approach is required that gives us the benefits of normalization and the benefits of calculated values.

Preserving 3NF With Another Table

In many cases something that looks like a calculation can actually be removed out to another table and turned into a foreign key. As a matter of fact, the example I gave above can be moved out to another table:

GRADE  | LETTER
-------+---------
 100   |  A
  99   |  A
  98   |  A
  97   |  A
  96   |  A
  95   |  A
  94   |  A
  93   |  A
  92   |  A
  91   |  A
  90   |  A
  89   |  B
  88   |  B
  87   |  B
  86   |  B
  ....
  .... etc ....
  ....
  1    |  F
  0    |  F

Most programmers would not think of a solution like this because it has too many rows! Your basic programmer finds it ugly, so he will not use it. This underscores a recurring theme of this series: skills that lead to good coding will lead to bad table design. Table design should be done according to the principles of table design, not the principles of coding. The customer will never know you have an "ugly" table, they will just know your system works well.

There are certain benefits that you always get when you normalize and when you put everything into tables. The big advantage is that you can change your system without modifying a program file. In the United States we would leave my "ugly" table as it is for public schools, but for many private schools we would change it to 93-100 for an "A", 85-92 for a "B", and so forth. We could make 70-100 "P" for pass and 0-69 "F" for fail. We can do all of this without changing any program code, which is always a good thing.

Preserving 3NF With a View

A "database view" is a SELECT statement that is given a name and stored permanently in the database. The nifty thing about views is that they can be queried with a SELECT statement just like a table. A view is a great tool for preserving normalization because you can keep the table itself fully normalized and put the calculated values in the view. In most databases a view is created with syntax like this:

CREATE OR REPLACE VIEW grades_extended AS
SELECT subject, year, teacher, student , grade
      ,CASE WHEN grade >= 90 THEN 'A'
            WHEN grade >= 80 THEN 'B'
            WHEN grade >= 70 THEN 'C'
            WHEN grade >= 60 THEN 'D' 
            ELSE 'F' END as LETTER
 FROM grades;
 
-- this command pulls only from the normalized table
SELECT * FROM grades

-- this command gives letter grades also
SELECT * FROM grades_extended

Because a view is simply a SELECT statement, a view can JOIN information from multiple tables, and it can also do aggregate functions like SUM, AVG and so forth, so views are very powerful indeed.

Views are very popular and can really help out in simple situations. I have found that they become very difficult to manage as the table count increases, and so I personally do not use them for this purpose anymore, but I would not discourage anybody who wants to try them out. They will probably benefit you a lot.

Preserving 3NF by Calculating As Needed

Another approach is to keep the calculated columns out of the database and calculate them as needed.

Some years ago I was made Lead Systems Architect on a package that already had bout 750 tables when I took the position. The tables were in strict third normal form: no calculated values, no exceptions.

All calculations were made as needed, and they were all performed in program code. This particular program was an ERP package, so there were a great many monetary calculations. Absolutely nothing about it was simple, every single calculation had many possible formulas that took into account discounts, locations, customer types, time of year, and as far as I could tell the sunspot cycle and the phase of the moon.

The sales team was very successful, and so this program was under heavy modification at all times by programmers on two continents. The end result was this:

  • Many calculations involved calls to subroutines in 2, 3 or 10 different programs, making them nearly impossible to debug.
  • The program was completely impossible to document, we didn't even try.
  • The program was impossible to test, because of course nobody really knew what it was supposed to be doing.
  • Performance was often terrible. The need to calculate every value on every access put a huge strain on the database as information was pulled over and over and cycles were spent doing calculations.
  • Only a few people "knew where the bodies were buried", which contradicts standard advice for keeping staff interchangeable.
  • Sometimes a customer would print an invoice, then print it a few months later after an upgrade and get different numbers, which we could not explain or correct except at great time and expense.

When somebody tells me to keep 3rd normal form religiously and keep all calculations in program code I say "been there, seen that, no thanks."

Discarding 3NF by Using Program Code

The mostly popular approach by far is to calculate and store derived values using program code.

Most programmers use some kind of object-oriented language, and are used to the idea that they will have a GRADES class that handles the calculations for the GRADES table. Whenever anybody inserts or updates the table, some method looks at the GRADE column (a value from 0-100) and re-calculates the LETTER code, then saves the whole thing in the table. This violates 3NF but makes the database more fully populated and therefore more useful.

The major advantage to this approach is that most programmers understand how to do it with little prompting or explanation. Moreover, it does make the database indeed much more useful.

The largest problem with this approach is that subvertible. Unless the programmer is absolutely certain that nobody can access the database except through her code, the possibility always remains that a different program will mess up the values. In fact, you must be able to guarantee that even within your program no rogue programmer can accidentally (or maliciously) issue commands that subvert the values. If you get even one prima donna on your team that does not believe in following rules, serious damage can be done.

Some programmers say, "that's ok, my team is small and tight and only my program will access the data," but this just means they never plan to sell a program to a company that has their own IT department. That company will demand access to the database, possibly even write access, and then you have a problem.

However, when all is said and done, this is a perfectly valid way to get working programs written. When it is reasonable to expect that nobody else can access the database, and you trust your team, then this method may be the Right Thing.

Discarding 3NF With Triggers

The last approach I will explain is the one that I use myself. In this approach you put a "trigger" on the table that performs the calculation for you. A trigger is a small program that resides in the database server itself and is written is some kind of SQL-like extended language (or Perl or C or anything supported by your server). The trigger executes whenever certain events occur in the database. Specifically, every trigger fires for exactly one table, and might fire before or after an event, and might fire for insert, update, or delete. Triggers are ideal for performing calculations.

Trigger code tends to be highly specific to the platform you are using. The code below works for PostgreSQL and will definitely not work for MS SQL Server or MySQL, you will have to look up the syntax for those systems and do a little translation. But here it is:

CREATE OR REPLACE FUNCTION grades_ins_before_f RETURNS TRIGGER AS
$$
BEGIN
    new.letter = CASE WHEN new.grade >= 90 THEN 'A'
                      WHEN new.grade >= 80 THEN 'B'
                      WHEN new.grade >= 70 THEN 'C'
                      WHEN new.grade >= 60 THEN 'D'
                      ELSE 'F' END;
END
$$ LANGUAGE PLPGSQL SECURITY DEFINER;

CREATE TRIGGER grades_ins_before_t BEFORE INSERT OR UPDATE 
    ON GRADES  
    FOR EACH ROW EXECUTE PROCEDURE grades_ins_before_f();

I should note that PostgreSQL's triggers are very powerful, but their two-stage command to make a trigger is very annoying. Other servers such as MS SQL Server, DB/2 and MySQL have simpler syntax. I put up with PostgreSQL because it is so powerful and it is free software.

One very big advantage of this system is that the calculated value is non-subvertible, no rogue commands can corrupt the calculated values. I personally love this because it gives me all of the advantages of normalized data while also getting useful derived data in the tables for easy access.

Conclusion: Denormalization and Sorting It Out

Third normal form itself is easy enough to understand, but it brings us our first conflict between good database principles and the real world. The principles of normalization keep out calculated values, but calculated values are part of every useful applicaiton.

This is why so many people say that it is necessary to denormalize. But keep in mind always that there is a difference between non-normalized and de-normalized. We have no interest in a non-normalized database, where the designer has not even bothered to identify the correct tables, primary keys, and foreign keys.

What we have seen instead is that it is very useful first to normalize a database by identifying the individual things we want to keep track of, making an individual table for each specific kind of thing, and then identifying the primary properties of each of these things. The process of de-normalization begins when we define calculated values and begin to put those into the tables. Once we do this, we must choose a method to ensure that these values are correct, using views, triggers, program code or a combination of the three.

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.

Monday, December 24, 2007

Database Skills: Second Normal Form

The most basic skill that you need for successful database projects is table design. Strong table design leads directly to efficient and clean code, while weak table design leads in the opposite direction, code that is hard to write and hard to maintain.

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.

The Example

Our example is a school that has different kinds of schedules on different days. Periods may be 35, 40, or 45 minutes, and there may or may not be an assembly. Our assignment is to create a page that displays the periods for a day.

Nobody Argues About Second Normal Form

This week's essay is going to be rather short for two reasons. The first reasons is that I have never really heard anybody argue against second normal form, and so there is no real debate for me to review. In fact, from my own personal experience I would say that Second Normal Form is possibly the least controversial concept in computer science.

The second reason this essay will be short is that I have no real-world examples of violations that appear to make sense, so I have to make up the example. Many people will put in repeating groups (violating first normal form) and give you a determined argument claiming they are doing the Right Thing, but nobody has done that to me with second normal form. So I had to make something up to demonstrate what 2nd Normal Form is.

Prelude: Multi-Column Primary Key

Before we get to second normal form we have to talk very briefly about multi-column primary keys. A multi-column primary key is exactly what it sounds like, a primary key that has more than one column.

Our example from prior essays is a table of periods with a primary key on DAY_TYPE and PERIOD. No combination of these columns can appear twice. So in the table below the combinations in the first two columns must be unique:

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   

Second Normal Form

Second normal form is violated if you have a column that depends on only a part of the primary key. In this case it would mean a column that depended only on DAY_TYPE or PERIOD. This example has two violations of second normal form:

DAY_TYPE  |  DAY_DESC     | PERIOD   | DESCRIPTION | SEQUENCE  |  START    |   END
----------+---------------+----------+-------------+-----------+-----------+----------
NORMAL    |  Normal       | HOME     | Homeroom    |  0        | 8:00 am   |  8:10 am
NORMAL    |  Normal       | 1        | Period 1    |  1        | 8:15 am   |  9:00 am
NORMAL    |  Normal       | 2        | Period 2    |  2        | 9:05 am   |  9:50 am   
...
...more rows for type NORMAL
...
ALONG     | Long Assembly | HOME     | Homeroom    |  0        | 8:00 am   |  8:10 am
ALONG     | Long Assembly | 1        | Period 1    |  1        | 8:15 am   |  9:00 am
ALONG     | Long Assembly | 2        | Period 2    |  2        | 9:05 am   |  9:50 am   

Getting to Second Normal Form

In the example above the column DAY_DESC always has the same value for a given value of DAY_TYPE. DAY_DESC depends on only part of the key (which is DAY_TYPE + PERIOD), and so it is a violation of second normal form.

The exact same error occurs with DESCRIPTION, which is always the same for a given value of PERIOD. This value depends on only part of the key, and so it is a violation of second normal form.

The fix in both cases is to create parent tables to hold these values. The primary key of the parent table is one of the columns from the original table. Here are the two tables we create when we normalize the table from above:

DAY_TYPES:

DAY_TYPE          |   DESCRIPTION
------------------+--------------------------------
NORMAL            |   Normal Schedule 
ALONG             |   Long Assembly
ASHORT            |   Short Assembly
ALUNCH            |   Short Assembly After Lunch

PERIODS:
    
PERIOD       |   DESCRIPTION
-------------+-----------------------------
HR           |   Homeroom
1            |   Period 1
2            |   Period 2
3            |   Period 3
4            |   Period 4
LUNCH        |   Lunch
6            |   Period 6
7            |   Period 7
8            |   Period 8
ASSM         |   Assembly

Why Normalize?

Because this is a short essay this week, now would be a good time to review the basic reasons why we normalize.

In a normalized database, every distinct fact is stored in exactly one location, and each location stores exactly one fact (where a "location" means the value of one column in one particular row). This goal itself serves the goal of avoiding something that the database theorists call "anomalies".

In database terms, an "UPDATE anomaly" occurs when you issue a reasonable update statement to change something, but in fact the same information is stored somewhere else as well, and your two locations are now out of sync, your database has become corrupted. An "INSERT anomaly" and a "DELETE anomaly" are similar.

What this means to us programmers is that when a database is not normalized we end up with a significant burden trying to keep the data correct. We waste a lot of time fixing data on live systems (admit it, you've done it!) or adding code trying to prevent the problem. Many of those problems would be avoided completely if the data were normalized.

Now, as always, programmer who do not know databases well will argue that a system of fewer tables is "simpler", because he only needs one table instead of three, and he does not need to use JOINs when querying, or his ORM system is easier to write. This may be true, but he now has the burden of making sure that when an end-user changes a description in one row that it gets changed in all of the others. I personally would rather spend my time adding a new feature then fixing one I coded last year.

Normalization cannot solve all of your problems, but it can very definitely solve the ones it was meant to solve. If you can get each fact stored in exactly one place then you never have to waste time coding special fix-it routines or crawling through inconsistent data on a live system.

Conclusion: Easier Coding

Today we have seen another example of how a normalized set of tables is actually easier to work with than a so-called "simpler" single table, because it is easier to make sure that the data is correct.

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.

Monday, December 17, 2007

Database Skills: First Normal Form

Since all non-trivial websites require a database, and since database skills are different from coding skills, there is very good reason for any programmer to master the principles of database design and use.

The most basic skill that you need for successful database projects is table design. A strong table design can be the foundation of a wonderful application, while a weak design brings nothing but delays, expense, headaches and heartaches.

One of the cornerstones of good table design is the process of normalization. Normalization proceeds through different stages. The result of each stage is called a "Normal form." So if we have completed the first stage we say the database is in "First Normal Form", and after we have completed the second stage we say the database is in "Second Normal Form" and so on.

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.

The Example

Our example is a school that has different kinds of schedules on different days. A normal day has 4 periods in the morning, lunch, and then 3 afternoon periods. Sometimes the periods are 35 minutes and there is an assembly at the end of the day that goes to the normal end of day. On short assembly days the periods are 40 minutes each. There is a also a variation where the short assembly might be after lunch instead of at the end of the day.

A First Draft Table

Taking a stab at some table design, we may say something like, "Well I can see that there are never more than nine periods, and there are four kinds of schedules. I can make a table that lists the four kinds of schedules and tells me what happens in each period, something like this:"

DAY_TYPE   | MINUTES | P0   P1   P2   P3   P4   P5   P6   P7  P8   
-----------+---------+----+----+----+----+----+----+----+----+----
NORMAL     | 45      | C  | C  |  C | C  | L  | C  | C  | C  | X 
ALONG      | 35      | C  | C  |  C | C  | L  | C  | C  | C  | A 
ASHORT     | 40      | C  | C  |  C | C  | L  | C  | C  | C  | A 
ALUNCH     | 40      | C  | C  |  C | C  | L  | A  | C  | C  | C 

....where "C" means a class period, "L" means lunch, "A" means assembly and "X" means nothing happens.

It so happens that this table is not in first normal form. We will look at what this means first in theory, and then we will see the practical problems that come from it.

First Normal Form as Theory

A table is in first normal form if it has no repeating groups. The example above repeats the period column nine times with columns P0, P1, P2. This is a repeating group, and so it violates first normal form. Theory tells us that we must redesign the table, splitting the repeating group out into a child table. This will leave us with two tables, one that is for day types and the other that tells us about periods. The table of day types is now quite simpler:

DAY_TYPE   | MINUTES | DESCRIPTION
-----------+---------+-----------------------------                  
NORMAL     | 45      | Normal Day                            
ALONG      | 35      | Long Assembly
ASHORT     | 40      | Short Assembly                           
ALUNCH     | 40      | Short Assembly After Lunch                        

The original table had ordering information buried in the names of the columns. We need to have that same information in our child table so we will have the PERIOD column be numeric and it will determine the order of the periods. Here is the table with some of its entries:

DAY_TYPE   |  PERIOD   |   ACTIVITY 
-----------+-----------+---------------
NORMAL     |  1        | Class
NORMAL     |  2        | Class
NORMAL     |  3        | Class
NORMAL     |  4        | Class
NORMAL     |  5        | Lunch 
NORMAL     |  6        | Class
NORMAL     |  7        | Class
NORMAL     |  8        | Class
ALONG      |  1        | Class
ALONG      |  2        | Class
ALONG      |  3        | Class
ALONG      |  4        | Class
ALONG      |  5        | Lunch 
ALONG      |  6        | Class
ALONG      |  7        | Class
ALONG      |  8        | Class
ALONG      |  9        | Assembly

We have now satisfied the theory, and put our fledgling database into First Normal Form. But the question arises, wasn't the single-table version a lot simpler? This new "fixed" version seems more complicated. A programmer might say something like, "I have to look all over the place to find what I need." To answer these objections we will now examine the practical side of the example.

Problem 1: Structure Changes

The non-normalized example is probably fine if you have just one school that never changes their schedule. But if you have multiple schools you will need to change your structure for them, adding or removing periods.

To this we might reply, "but sure, structures change all of the time." Yes, it is true that a structure change may be required to improve a program or add functionality, but in the example above you must change the structure in cases where you are not improving the program or adding functionality. This means the more people use your program, the more work for you! By contrast, in the normalized situation a different schedule just means an end-user task to make entries in the tables.

You can argue that this particular example is not so bad, because, after all, how many periods can any school really have? Let's make a table with 15 periods and be done with it, no? The problem is that if you win that argument and make a habit out of repeating groups then you saddle your development efforts with the ongoing need to do structure changes where simple data editing would have done the job.

Problem 2: Secondary Columns

I worked on a system one time that had a repeating group of 24 columns in several tables. The columns were for the size of an item of clothing. In one table there were 24 additional columns for price. Then 24 more for cost. Then 24 more for gross profit. There were more, should I continue?

The problem would appear in our school schedule if we wanted to put the start and end times of the periods into the tables so we did not have to figure them out on every page load. We would now have START0, START1, END0, END1, and so on, eighteen more columns. Such fat tables like this are very clumsy to work with both in code and when doing ad-hoc queries.

Problem 3: Constants Required

Imagine you have a simple routine called SQL_ALLROWS() that gives you query results in an array (if you don' have one, code it today!). If your database is well normalized your code will display this pattern a lot:

$rows = SQL_AllRows(
    "SELECT activity FROM period_info 
      WHERE day_type = 'NORMAL'
      ORDER BY sequence"
);
foreach($rows as $row) {
   // ...do something interesting...
}

This simple code is not possible when you violate first normal form. You must put a constant into your code somewhere so that your code knows how many columns there are. This is an extra bit of maitenance to worry about here:

$row = SQL_OneRow(
    "SELECT * FROM period_info 
      WHERE day_type = 'NORMAL'
);
for($x = 0; $x < NUM_PERIODS; $x++ ) {
   $period = $row['P'.$x];
   // ... do some display...
}

Problem 4: Query Construction

For problem 3 above, I put a "Select *" into the query. I really should not have done that. I should have listed all 9 "P" columnns (or all 27 columns if we put start and end times in also). Something like this:

SELECT P0,P1,P2,....

But this violates good programming practice. If the structure changes then I have to go to all of those queries and modify them. A better programming practice would be to use that NUM_PERIODs constant to generate column lists on the fly:

$cols = array();
for ($x = 0; $x < NUM_PERIODS; $x++ ) {
  $cols[] = 'P'.$x;
}
$query = "SELECT ".implode(',',$cols)." from...";

Most programmers will avoid a bit of code like this because it seems at first glance to be really stretching a point. Of course once the program is successful and the additions and changes come in that leaves you going through and changing every query every time you modify the structure. None of this is necessary with the normalized version.

It is starting to seem like a stretch to claim that we have take the simpler path here. Perhaps the "complicated" normalized tables really are simpler.

Problem 5: Hard-Coded Tools

As I write this in December of 2007 the web is rediscovering a UI widget that has been in use for decades in terminals and desktops: the grid. The grid is the natural method of editing child tables in admin interfaces.

Before 1995, when there were no web programmers, a programmer who violated first normal form paid the price by needing to code up things like grids by hand because he could not use grid-like tools available in his framework. The modern web programmer will pay the price in not being able to use the grids that are rapidly coming online.

Problem 6: Cannot Aggregate

Finally we will consider a simple question, tell me how many periods there are in any particular DAY_TYPE. In a normalized database the SQL is easy:

SELECT COUNT(*) FROM periods WHERE day_type='NORMAL'
-- or, to get them all:
SELECT COUNT(*),Day_type
  FROM periods
 GROUP BY day_type

Getting the same answer in the non-normalized database requires code. It does not matter how simple that code is, the fact is that if the tables were normalized the code would not be required at all. Multiply that by 10 tables, 100 tables, and you just have complication after complication.

Conclusion

Violating first normal form puts us in a situation where we need programmers to make changes, while normalized databases can be modified by end users. Violating first normal form also forces us to embed and maintaint constants we would not otherwise need and to change queries that could otherwise be left alone. These problems can be mitigated with libraries of code that build queries for us, but still those libraries have to be written.

In this light, we see can see first normal form as something that creates efficient code and easier-to-maintain databases.

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.

Monday, December 10, 2007

Database Skills: Foreign Keys

This is the third in a series of articles that will help you develop your database skills, Since all non-trivial websites require a database, and since database skills are different from coding skills, there is very good reason for any programmer to master the principles of database design and use.

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.

The Example

This week the example continues from the last two weeks. A school has 8 periods in a day that are varying lengths, sometimes an assembly, and of course homeroom. Our assignment is to code a screen that shows the begin and end times of each period depending on what schedule was being followed for the day. We have already decided to "spell it out" by populating a table with the start and end times for each period in each kind of day (the schedule was described in the first essay in the series. Here is a list of about half of the rows in the table:

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
ALONG     |  HOME   |   0       | 8:00 am   |  8:10 am
ALONG     |  1      |   1       | 8:15 am   |  8:50 am
ALONG     |  2      |   2       | 8:55 am   |  9:30 am
ALONG     |  3      |   3       | 9:35 am   |  10:10 am
ALONG     |  4      |   4       | 10:15am   |  10:50 am
ALONG     |  LUNCH  |   5       | 10:55 am  |  11:30 am
ALONG     |  6      |   6       | 11:35 am  |  12:10 pm
ALONG     |  7      |   7       | 12:15 pm  |  12:50 pm
ALONG     |  8      |   8       | 12:55 pm  |  1:30 pm
ALONG     |  ASMB   |   9       | 1:35 pm   |  2:50 pm
....
.... same for other two schedules
....

Now we will see how a foreign key will help to make sure the above table is correct.

The Foreign Key

A foreign key is used to prevent certain kinds of errors. In our example above we see that the column DAY_TYPE has only two values, NORMAL and ALONG (and ASHORT and ALUNCH that were not dipslayed). If a user were typing in this schedule and accidentally typed 'NRMAL' or 'NORMALL' we want to catch that and prevent it. A foreign key is how we do this. Making the column SCHEDULE_TYPE a foreign key means here is some other table that holds the allowed values. We call that other table the parent table. Such tables are also called lookup tables, reference tables and sometimes leaf tables. Here is the parent table of SCHEDULE_TYPES, which lists the allowed values of SCHEDULE_TYPE.

SCHEDULE_TYPE     |   DESCRIPTION
------------------+--------------------------------
NORMAL            |   Normal Schedule 
ALONG             |   Long Assembly
ASHORT            |   Short Assembly
ALUNCH            |   Short Assembly After Lunch

The column PERIOD will also be a foreign key, and the parent table will look like this:

PERIOD       |   DESCRIPTION
-------------+-----------------------------
HR           |   Homeroom
1            |   Period 1
2            |   Period 2
3            |   Period 3
4            |   Period 4
LUNCH        |   Lunch
6            |   Period 6
7            |   Period 7
8            |   Period 8
ASSM         |   Assembly

Database people say that a foreign key enforces "referential integrity". This term comes from the fact that one column reference another, and this reference ensures the integrity of the data.

The SQL Code

This SQL is illustrative only. Your particular database may require additional commands or syntactical variations.

Creating the two lookup tables is accomplished like so:

CREATE TABLE periods (
     PERIOD char(8)
    ,DESCRIPTION char(25)
    ,PRIMARY KEY (PERIOD)
)
CREATE TABLE day_types (
     DAY_TYPE char(8)
    ,DESCRIPTION char(25)
    ,PRIMARY KEY (DAY_TYPE) 
)

The table of actual periods for each kind of day is created with the code below. I have used type 'character' for the start and end times to keep things simple for now. Note the two foreign keys, one to each table:

CREATE TABLE periods_x_days (
     DAY_TYPE char(8)
    ,PERIOD char(8)
    ,SEQUENCE int
    ,START char(8)
    ,END char(8)
    ,PRIMARY KEY (DAY_TYPE,PERIOD)
    ,FOREIGN KEY (PERIOD) REFERENCES PERIODS(period)
    ,FOREIGN KEY (DAY_TYPE) REFERENCES DAY_TYPES(DAY_TYPE)
)

There is a multiple-column primary key that I snuck in there. By enforcing a primary key on DAY_TYPE and PERIOD, we make sure that there is no accidental double listing for a particular type-period combination.

The Basic Building Blocks

Now that we know how a foreign key works and what it does, we can start to talk about why it is important. In short, the foreign key connects your tables together so that you have a true database. Without the foreign key all you have is a jumbled bag of unrelated tables.

An analogy to code may be in order here. Most of us can list many ways in which bits of code can connect to each other. You can call a function, you can make complex objects via composition, you can make classes via inheritance, along with many others. All of these mechanisms tie bits of code together into a coherent application. The corresponding fundamental way to tie tables together in a database is the foreign key.

So to sum it up, we have seen so far that:

  1. A database programmer by inclination wants to spell everything out in tables.
  2. Each table stores information about exactly one kind of thing, and a primary key makes sure there are no duplications.
  3. Foreign keys define how different kinds of things relate to each other.

The Server Does The Work

Using a foreign key is a good way towards moving your business logic into the database server itself. Once you have defined the foreign keys above, the server will never allow you (or your users) to accidentally or maliciously insert bad data. This command:

INSERT INTO periods_x_days (
    day_type,period,sequence,start,end
)
values (
   'FICTION','99',5,'11:00 pm','11:05 pm'
)

...will throw an error when you try to execute it. The exact wording varies from product to product, but the bottom line is you are fully protected from these errors without doing any coding of your own.

Foreign Keys and The Classic Blunder

Last week we noted that many programmers have the idea that they can combine the PERIODS table and the DAY_TYPES table into a single table, thereby "saving" a table. I did my best to debunk this idea by pointing out that

  1. First, it is actually less efficient to do so because it complicates code and requires at least one extra colum, and
  2. Second, it destroys the value of the primary key column as a unique identifier of a single kind of thing.

We now have a third argument, based on our understanding of the foreign key. If we combine the tables then we can no longer use the combined table as a lookup table for foreign keys. The classic blunder of "saving a table" means the resulting table cannot be tied properly to anything else. This kills the idea off completely, since the entire purpose of creating the tables is to use them as parent tables for foreign keys!

Conclusion: The Building Blocks

When we grind out code, we do not usually expect the customer to give us rigorous specs. They tell us what they need in their own language and we have to convert those needs into functioning code. We use building blocks like classes, functions, conditionals and iterators to make those programs.

Designing a database is the same as designing code insofar as the customer will not be able to design it for you. They will be able to tell you want they want in their own language, but you will have to design the tables. That process of table design is all about identifying primary and foreign keys. Next week we will begin to see how to do this, through the process of normalization.

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.

Monday, December 3, 2007

Database Skills: Primary Keys

This is the second in a series of articles that will help you develop your database skills, Since all non-trivial websites require a database, and since database skills are different from coding skills, there is very good reason for any programmer to master the principles of database design and use.

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.

The Example

This week the example continues from last week. A school has 8 periods in a day that are varying lengths, sometimes an assembly, and of course homeroom. Our assignment is to code a screen that shows the begin and end times of each period depending on what schedule was being followed for the day.

Last week we introduced the idea that the database programmer will tend towards trying to spell it out. That is to say, the database programmer will cast the solution in terms of saving information in a database that can be retrieved later on. He will generally avoid coding an algorithm that "figures it out" if he has the chance to simply store data that can be retrieved as needed. We will now see how a primary key works by looking at two tables that make up part of the solution.

The Primary Key Defined

The primary key is pretty easy to understand. It is simply the column or columns in a table that must be unique. In our example of the school schedule the database programmer is going to create two reference tables that have single-column primary keys and just a few rows each. Here they are, the table of schedule types and the table of periods:

SCHEDULE_TYPE     |   DESCRIPTION
------------------+--------------------------------
NORMAL            |   Normal Schedule 
ALONG             |   Long Assembly
ASHORT            |   Short Assembly
ALUNCH            |   Short Assembly After Lunch
PERIOD       |   DESCRIPTION
-------------+-----------------------------
HR           |   Homeroom
1            |   Period 1
2            |   Period 2
3            |   Period 3
4            |   Period 4
LUNCH        |   Lunch
6            |   Period 6
7            |   Period 7
8            |   Period 8
ASSM         |   Assembly

The first table has a primary key of SCHEDULE_TYPE. No two rows in that table can have the same value of SCHEDULE_TYPE. The second table has a primry key of PERIOD, so that no two rows in that table can have the same value of PERIOD.

A primary key is in fact very simple and does not really require a lot of discussion except for one further point: a primary key identifies something. For example, the value 'NORMAL' identifies a certain kind of schedule, and if I am given this value I can look up things about the schedule in the SCHEDULE_TYPES table.

Detour: Code and Data Decisions

These examples are so far very simple, so it may not be obvious that the tables SCHEDULE_TYPES and PERIODS are really necessary. After all, we can just put them into an associative array in some type of include file and not bother making tables, right?

Technically, you can of course put anything you want into an include file and save yourself some time at the front of a project. The drawbacks emerge over time and are the consequence of "trapping" data inside of code.

The first and most obvious drawback is that if the customer wants to change a description then a programmer must change a program file, instead of just having a user go to an admin screen. But worse, what if our program becomes a smash hit and schools all over the country are using it? What if they all want different descriptions, or what if they have different numbers of periods and kinds of schedules? If we start off by putting such things into tables then we will be well prepared for future demands, but if we "trap" the data in code then future success will produce choking demands on the programmers to change the files.

If the tables were more complicated we would have another issue. Databases were designed for efficient querying, but arrays were not. Querying the data that is trapped in code is much harder than querying tables in the database.

So we will take it as a given that data belongs in a database, even for simple two-column tables.

The Primary Key And a Classic Blunder

Programmers are by nature visual thinkers and we tend to be good at pattern recognition. This is why it is so common for programmers to look at the example above and say, "Hey! I just noticed that both of those tables have the same structure, a code and a description! I've got a great idea, I'm going to combine them into one table, which will be more efficient!"

This is a classic blunder. It is a result of applying wisdom from one situation into a different situation where that wisdom does not apply. Here is what happens. The programmer takes his idea of efficiency, which he learned from writing code, and tries to apply it to table design, which operates on completely different principles. He thinks that by replacing two tables with one he is being more efficient, but the real result is less efficiency.

First off, the new combined table now requires a new column, ROW_TYPE, which was not necessary before. More complicated, less efficient.

Secondly, a simple query to pull all types of schedules now requires a filter on ROW_TYPE that was not required before. This raises major alarms for seasoned programmers because any time you make a simple action more complicated with no apparent gain then you are on a bad road!

But most importantly for today's essay, the "efficient" single-table approach has muddled the primary key. The value 'ASHORT' by itself no longer uniquely identifies a schedule type. To solve this we end up in strange discussions. Should the column ROW_TYPE be part of the primary key? Or should we keep the single column key and have the odd situation that a schedule type and a period cannot have the same code? These conversations should raise alarms with any seasoned programmer because we have barely gotten started with two simple tables and already we are struggling with the very basics of picking primary keys! Even a programmer with no database experience should be suspicious of an approach that requires wrangling with the very basics. What good can come of that? The big irony is that these complications arose from the "simplification" of combining two tables into one.

Finally, there is a big technical problem with foreign keys and the one-table approach, but that can wait until next week.

All of these problems can be avoided by remembering that each table in a database contains information about one kind of thing and only one kind of thing. The primary key is that column or columns that uniquely identifies each of these individual things.

Conclusion: Look for the Keys

The primary key is simple to understand. It is a column or columns that must be unique in a table. But beyond that, the primary key is not just unique values, but unique values for like items. Database design skills begin with identifying the kinds of things that must be tracked, putting each into a table, and assigning the primary keys to those tables.

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.

Monday, November 26, 2007

Database Skills: Introduction

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.