Core Web Application Development with PHP and MySQL

In keeping with our theme of planning our web applications in advance, we should sit down and ask ourselves what should be going into our databases before we start writing PHP script or SQL code. Once we have decided what we will put in them, then we can worry how to accomplish that, which will be covered later in the chapter.

While there are no concrete rules on how to identify something that should go into a databasesome people might start accusing us of being "data happy" if we start viewing everything as a candidatethere are common items that are very likely to end up in tables (see Figure 9-1).

Obviously, any large lists or collections around which we base our web application will wind up in a databaseproduct lists, customer data, addresses, account information, account transactions, stock purchases and sales, user discussion comments, suppliers, and so on. We will also want to have special users of the web site, such as administrators or moderators, stored in tables.

If we consider our application further, we can see other items that might be appropriate for inclusion in a database. We might decide to include the list of articles to display on a site's home page, or perhaps other information on events that we display in certain pages. By putting these in the database, we make it easier to write administration pages that allow non-technical people to manage our site without having to know PHP (or even how to work with databases).

Finally, for sites that are more content-intensive (for instance, an association that wants to provide information to its members, such as by-laws, meeting summaries, or calendars), we can consider whether to place the actual content into the database, too. Here is where we wander into areas where the decision is difficult: If the site is reasonably manageable, it might make perfect sense to leave the files in the file system for easy editing and replacement. However, if we were writing a content management system for a larger organization with pages in multiple languages, we could find ourselves planning on putting these in the database for ease of maintenance.

A good example of data you might want to consider twice before placing it into a database would be a collection of images. If you were building a photo display system to which users could upload a large number of photos, it might prove beyond the capabilities of your database system to manage gigabytes (if not terabytes) of images efficiently.