Understanding Relational Database Design

You use SQL joins to perform operations and extract data from relational databases. As such, an understanding of relational database design is a prerequisite to successfully using joins.

Relational databases are sets of tables that each store parts of a complete data set; these parts relate to each other, and thus are termed "relational". The underlying principle here is that data should be organized so that it never has to be repeated (stored more than once), while at the same time keeping it in small, manageable sets.

NOTE

Databases that are not relational are often referred to as being "flat."

Some examples will help you understand this concept:

• A list of employees could be stored in a flat tableone big (and wide) table with employee and department information in each row. But employees are members of departments, and many employees share the same department information. Relational database design would then dictate that the data be broken into three tablesan employee table, a department table, and a third table that connects the two by storing the primary keys of employees and departments to relate them to each other.

• An orders database is another classic example of data that should be stored relationally. Customer information is not tied to a specific order (a customer may have many orders), order items are not tied to a specific order (an order may have multiple order items), and products are not tied to specific orders (a single product could be part of many orders). Relational design might require a table for products, a table for customers, a table for orders (which relates to the customers table), and a table for order items (which relates to both the orders and products tables).

When you're planning relational databases, there is no real right or wrong design, and there is always more than one way to lay out the tables. The following are some of the issues that you need to consider when planning relational table design:

• Data access. The less relational the data is, the harder it will be to extract required data filtered or ordered as needed.

• Maintainability. Relational databases can be maintained far more easily, but special attention has to be paid to the links between tables so as not to break them.

• Storage. A well-designed relational database can use disk space far more efficiently.

• Performance. Relational databases can perform far quicker than flat databases, if the design is well thought out and implemented.

NOTE

Primary keys, which were introduced in Chapter 44, "Basic SQL," are columns within tables that uniquely identify every row within those tables. Primary keys are an important part of relational database design because they are the values that connect tables to each other.

NOTE

A full discussion of relational database design is beyond the scope of this chapter and book. For an explanation of relational databases from a ColdFusion perspective, see ColdFusion MX 7 Web Application Construction Kit (ISBN: 0-321-22367-5).

Another important type of key used in relational databases is the foreign key. It is a column within a table whose value is that of another table's primary key. For example, an orders table might use the order number as a primary key, in which case the order number stored in the related order items table is a foreign key. Unlike primary keys, foreign keys need not be unique, and they can be updated as needed. Like primary keys, they always have values (and should not be NULL).