3.1 The Purist s Relational Database Model

---

3.1 The Purist's Relational Database Model

What I call the Purist's relational data model is what the relational database looked like in its original, most pure form. Figure 3.1 shows a Purist's form of the Employees schema introduced in the previous chapter. Note how all the primary keys are composite indexes. All of these composite indexes are concatenations of all keys inherited or passed down from all parent tables above in the hierarchy. The unique identifying key in the entity concerned is placed at the end of each primary key.

Figure 3.1: A Purist's Form of the Employees Schema

The type of primary key structure shown in Figure 3.1 provides effective performance for reporting or retrieval of groups of rows where it is assumed the report output is sorted based on primary key values.

SQL was originally designed to retrieve sets of rows not single rows. Note that the size of the keys is large and in some cases the indexes would occupy as much physical space as the tables themselves. If tables were to be joined, the tables would be joined on the basis of all the elements of each primary key. Following is an example script of a join that selects rows from four tables; note the size of the WHERE clause used to execute the join.

SELECT de.name, pr.name, pt.name, prd.revenue - prd.cost FROM department de, project pr, projecttype pt,    product prd WHERE de.division = pr.division AND de.department = pr.department` AND pr.projecttype = pt.projecttype AND pr.division = prd.division AND pr.department = prd.department AND pr.projecttype = prd.projecttype AND pr.project = prd.project;

Large WHERE clauses would be used for SQL statements using a data model as shown in Figure 3.1. The primary keys would be shortened coded versions of the name fields in each entity. Thus if Division.NAME values are "North East", "South East", "Mid-West" and "Pacific Coast" their related division code values could be NE, SE, MW, and PC, respectively. Without the coded values indexes would be created on the name of each row such as Division.NAME. Coded values were used to make coding easier and to reduce the physical size of indexes.

So that is the Purist's form of the relational database model. Let's now take a look at how object SDKs have affected how relational database models have changed and evolved as a result.

---