Database Reference
In-Depth Information
and the name redundantly show for each store. Normalization moves the data into their own table and uses an
integer value to link the tables together. The integer values are repeated, but they represent less redundancy than
repeating both the state name and abbreviation columns.
Table Relationships
Relational databases consist of a collection of related columns. Each set of columns forms a relation otherwise
known as a
table
. In Figure
4-2
, you can see a relationship line between Sales and Employees. Although the line
only connects the tables together, it is not difficult to guess that the relationship is between the Sales.EmployeeId
and Employees.EmployeeId columns.
Relationships between the tables are a vital part of any OLTP design, and when you are trying to understand
a particular database, understanding these table relationships is also vital. Before we go any further, let's review
the relationships between the tables in our example database in Figure
4-2
.
Many-to-Many Tables
Let's take a look at the many-to-many relationships within our example. One is the relationship between sales
and titles using three tables: Titles, SalesLineItems, and Sales. This relationship dictates that one title can be on
many sales and one sale can have many titles. The SalesLineItems table contains both SalesId and TitleId, making
this a bridge or junction table. These bridge tables are also called
associative entities
. Whatever you choose to call
them, they provide the link between tables with a many-to-many relationship, like Sales and Titles.
Another example of a many-to-many relationship is the link documented by the TitleAuthors table. This
table defines that one author can write many titles and one title can be written by many authors.
One-to-Many Tables
The Stores table has a one-to-many relationship with the Sales table. The relationship declares that one store can
have many sales, but each sale is associated with only one store.
A similar one-to-many relationship is found between the Sales and Employees tables. This relationship
describes that one sale is associated with a single employee, but one employee can be associated with many
sales.
Parent-Child One-to-Many Tables
Now, let's take a look at an additional relationship defined in the Employees table. This relationship is bound to
itself in the form of employees and managers. One manager can have many employees, but one employee has
only one manager, at least as defined in this database.
A Managers table could have been created instead of defining the relationship in the table itself. In doing so,
however, the relationship would have a single level between a manager and an employee. As it stands now, the
relationship in the Employees table can take on many levels. By that we mean an employee could report to the
manager, but that manager is also an employee who could report to another manager.
The relationship of employees to managers forms a jagged (sometimes called
ragged
) hierarchy. The chain
between the parent and the child may consist of one level, two levels, or many levels. The fact that the number
of levels is unknown is what exemplifies the pattern of parent-child relationships, not unlike how some human
children may provide a parent with grandchildren, but others may not. Those grandchildren in turn may or may
not have children of their own.
