91
CHAPTER
Designing Bulletproof
Databases
IN THIS CHAPTER
Understanding bulletproof
database design
Normalizing database data
Looking at common table
relationships
Understanding integrity rules
Adding key fields to tables
I
’ve already covered one of the most basic assumptions about relational
database systems — that is, that data is spread across a number of tables
that are related through primary and foreign keys (see Chapters 1 and 2
for a review). Although this basic principle is easy to understand, it can be
much more difficult to understand why and when data should be broken
into separate tables.
Because the data managed by a relational database such as Access exists in a
number of different tables, there must be some way to connect the data. The
more efficiently the database performs these connections, the better and
more flexible the database application as a whole will function.
Although databases are meant to model real-world situations, or at least
manage the data involved in real-world situations, even the most complex
situation is reduced to a number of relationships between pairs of tables. As
the data managed by the database becomes more complex, you may need to
add more tables to the design. For example, a database to manage employee
affairs for a company will include tables for employee information (name,
Social Security number, address, hire date, and so on), payroll information,
benefits programs the employee belongs to, and so on.
This chapter uses a variety of data from different business situations, includ-
ing Northwind Traders (the traditional Access example database), a small
bookstore, and the Collectible Mini Cars application used in other chapters
of this book. Each data set has somewhat different objectives from the others
and is used to emphasize different aspects of relational theory. All the tables
described in this chapter are contained in the Chapter03.accdb database.
When working with the actual data, however, you concentrate on the rela-
tionship between two tables at a time. You might create the employees and