Stay informed about any major changes. Click here to subscribe
How to create relationships between tables
Every table should describe only one type of objects. For instance,
the Employees table contains the data about employees, their names, addresses,
telephone numbers, and any other relevant data. The Customers table contains
only the data that are logically related to customers. It goes without
saying that the data about employees will not be kept in the Customers
table. In case it is necessary to have some link between the employees
and the customers, the best solution would be to create a brand new table
called CustomersEmployees. Instead of complete records, this new table
will contain only unique IDs from both tables.
The same logic can be applied to the example below. In this case, the
Orders table should contain data about orders. The data about the customer
and about the employee who has sold the product to the customer should
be entered into this table.
Tables
Columns
Employees
EmployeeID
FirstName
LastName
HireDate
Title
Customers
CustomerID
ContactName
Address
Country
Phone
Orders
OrderID CustomerID EmployeeID
OrderDate
The data about every order will be entered in the Orders table. All
three tables need to be connected so that the data from ID (primary key)
columns from each table are kept in the Orders table, without entering
all the data about customers and employees. This is achieved by setting
the relationships
between the tables. The Relationships
Manager tool is used to accomplish this task. For the purpose of simplicity,
the number of columns in each of the tables in the figure above has been
reduced to minimum.
EmployeeID
CustomerID
OrderID
CustomerID
