When designing a database schema, one of the first things that we do is to place multi-valued or repeating attributes into a table of their own. An example of a multi-valued attribute would be the classifications or positions an employee has held in their career. Since the employee can have an unlimited number of classifications, it is not efficient to place this attribute(s) in the Employee table. This would increase the size of the table, make it hard to analyze, and cause the schema to change when more classifications are held than the schema can handle. For these reasons, multi-valued attributes are placed into a related table of their own.
The following is just such a schema. The base entity, Employee, can have a number of different classifications throughout their careers. It is required that the various classifications and the date they were achieved be retained. It's decided that the multi-valued location attribute be placed into its own table.
This schema conforms to the rules of normalization and results in a space-efficient and flexible database. However, there is one problem with this schema. A clerk must regularly make a report of the employees and their current classifications. In order to identify the employee's current classification, the query must first identify the most recent classification date for an employee and then determine the classification for that date. The following is the SQL:
SELECT employee_department, employee_number,
classification_date, classification
FROM employee, classifications
WHERE employee_number = fk_employee_number
AND classification_date = (SELECT max(classification_date)
FROM classifications
WHERE fk_employee_number = employee.employee_number);
This query is likely to be far too difficult for the inexperienced clerk to write. It also requires a great deal of work from the database manager. In order to eliminate this problem, a current classification date and classification attribute can be added to the Employee table. As the values are updated, the updated value is placed into the Classification table. The following is this star schema:
This schema will likely empower the clerk to develop the queries that are needed. The clerk does not have to join tables or try to perform correlated subqueries. This change will increase the user friendliness of the database. Of course, it will also increase the complexity of the on-line transaction processing (OLTP) system since the application must populate the "Historic Classifications" table.
After I design a database, I always try to imagine the normal day-to-day queries/reports that will be produced from the database. This exercise will point out problems such as the complex SQL statement illustrated above and allow me to make minor modification such as the one I illustrated.
I believe databases should be both efficient as an OLTP and able to support user information needs when possible. I hate to develop special extracted business objects when simple changes such as the above can be done. The creation of business objects requires valuable analyst time and daily machine processing. It is far better to soften your database design before the application is developed whenever possible.
Writing imaginary queries against your database and incorporating modifications such as I illustrated will help you develop a more usable and valuable database. It will also reduce your future DA and DBA work by reducing the number of business objects needed from your OLTP databases.
John Palinski has been an Oracle developer and project manager for the last fourteen years. He developed one of one of the first relational database work management systems in the country. Mr. Palinski also teaches his own Oracle courses at Iowa Western Community College and the University of Nebraska at Omaha. Mr. Palinski is the author of the "Oracle Database Construction Kit" (available at the Bookstore) and provides custom Oracle training worldwide through his consulting business, Realistic Software Training.
For More Information
