ASA SQL User's Guide
Designing Your Database
Database design concepts
A relationship between entities is the database equivalent of a verb. An employee is a member of a department, or an office is located in a city. Relationships in a database may appear as foreign key relationships between tables, or may appear as separate tables themselves. You will see examples of each in this chapter.
The relationships in the database are an encoding of rules or practices that govern the data in the entities. If each department has one department head, you can create a one-to-one relationship between departments and employees to identify the department head.
Once a relationship is built into the structure of the database, there is no provision for exceptions. There is nowhere to put a second department head. Duplicating the department entry would involve duplicating the department ID, which is the identifier. Duplicate identifiers are not allowed.
TipStrict database structure can benefit you, because it can eliminate inconsistencies, such as a department with two managers. On the other hand, you as the designer should make your design flexible enough to allow some expansion for unforeseen uses. Extending a well-designed database is usually not too difficult, but modifying the existing table structure can render an entire database and its client applications obsolete. |
There are three kinds of relationships between tables. These correspond to the cardinality (number) of the entities involved in the relationship.
One-to-one relationships You depict a relationship by drawing a line between two entities. The line may have other markings on it such as the two little circles shown. Later sections explain the purpose of these marks. In the following diagram, one employee manages one department.
One-to-many relationships The fact that one item contained in Entity 1 can be associated with multiple entities in Entity 2 is denoted by the multiple lines forming the attachment to Entity 2. In the following diagram, one office can have many phones.
Many-to-many relationships In this case, draw multiple lines for the connections to both entities. This means that one warehouse can hold many different parts, and one type of part can be stored at many warehouses.
You can describe each relationship with two roles. Roles are verbs or phrases that describe the relationship from each point of view. For example, a relationship between employees and departments might be described by the following two roles.
An employee is a member of a department.
A department contains an employee.
Roles are very important because they afford you a convenient and effective means of verifying your work.
TipWhether reading from left-to-right or from right-to-left, the following rule makes it easy to read these diagrams: Read the 1 name of the first entity, 2 role next to the first entity , 3 cardinality from the connection to the second entity , and 4 name of the second entity. |
The little circles just before the end of the line that denotes the relation serve an important purpose. A circle means that an element can exist in the one entity without a corresponding element in the other entity.
If a cross bar appears in place of the circle, that entity must contain at least one element for each element in the other entity. An example will clarify these statements.
This diagram corresponds to the following four statements.
A publisher publishes zero or more books.
A book is published by exactly one publisher.
A book is written by one or more authors.
An author writes zero or more books.
TipThink of the little circle as the digit 0 and the cross bar as the number one. The circle means at least zero . The cross bar means at least one . |
Sometimes, a relationship will exist between entries in a single entity. In this case, the relationship is said to be reflexive. Both ends of the relationship attach to a single entity.
This diagram corresponds to the following two statements.
An employee reports to at most one other employee.
An employee manages zero or more employees.
Notice that in the case of this relation, it is essential that the relation be optional in both directions. Some employees are not managers. Similarly, at least one employee should head the organization and hence report to no one.
Naturally, you would also like to specify that an employee cannot be his or her own manager. This restriction is a type of business rule . Business rules are discussed later as part of The design process.