Database Design

When creating a new database, start by listing all the objects that will be involved. These are the things that you want to store information about. Next make a list of the data needed for each object. For example, in a contact list, the data will consist of names, addresses, telephone numbers, email addresses, etc.

After listing the objects and their data, consider the relationships between the objects. A club might have the names and addresses of members in one list and the events that the club sponsors in another. The relationship between them might be the lists of members that will attend the events.

Both the objects and the relationships will be modeled by tables in the database. And the data describing the objects and relationships will be given by the fields (columns) of the tables. The tables for the relationships most often consist of pointers to the objects that are involved. The list of members attending events would include both an id or name for the member and the name or date of the event.

Library Database Example

There are a number of objects that should be part of a library database, but we will look only at the books and the borrowers. The library also has a staff; they contact publishers and book dealers. They hold events and have facilities to manage. All this requires a budget and sources of income. Some portion of the support for a library comes from donors and clients. A complete database, sometimes called an Enterprise Resource Plan (ERP), would encompass all of these areas.

A small portion of a table of library books might look as follows:

The ISBN (International Standard Book Number) is the usual way to keep track of books. A real library table would also have the Library of Congress Number. This catalog number is used to locate a book in the stacks of the library. It is used to keep books on similar topics together.

The table for the borrowers would normally have a lot of data including the complete addresses of the people. This will be replaced here with just their email addresses. However, there are other things to consider. Should we keep all the books that a person borrows together with the address, telephone number, etc.? This might either require us to put an artificial limit on the number of books that someone can borrow or risk a lot of duplicate data.

The following table shows what would happen if we said that no one could take out more than two books at a time.

Another possibility would be to include separate records for each book.

This has several disadvantages. The first is that we no longer have a key field. The ID field has been duplicated a number of times for each borrower. And the obvious other disadvantage is that we have the same data duplicated in several places. This means that any changes to the data, such as new telephone numbers or addresses would have to be made in more than one place. In addition, it really makes no sense to store temporary data, such as the loan data with permanent information about addresses and telephone numbers.

The solution is to construct a separate table just for the loan information. This will be a relationship table, since it will relate the borrowers table to the books table. In the new table we need the borrowers ID, the book’s ISBN, and the date the book is due. The primary key can be created from the ISBN and the borrowers ID. The borrowers ID is duplicated and it could be left out, since the LoanKey contains this information. However, leaving it in makes it easier to find all the books loaned out to a particular borrower.

Entity Relationship Diagrams

It is often easier to see how things fit together using visual information rather than descriptions. Entity relationship diagrams were introduced a number of years ago to help with database design. An entity is an object, such as a book or a borrower. An entity has attributes, such as title, author, name, phone, etc. Several entities can interact in a relationship. In the previous example, a relationship between books and borrowers is formed when a borrower checks out a book from the library.

In the diagrams, entities are usually represented by boxes, relationships by diamonds, and attributes by ovals.