Database Tutorial
Establishing Relationships between Tables
In databases it matters very much whether information stored in one row of one table can be matched with only one row of data in another table, or with lots of rows in another table.
Relational databases like Access link information for the one item that is stored in several tables by establishing relationships between these tables. Relationships are based on the same information being stored in both tables.
In the bus database, the customer entity is represented by a table that will store a customer number for each customer:
The booking table will also store the customer number for the customer making the booking, as follows:
The customer ID for the booking table identifies the customer that made the
booking. To find out other details about the customer, we need to
- find the customer ID stored in the booking table for that particular booking, then
- locate that particular customer ID in the customer table, and
- find all the information that is stored in the Customer table on the same record (row) as that customer ID.
As an example, if you want to know the name and address of the person who booked a trip to Mawson for 9 December 2005, you would:
- Find a record that contains "Mawson" as the destination and 9/12/2005 as the trip date.
- Read the customer ID stored on the same row as the information you just found. In this case, the customer ID is "MEG-09".
- To find out who MEG-09 is, transfer to the customer table and locate MEG-09 in the customerID column.
- The details for this customer occupy the same row as the customer ID in the customer table. We can now say that MEG-09 is Sandy Meggs who lives at 72 River View, Here. Sandy has no money owing.
To test these principles, find out the given name of the person booking a trip
for 22 November 2005. You can find the answer by hovering your mouse over this button:
Fields and records
Relational databases are set out and stored as tables, with rows and columns. Each row contains information about the one example of the entity. Each column contains information of the same sort about all examples of the entity. So, considering the booking entity, each row records one booking, and the columns contain all customer IDs (for example), or bus IDs, or destinations, etc. Rows of a database table are called records and columns are called fields.
Business Rules
When defining relationships between tables, you will need to consider information that may not be written down in your snapshot, but which will need to be included as business rules for the database. For example, all destinations are within the distance one driver is permitted to drive in one shift. Therefore, only one driver will be allocated to a booking. Similarly, all bookings are made by one customer for all seats of a bus: the XYZ Bus Company operates a bus hire service, rather than a regular passenger transport route. These business rules make a considerable difference to database design, and need to be clarified prior to creating database tables. There are other business rules for the XYZ Bus Company that we may encounter later.
One-to-many table relationships
Revisiting the above example, we know that one booking will have only one driver, but any one driver may be allocated to several different bookings. Once we know which booking is involved, we can immediately use the database to find out which driver is involved. However, by knowing which driver has a booking may not necessarily determine which booking. This is a one-to-many relationship, which can be illustrated as shown below. Note that all drivers may not necessarily have more than one booking for the relationship to be one-to-many: it is sufficient that the potential exists for more than one booking for each driver, but there must be only one driver for the one booking.
One-to-one table relationships
On the other hand, each customer has their own account summary stored in the database, which is updated from the accountants' database. Each account refers to only one customer. This is a one-to-one relationship. If the company had elected to store multiple payment details for a customer, the customer - account relationship would be one-to-many, similar to the driver - booking relationship described above.
Many-to-many table relationships
However, any one customer can book a bus for any destination, and any one destination can be visited by any customer. This is a many-to-many relationship. Knowing which customer has a booking will not determine details for the exact destination, and knowing the destination will not determine which customer made the booking. There is no way that a direct reference link can be established between customer and destination details: these must be linked through an intermediate table that stores information about both customers and destinations. In the XYZ Bus Company database, this is done via the booking table, as follows:
Many-to-many relationships must be reduced to two one-to-many relationships. You may need to create an intermediary table that consists only of defining information from both tables in the relationship. For example, the customerID and destination name are both found in the booking table. Each entry in the booking table shows which customer booked a bus for which destination on a particular trip.
Table Relationships for the XYZ Bus Company Database
- The Account - Customer table relationship was shown to be a one-to-one relationship above.
- The Driver and Booking tables have a one-to-many relationship.
- Each customer can book many buses, and each bus can be booked by many customers. This is a many-to-many relationship. We will adjust this relationship later.
- Similarly, the Driver - Locality tables have a many-to-many relationship.
- Customers, localities and buses can each be involved in many bookings, whereas each booking can involve only one customer, one destination and one bus. These are one-to-many relationships.
The above relationships can be represented by the following diagram. Lines represent relationships between tables. Black lines show how the database relates information across tables. The red lines show where the database cannot construct a direct relationship between two tables. The table name is shown inside a box. 1 ---- M is a one-to-many relationship, M ---- M is a many-to-many relationship and 1 ---- 1 is a one-to-one relationship.
Once you have understood the analysis above, it is time for you to determine relationships between pairs of tables in your database.
- Show the relationships between all pairs of tables for your database.
You will probably find that some of the tables may have many-to-many relationship between pairs, but all tables should be included in a one-to-many or one-to-one relationship with at least one other table.
What's next?
The next chapter will start from the entities you have identified and the tables that store data about these entities. You will work with the required attributes of these entities to construct the fields for each table.