It’s very important that you design a database correctly before you start to create it; otherwise, you are almost certainly going to have to go back and change it by splitting up some tables, merging others, and moving various columns about in order to achieve sensible relationships that MySQL can use easily.
Sitting down with a sheet of paper and a pencil and writing down a selection of the queries that you think you and your users are likely to ask is an excellent starting point. In the case of an online bookstore’s database, some of the questions you write down could be:
How many authors, books, and customers are in the database?
Which author wrote a certain book?
Which books did a certain author write?
What is the most expensive book?
What is the best-selling book?
Which books have not sold this year?
Which books did a certain customer buy?
Which books have been purchased along with the same other books?
Of course, there are many more queries that could be made on such a database, but even this small sample will begin to give you insights into how to lay out your tables. For example, books and ISBNs can probably be combined into one table, because they are closely linked (we’ll examine some of the subtleties later). In contrast, books and customers should be in separate tables, because their connection is very loose. A customer can buy any book, and even multiple copies of a book, yet a book can be bought by many customers and be ignored by still more potential customers.
When you plan to do a lot of searches on something, it can often benefit by having its own table. And when couplings between things are loose, it’s best to put them in separate tables.
Taking into account those simple rules of thumb, we can guess we’ll need at least three tables to accommodate all these queries:
authorsThere will be lots of searches for authors, many of whom will
have collaborated on titles, and many of whom will be featured in
collections. Listing all the information about each author together,
linked to that author, will produce optimal results for
searches—hence an authors
table.
booksMany books appear in different editions. Sometimes they change publishers, and sometimes they have the same titles as other, unrelated books. So, the links between books and authors are complicated enough to call for a separate table for books.
customersIt’s even more clear why customers should get their own table, as they are free to purchase any book by any author.
Using the power of relational databases, we can define information for each author, book, and customer in just one place. Obviously, what interests us is the links between them, such as who wrote each book and who purchased it—but we can store that information just by making links between the three tables. I’ll show you the basic principles, and then it just takes practice for it to feel natural.
The magic involves giving every author a unique identifier. Do the
same for every book and for every customer. We saw the means of doing
that in the previous chapter: the primary key. For
a book, it makes sense to use the ISBN, although you then have to deal
with multiple editions that have different ISBNs. For authors and
customers, you can just assign arbitrary keys, which the AUTO_INCREMENT feature described in the last
chapter makes easy.
In short, every table will be designed around some object that
you’re likely to search for a lot—an author, book, or customer, in this
case—and that object will have a primary key. Don’t choose a key that
could possibly have the same value for different objects. The ISBN is a
rare case for which an industry has provided a primary key that you can
rely on to be unique for each product. Most of the time, you’ll create
an arbitrary key for this purpose, using AUTO_INCREMENT.