Database N-to-N Relationships
Database Essentials - Part 5
Division 1
Forward: In this part of the tutorial we look at one-to-one, one-to-many and many-to-many relationships.
By: Chrysanthus Date Published: 4 Aug 2012
Introduction
One-to-one Relationships
Imagine that you are a pet store seller; that is you own a store where to sell pets. Open the following link to see the tables of this tutorial (link below).
Table 5.1 shows a table for the animals (pets). In this table all animal characteristics are table attributes. The table is very small for pedagogic reasons. The values in the table are imaginary; let us allow this simple situation for pedagogic reasons. Let us also assume that the first two animals (rows) are mammals and the second two rows are fishes.
Your customers are people who want to own pets. All animals do not share the same characteristics and a customer may not know all animal characteristics. A mammal (e.g. cat) buyer may want to know the size of the litter or if the animal has claws and he does not know and does not care about characteristics of fishes. On the other hand a fish buyer may not care about mammal characteristics; he might want to know if his would-be pet (fish) is fresh water or salt water fish.
In the table, note that each animal has certain generic properties. The generic properties (attributes) are DateBorn, Name, Gender and color. You can consider the ID as a generic property. However, in the table, the properties LitterSize and Claws are only for mammal, not for the other animal categories (mammal is one category and fishes is another category). The properties, FreshWater and ScaleCondition are only for the fishes category and not for the other animal categories. This is why you have some empty cells in the table.
Table 5.1 is not a good table. You need a class hierarchy of tables. The main table will be one with the generic properties and the other two will be for each category. Table 5.2 shows the main table called Animal table. Table 5.3 shows the Mammal table and Table 5.4 shows the Fishes table.
Each of the category table has a one-to-one relationship (association) with the main table. That is, for each row in a category (mammal or fishes) table there is only one row in the main table.
In the previous tutorial we saw an example of one-to-many relationship. There was a sale table and a Sale Item table. Each row in the Sale table would correspond to at least zero row in the Sale Item table (you can refer to the previous tutorial). The class diagram we saw in the previous part has been redrawn in fig. 5.1 of the browser tab window, vertically.
Look at the diagram again. There is the number 1 by the line next to the sale class (table). There is a * by the line next to the SaleItem class. The * means many. So the 1 and the * by the line mean one-to-many.
Sometimes it is good to give the minimum and maximum number of rows involve in a one-to-many relationship. In normal life it is possible to have a customer who has bought nothing. In other words a customer has to exist before any item is bought from a store or anywhere else (e.g. supermarket). When you go into a supermarket, you have to be present at the supermarket first as a customer before you can buy anything. When you place an order from a company, at the company, your credential information has to be recorded first before the items can be sent to you. So a customer must exist first before anything can be bought by him.
The sale and the SaleItem tables of the previous tutorial have been repeated in the browser window tab for this tutorial. Each relationship consists of one row in the sale table and zero or more rows in the SaleItem table. The first row in the Sale table corresponds to three rows in the SaleItem table. The second row in the Sale table corresponds to two rows in the SaleItem table. The third row in the Sale table corresponds to three rows in the SaleItem table. So, for all the relationships of the two tables, the minimum number of rows in the SaleItem table is 2 and the maximum is 3. For each of the relationships there is one and only one row in the Sale table, giving a minimum of 1 row and a maximum of 1 row at the Sale table for each relationship.
If we want to be exact for the class diagram, we would have to type “1…1” by the line next to the Sale table and “2…3” by the line next to the SaleItem table. The number on the left of the ellipse (…) is the minimum number of rows for a possible relationship and the number on the right is the maximum number of rows in the relationship. To obtain “2…3” we look at the tables and did not follow the follow business rules.
When dealing with database, you must always follow business rules. There is the rule that a customer can exist without purchasing anything. This gives a minimum value of zero at the end of the line for the SaleItem table. In practice, there is no limit as to the number of purchases (items) that the customer can make. This many but undefined limit is represented in the class diagram by *. So according to business rules, instead of “2…3”, we should have “0…*”. At the Sale end, there can be one and only one row for any relationship, giving a minimum of 1 row and a maximum of 1 row at the Sale end for any relationship. Fig 5.2 illustrates this.
This is another kind of association. Table 5.7, Table 5.8 and Table 5.9 illustrate this. These are three table of a wholesale company. Table 5.7 is the Order Table having the orders that were placed by the company to suppliers (manufacturers). The actual items (products) ordered are not in this table. Table 5.8 is called the OrderItem table. It shows which item (ItemID) was ordered for a particular order. Table 5.9 is the item table. It shows the items (products) that the company normally orders and sells.
The Order table has the OrderID column, which is the primary key. It has the date column, which shows the date the order was made. It has the supplierID column indicating the supplier in terms of ID who made the supply. There is a supplier table (not shown) that has information (credentials) for each supplier. In the supplier table, the supplierID uniquely identifies a supplier. The Order table has an employee column indicating in terms of ID, the employee who actually placed and typed the order in the wholesale company. You saw an example of an employee table (not shown here) in one of the previous tutorials.
The OrderItem table has the OrderID and the ItemID. These two columns form the primary key of the of the OrderItem table. The OrderItem table shows which item was ordered for which order. It also shows the quantity of item ordered in its third column. The last column of this table shows the actual price paid.
The Item table shows the items (products) that the company normally orders and sells. This first column of this table is the ItemID, which is the primary key. The second column describes the item. The third column shows the price you budget for each item.
The many-to-many relationship exists between the Order table and the Item table. This many-to-many relationship is actually indicated by the OrderItem table. We see from the two ID columns of the OrderItem table that OrderID 1 corresponds to ItemID 1 and 2. This is a one-to-many relationship between the Order table and the Item table. We also see from the OrderItem table in the opposite direction that ItemID 2 corresponds to OrderID 1 and 2. This is a one-to-many relationship between the Item table and the Order table.
We have demonstrated that there is a one-to-many relationship between the Order table and the Item table. We have also demonstrated that in the other direction, there is a one-to-many relationship between the Item table and the Order table. Therefore there is a many-to-many relationship between the Order table and the Item table. However, there is a one-to-many relationship between the Order table and OrderItem table and still a one-to-many relationship between the Item table and the OrderItem table.
Fig 5.3 shows a class diagram for many-to-many relationship. Note how two asterisks have been used.
Many-to-many relationships are hardly implemented (coded) for a database. Implementing a many-to-many relationship is very difficult and unnecessary. When you have a many-to-many relationship, you have to break it down to one-to-many relationships, before implementation; we shall see how to do this in a different series (division).
The aim of the tutorials concerning relationships (associations) is not to show you how to create tables (and know which columns a table should have) or derive relationships; but it is to show you the types of relationships that exist in databases. We shall learn how to create tables and derive relationships with information you get from company users in a different series (division). The business rules for a database are given to you by the people who will use the database. Similar companies have similar business rules. With experience you understand the basic business rules for common companies. We shall see all of these in a different series.
Let us end here for this part of the series. We continue in the next part still with associations.
Chrys
Related Links
Major in Website DesignWeb Development Course
HTML Course
CSS Course
ECMAScript Course
NEXT