Understanding the Process

Designing a database to be built with ALLBASE/SQL means examining the data you wish to store and then putting it into a form that ALLBASE/SQL can understand. In other words, you look at the logical relationships that exist within the data and then create a relational design (tables, views, indexes, etc.). The process can be complex, and you could use a number of formal design methodologies. However, you can get a good first approximation by using an intuitive approach, which is sketched very briefly in this chapter.

Database design is the subject of much theoretical discussion and debate, but everyone agrees that good design results in good performance. Good database design also gives you the greatest flexibility in formulating your queries and in restructuring your databases when that becomes necessary. For these reasons, time spent on analysis and design "up front" results in time and money saved in developing a production system.

A reading list at the end of this chapter provides references to additional information. If you are about to embark on a complex design, be sure to consult this material.

A Small Sample Database

To examine some data modeling techniques, let's imagine a small sample database. Suppose a radio station wants to create a database of classical music recordings for use by program directors and announcers. The station needs this information to plan a schedule of broadcasts, to maintain a log of what is played, and to publish a monthly listener's guide. Here are some specific data items that will be needed:

• Recording company and date recorded.

• Album title.

• Album price.

• Medium.

• List of selections and timings.

• Total timing of each album.

• Names of orchestra, conductor, singers, accompanists.

• Composer's name, birthplace, and dates of birth and death.

• Comments on composers, albums, and selections.

• Date, time, and announcer for each selection played.

You can probably think of other items of information that might be useful (for example, the date the album was acquired), but let's use these for now. How should this data be organized?

How Will the Data Be Used?

A typical user of the database would be the program director, who might ask questions like the following:

• What selections do I have by Beethoven that are less than 20 minutes long?

• What did we broadcast last year on Beethoven's birthday?

• How many different versions of Beethoven's Fifth Symphony do we have, who are the conductors, and what are the timings?

• What composers represented in our library were born in March?

• What selections did George play last Tuesday morning on his chamber music show?