Establishing Relationships between Objects
Go Up to Designing Databases
The relationship between tables and columns in the database must be defined in the design. For example, how are employees and departments related? An employee can have only one department (a one-to-one relationship), but a department has many employees (a one-to-many relationship). How are projects and employees related? An employee can be working on more than one project, and a project can include several employees (a many-to-many relationship). Each of these different types of relationships has to be modeled in the database.
The relational model represents one-to-many relationships with primary key/foreign key pairings. Refer to the following two tables. A project can include many employees, so to avoid duplication of employee data, the PROJECT table can reference employee information with a foreign key. TEAM_LEADER is a foreign key referencing the primary key, EMP_NO, in the EMPLOYEE table.
PROJECT table
PROJ_ID
|
TEAM_LEADER
|
PROJ_NAME
|
PROJ_DESC
|
PRODUCT
|
|---|---|---|---|---|
|
DGPII |
44 |
Automap |
blob data |
hardware |
|
VBASE |
47 |
Video database |
blob data |
software |
|
HWRII |
24 |
Translator upgrade |
blob data |
software |
EMPLOYEE table
EMP_NO
|
LAST_NAME
|
FIRST_NAME
|
DEPT_NO
|
JOB_CODE
|
PHONE_EXT
|
SALARY
|
|---|---|---|---|---|---|---|
|
24 |
Smith |
John |
100 |
Eng |
4968 |
64000 |
|
48 |
Carter |
Catherine |
900 |
Sales |
4967 |
72500 |
|
36 |
Smith |
Jane |
600 |
Admin |
4800 |
37500 |
For more information on using PRIMARY KEY and FOREIGN KEY constraints, see Working with Tables.
Topics
- Enforcing Referential Integrity
- Normalizing the Database
- Choosing Indexes
- Increasing Cache Size
- Creating a Multifile, Distributed Database