Planning the Database Structure

Chapter 4 - Planning the Web Site
byGareth Downes-Powellet al.
Wrox Press 2003

Like the web site itself, a database has its own internal structure, hierarchy, and set of rules. As such, you should approach the design of a database in much the same way as you would approach the design of a web site.

For the Dreamweaver Hotel, there are specific types of information that we need to store and retrieve. What we really need to determine, is the precise nature of that information, how we will store it, and whether that information is related to any other information that might be stored elsewhere.

The starting point for all public enquiries on the site is the visitor seeking a room. We can safely establish what information we need to store for the room, and build upwards from there.

The diagram above outlines the information that we need to store in this database. As The Dreamweaver Hotel is not a large hotel, it isn't necessary to store prices in a different table and create a relationship. Though of course, this might be a more practical approach if you are working on a database for a hotel with a few hundred rooms.

As there are various elements of this table that will be used within the search functionality of the site, we are going to create a couple of indexes to help speed up this process. As the Bed Type will be a criterion that the customer can specify, we will make this column into an index, and we will do the same for the only other obvious column, the Room Number.

As we begin to build on this first table, we immediately know that there will be some relationships, but it is important to establish which relationships these are. I find that the best method for me is to get a large sheet of paper and list out, longhand, all of the information that I need in each table. Once this is done, I start to look for common denominators, or information of the same type, and put them together. Before too long, you'll find that there is enough common information to justify another table. Continue this process until there is no other shared information.

What you have been doing here is to establish relations. In the image below you will see a screenshot of the table layout and relationships for all of the room-related information.

These relationships between tables will help immensely when coding the pages that make up the site. Now that we know how the database will work we need to plan the functionality of the site, so that we can fully understand the processes involved. Of course, we need to also remember that the design of a database is an iterative process. In the future, during development of the site, or with any later site amendments or additions, certain aspects of the database structure may require change. It is not uncommon for additional functionality to be added to a site at a later date. This additional functionality will have its own set of data requirements which may force the amendment of some, or all of your existing database tables. It is worth considering the possible additions that could occur within the site, and build a degree of latency into your database for these future additions.