Welcome back to my unauthorized look into the seedy underbelly of a web application. (did you miss part one?) Today: the database. The model. The schema. The sick, twisted, gang of data that lurks beneath the surface of nearly every web application. This isn’t for the faint of heart, so if you can’t bear to imagine your precious information being crammed feet-first into a database, I understand. Otherwise, read on for the thrilling story of a dictator with some complex data modeling issues.

Let’s say you’re in a dictatorial society, and that the dictator wants a database to manage his island. The dictator, being a dictator who doesn’t have to accept bids on a project, picks me (we’ve worked together in the past).

Who needs a database?

Databases are tools for describing the complex between the “things” that are important to the processes of your application, things that you expect to remain consistent from one time a page loads to the next. The users who frequent your site and must log in to access the data they want are stored in your database. If you use an Internet email application, your email is probably also stored in a database so that it won’t vanish between your visits. Because of the permanence of data stored in a database, it is the ideal place to store data for later retrieval. A database that runs a shopping cart might remember items, shoppers, sales, shipping data, even the contents of an abandoned shopping cart. Can your local grocery store do that? Probably not, and they’d be upset when maroon a shopping cart in the aisle and leave the store, even if you did plan to return tomorrow after discussing your purchases with your friends.

Think about how objects in the real world are related. Databases are built to model the sorts of relationships that occur in reality, not in a perfect world. Digest that sentence and try to guess the implications. Done? You probably came to the conclusion that databases are often very complex, and you’re right. A business does complicated things, and the complexity of the business is reflected nearly one-to-one in the database. Back to the story.

Database models can come from User Stories

I say, “OK, Generalissimo, let’s write some user stories.” The dictator, gesturing with his pistol, says “User stories?!” and cowering, I reply, “User stories are how I can figure out your needs so I can create a requirements document for you and build your tool!” The dictator lowers his pistol and waves off his bodyguards. He indicates that in his society, there are only People, Pets, and Prisoners, and that the following rules apply (these are the User Stories):

1. All People, Pets, and Prisoners must have a unique ID. It sounds totalitarian, but I’m the dictator and I demand this.
2. Pets may belong to zero or one Person, and no more. A person can have zero, one, or many pets.
3. People may have many friends, who may each have many other friends.
4. Prisoners must be directly associated with one and only one Person ID, but must use their Prisoner ID while in the big house.
5. I can place any Person in Prison for any Reason, and there shall be no table for Reasons, for my Reasons will be arbitrary.

I say to the dictator, “This looks good. I think I have enough information now.” The dictator fires his pistol in the air three times and vanishes in a puff of cigar smoke. I retreat to the basement and put on my pocket protector, giant glasses, and magical iTunes mix.

Building the database

So we have three objects, People, Pets, and Prisoners. Each of these objects will be a table. If you’ve used a spreadsheet, you should be familiar with the notion of a table–each row is one instance of the data type that the table represents, each column is a field with a value pertinent to the data type. Let’s apply our five rules to our three tables.

Rule number one is simple; each of our three tables will have a field (think column) named “ID” which will always be unique; no two rows may have the same ID.

Rule two states that Pets may belong to zero or one People, so we will add a field called “PersonID” which will either store NULL (meaning no person) or a positive number that is the ID of the person who owns the Pet.

Rule three is that people can have many friends, and friends can have many other friends. This requires the addition of another table. Let’s call it Friends. Friends will have two fields only, PersonID and FriendID. Both of these fields are positive integers that are IDs for rows in the Person table. Think about it. You have four friends, Joe, Jim, John, and Jane, so there are four rows. One row has your ID and Joe’s ID, the second has your ID and Jim’s ID, and so on.

Rule four is easy to implement. Like with Pets, the Prisoners table will also have a PersonID, but we’ll tell the database to keep it Unique so that there will be only one Prisoner per Person (makes sense, you can’t be in two Prisons at once).

Rule five requires no implementation. Since the dictator told me that Prisoners may be in Prison for arbitrary reasons, I shrug, and say, “Ok, so I won’t add anything to the Prisoners table.” This is an interesting point about developing software for clients–sometimes the thing most important to them is something that requires no work, and they confuse the importance of an issue with the amount of work it will take. It’s only fair, though, since the necessary details that clients rarely care much about often take an enormous amount of work.

As to the rest of the tables, they contain other fields (columns) with other relevant information. The People table has name, address, eye color, hair, color, the Pets table has name, diet, and housebroken status, and so on.

The dictator examines his database and says, “Wonderful! This will allow me to dictate at 200% efficiency!” Upon closer examination, he decides that he wants to keep records of who has been to Prison, even after they’re out, so I add a field (column) to the Prisoners table called isImprisoned, which can be either 1 or 0. 1 means yes, this prisoner is incarcerated, and 0 means no, this prisoner is back on the streets. Finally, I turn this over to the dictator, who rewards me with a hearty pat on the back.

The resulting tool

At this point, all we have is a database, so in order to control his society, he must write SQL statements. SQL stands for Structured Query Language, and it is a very useful tool for programmers and programs alike to communicate with a database. Even so, as long as he knows SQL (which he does), this is what the dictator can do:

1. Find Pets, People, and Prisoners.
2. Create new Pets, People, and Prisoners.
3. Destroy Pets, People, and Prisoners.
4. Show a list of a person’s friends.
5. Find all the ownerless pets and reassign them.
6. Find all the people who have more pets than friends and imprison them.
7. Find all the prisoners who own pets and reassign the pets to other people.
8. Find all the pets whose owners neglect their diet, imprison the owner, and reassign the pet.
9. Imprison random people.

And so on. After a long night playing with the database, the dictator realizes that he has more power than ever, and sends me a nice note, a plate of cookies, and a request to build a nice framework that he can use to manage this database since he doesn’t like using SQL (most users wouldn’t!). Developing the database took all my energy, though, so I’ll have to wait a week or more in order to build the application. See you later!

Previous: Introduction

Tags: Database, Development, Enlightening, Tutorials

This entry was posted on Sunday, April 22nd, 2007 at 8:00 pm and is filed under General. You can follow any responses to this entry through the RSS 2.0 feed. You can leave a response, or trackback from your own site.