The first, and probably most important thing we need to do, is design our database tables. Well, we need a table for storing member information, so there is our first table. We also need to think about the actual DVDs. It's important to realize that there are two different types of information to be stored - information about a film that is on DVD, for example, the film 'Death and the Maiden' starring 'Sigourney Weaver', which has a director, a release date and so on, and the actual DVD disks available in the shop. The film exists independently of an available DVD disk; there could be zero, one or many disks with that film title actually available in the shop. This tells us that we should separate the film information from the disk information using two separate tables. Clearly they are related pieces of information, but they are not the same.

The next piece of information to store is to relate member bookings to titles. We do this by adding an additional table, 'member_booking, which stores a member ID and a title ID, along with the booking date. This acts as the link between a member and the title they have booked. This also allows more than one member to have reserved the same title on the same day, a classic many-to-many relationship. (The application must check how many disks are actually available of course!)

When we come to disks actually rented, we could do a similar thing, by adding a table between the 'disk' and 'member' tables, we could link disks to members when they are rented. However we notice an obvious optimization - there can only ever be no link, or a one to one link between a particular disk the store owns, and a member renting that disk. So we could store the 'renting' related information directly in the 'disk' table, using a NULL member ID when the disk is not rented. This is called de-normalizing, and should only be done when you are sure you have properly analyzed your data structures. We do it here as much for the purposes of demonstration as any valid optimization technique, though it does slightly simplify the code.

Finally, we need three additional tables for utility information, one for error messages, one for the film genre categories, and one for film classifications. Both the genre and film classifications relate directly to a film title, and are the only values that should appear there. Here we have another set of choices to make. We could either directly store the genre and classification text in the 'title' table against a film, relying on the application to lookup the allowed text from the utility table. Alternatively we could store only an ID, with links in the database back to the actual table where the text is stored.

If the text is very short, and we are confident we can rely on the application only to use a valid text string, then it's probably better to simply store the actual text in the title table, since it makes the database design simpler, and the SQL easier to write. However, if the text is longer, and we want to be absolutely certain that no illegal values could be stored, then we should store the ID for the text, and store the text in a different table. This reduces the storage, since each unique string is only stored once. For the purposes of demonstration in this application, we store the classification directly, but keep the genre stored separately, so you can compare the two techniques.

In a real application however, we would always recommend storing only a link to the table with the real data. This is because it's much more conducive to maintaining the quality of your data, which in a database should always be your number one concern. A brilliantly designed database, that stores incorrect data, is little better than no database at all.

To make managing our table easier, we store the SQL we need to create the tables in a separate file, so we can edit the file and re-create the database easily. You can run SQL commands from a file in psql with the \i file.sql command. Here is the SQL code that creates our database:

create table member (
  member_id SERIAL,
  member_no CHAR(6) NOT NULL,
  title      CHAR(4),
  fname      CHAR(26),
  lname      CHAR(26) NOT NULL,
  house_flat_ref CHAR(26) NOT NULL,
  address1    CHAR(51) NOT NULL,
  address2    CHAR(51),
  town       CHAR(51) NOT NULL,
  state      CHAR(3),
  phone      CHAR(31),
  zipcode     CHAR(11) NOT NULL,
  CONSTRAINT   member_no_uniq UNIQUE(member_no)
);

create table title (
  title_id    SERIAL,
  title_text    CHAR(61) NOT NULL,
  asin       CHAR(11),
  director    CHAR(51),
  genre_id    INT,
  classification CHAR(11),
  actor1     CHAR(51),
  actor2     CHAR(51),
  release_date  CHAR(9),
  rental_cost   CHAR(7)
);

create table disk (
  disk_id     SERIAL,
  title_id    INT NOT NULL,
  member_id    INT,   /* set if rented out otherwise NULL */
  rented_date   CHAR(9)
);

create table member_booking (
  member_id    INT NOT NULL,
  title_id    INT NOT NULL,
  date_required  CHAR(9) NOT NULL
);

create table filmclass (
class_name    CHAR(11)
);

create table genre (
  genre_id    INT NOT NULL,
  genre_name    CHAR(21),
  CONSTRAINT    genre_id_uniq UNIQUE(genre_id)
);

create table errtext (
  err_code    INT,
  err_text    CHAR(50)
);

You should notice some extra 'constraints' have been added, for example:

  CONSTRAINT    genre_id_uniq UNIQUE(genre_id)

We did not want to make the genre_id a SERIAL column, because if we ever need to reload the data it's very important that we re-create each genre_id with the same value we had before, or all the information it relates to in the title table will be wrong. On the other hand, it's very important that the value is unique. We trade off these two conflicting demands by adding a constraint that allows us to pick the value of genre_id - so long as the value we pick does not currently exist in the database.

Below is a graphical representation of the database structure:

We don't have anything like the space required here to reproduce all the code, so we just show a few small snippets to give you a flavor of how the application was developed. These code pieces are the lowest level of the application, and are called after general sanity checking (such as ensuring we have a database connection, and that pointer parameters were not NULL) has been performed.

Continued...