Chapter 8 - Database Design

The first things to do are to describe the application that we will be creating, and to lay down some initial requirements covering what the database will store, and what the application code will do with that data. For this example, we will be creating a "wishlist" web application; a fairly simple website where people can post details about things they'd like for an upcoming birthday or other event, and which their friends and family can use to help them to choose gifts which will be appreciated.

We will keep the HTML / CSS presentation very simple, to focus on the LAMP side of things: A database storing data, and a PHP application managing and presenting that data. In a commercial website, professional HTML, CSS, and quite possibly AJAX techniques would be used to make the presentation more appealing.

Database Tables

Whether a web application or any other type of programing project, it is always best to spend time thinking about data structures before worrying about algorithms and coding details. A well-defined data structure makes the programming much easier, so it is well worth planning the data structure carefully first. A poorly-defined data structure, or one which has been built without planning to fit around the first parts of coding, normally results in hard to manage code later on. In this case, most of the tables present themselves quite intuitively: We will have a users table containing user information, a wishlist table for the wishlists, which will reference items in an item table, which contains all of the individual items that users are interested in.

The relationship between wishlists and items, and how users interact with both of these things, is the first thing to consider. Provisionally, the three tables will look something like this:

Item table

Wishlist table

User table

This defines three different types of things - Users, their Wishlists, and the Items that are contained in those Wishlists. A user (John) can create a wishlist, give it a name, like "John's Birthday List", and put items, such as a "Baby GNU," into that wishlist. That's all fine (storing unencrypted passwords like this is never okay; we will deal with that later!), but if there will be lots of items, lots of wishlists, and lots of users, how do we tell which things belong together?

Relational Databases

That's where the "Relational" part of Relational Database Management Systems (RBDMS) comes into play. The reason for having a unique ID for each element in each table, is so that they can be identified uniquely by the application, and also so that they can refer to one other. There are three main types of relationship: One-to-One, One-to-Many, and Many-to-Many. So far, we are using two One-to-Many relationships (which are probably the most commonly-used kind).

First, let's consider the item / wishlist relationship. The links are shown by MySQL Workbench with a red diamond next to them (wishlist_id in this case):

Each item has exactly one wishlist_id entry, so that it can point to its single parent wishlist. The wishlists have no corresponding item_id entries, so a wishlist can't associate itself with any items at all. At first, this can seem to be the wrong way of doing things. By definition, a wishlist is a list of items, so surely the wishlist table must contain pointers to items, and not the other way around?

The problem with making the wishlist contain the items, is that you'd have to create a column for "item1", another for "item2", and another for "item3". If you needed four items in a list, you'd need to edit the structure of the table itself, and add a column called "item4". If we want to deal with lists of thousands of items, the structure will become very messy, and the code will also be awkward to deal with, using named columns such as "item43" and "item57".

If every Wishlist can contain 100 items, then every list created will need to store 100 pointers, even if it only contains five items, so this is inefficient use of storage, as well as being limiting for the user. If you only define fields "item1" to "item10", then you can't ever hava a list with 11 items in it. That's pretty frustrating, and part of the point in using a database is to give yourself flexibility in the size of the data you manage.

The proper way to deal with this situation is to simply make each item identify its parent. This takes exactly one field in each item, and you can find all of the items in wishlist #321 with the simple SQL statement: "select * from item where wishlist_id = '321';", and there is no need to limit yourself with fixed-sized lists.

Looking at the three tables that we have defined so far, their relationship is One Wishlist == Many Items, and One User == Many Wishlists. This is shown in the diagram below.

The line from user (one prong) to wishlist (three prongs) shows that one user can have many wishlists, but each wishlist only has one user associated with it. Similarly, the line from wishlist (one prong) to item (three prongs) shows that one wishlist can contain many items. Each item may only belong to one wishlist, however.

Many-to-Many

It would be possible to have a Many-to-Many relationship between wishlists and items - an item could exist in multiple wishlists, and whenever that item was updated (in any of those lists), that change would be reflected in every list the item was included in. For example, you might have a "Birthday" list as well as a "Christmas" list, which could contain different things but also share some common things, which you would be happy to receive for either event. A third table, itemlist is required to properly map this property.

The itemlist table stores every item entry in every wishlist; most often, there would only be one itemlist for each item, but if an item existed in more than one list, then each entry would have an itemlist entry. If you are familiar with "hard links" in Unix or Linux filesystems, this is a similar structure.