Relational Databases

“Flat File Databases” exist in two dimensions of columns (the vertical dimension) and rows (the horizontal dimension). Columns represent individual fields of data where rows represent single records of data. Conversely, “Relational Databases” exist in three dimensions where tables of fields and records can be linked to each other (“Joined”) to create the third dimension. This “depth dimension” allows users of relational databases to analyze their data according to a stream of consciousness whereby one record or a single field of data within a record can lead to additional content (either macro or micro) with the simple click of a button.

When speaking of a relational database, we are speaking of a “normalized” database. Normalization is the process of organizing data in a database. This includes creating tables and establishing relationships between those tables according to rules designed both to protect the data and to make the database more flexible by eliminating redundancy and inconsistent dependency (Credit: Microsoft Press).

The high-level objectives of normalization are as follows:

• Eliminate redundant data entry and storage
• Create logical relationships between elements of data and the records they compose

Relational databases are constructed (architected) according to the Rules of Normalization. These rules represent Best Practice when design the data model and information flow that will serve as the backbone of a relational database application. The rules of normalization are as follows:

1. Eliminate repetitive groups of content – Create a separate data table within the database for each related subject (Customers, Invoices, Product Inventory, etc.)

2. Eliminate redundant data – Create relationships between data tables that eliminate the need to enter the same pieces of data more than once

3. Eliminate table fields that are not related to the Key Field of that table – The “Key Field” defines the “topic” of the content of each data table. In an employee table, the key field may be the employee number. In a Customer Invoice Table, the key field may be the invoice number. In the first example, we would not expect to see fields containing the numbers of all invoices attributed to a salesperson and in the second example, we would not expect the name of the sales person to be stored in the table where all invoice information is maintained. According to the third normal state, we would expect to see a single table containing all content related to the employee (Name, Hire Date, Job Title, etc.) and a single table containing all content related to the customer invoices (Invoice Number, Invoice Date, Total Amount Due, etc.) with each of these tables linked to each other so as to be able to answer the questions “Who sold this product?” and “What products has this salesperson sold?”

4. Isolate many-to-many relationships using a cross-reference table – Where many records in a table may be related to many records in another table (consider a class scheduling database where each individual student may enroll in multiple classes and each individual class my have multiple students enrolled). In this example, a third table – a Cross-Reference Table – would exist that would contain all enrollment records for all classes. This X-Ref table would be linked to both the Student Body table and the Class Curriculum Table and would answer the questions “what classes has this student taken?” and “which students attended this class?”