SQL Bible, Second Edition

Appendix C. Basics of Relational Database Design

Database design still remains more of an art than an exact science. A database does not exist in a vacuum, a thing unto its own; it serves some specific business purpose, and it is reflected in its design. General database types could be divided into two broad categories: operational databases and analytical databases. The operational database (OLTP — online transaction processing) handles day-to-day operations: recording data, printing payroll checks, and so on. The data in such a database changes rather frequently. Examples of this kind of database include the ACME database supplied in this book.

Analytical databases (OLAP — online analytical processing) are used to store historical data, which is analyzed for reporting purposes, used to generate statistics, and so on. The information in such a database is mostly static; new data can be added, but the historic data cannot usually be modified. In addition, the information in the OLAP database is stored in an aggregated state and therefore non-normalized (see more about normalization later in the chapter); there are many levels of aggregation, depending on the particular purpose. Designing of an OLAP database goes beyond the basic introduction of this appendix, and is touched on only briefly here.

Note

Several database types are listed in Chapter 1, "SQL and Relational Database Management Systems (RDBMS)", and the examples throughout the book use the ACME database, which is a fictitious ...