Packages and Many-To-Many Relationships
Designing Database Tables Part 7
Forward: In this part of the series, we look at many-to many relationships in packages as you design your database.
By: Chrysanthus Date Published: 5 Aug 2012
Introduction
Note: If you cannot see the code or if you think anything is missing (broken link, image absent), just contact me at forchatrans@yahoo.com. That is, contact me for the slightest problem you have about what you are reading.
Breaking down the Purchasing Package
Open the following link in a new tab window. You should be toggling between this page and the newly opened page as you read.
From the discussion you have had with the workers of the company that wants the database, you should be able to come up with the broken down package in fig 19.1 in the opened web page. Before you can break down a package, you must understand the existence of the different associations (relationships) that I described in the first division of this series and that I mentioned in the previous part of this division. You may need one, two or three steps to arrive at the diagram in fig 19.1 in the opened web page.
- A one-to-many relationship between the supplier class and the Purchase Order class and also a one-to-many relationship between the Employee class and the Purchase Order class.
- A many-to-many relationship between the purchase order class and the Item class.
- A subtype (generalization) relationship between the Item class and the three classes below the Item class.
- A reflexive relationship within the Employee class.
In this tutorial we shall look at the first two relationships. We shall look at the other two in the following parts.
At this level, for any package you will place a common column in all the tables for any relationship. All the classes in fig. 19.1 in the opened tab window represent tables (not necessarily in 3NF). For each of the four relationships here you will have to place a common column in each of the tables concerned. This column should be a key in one of the tables. You will not do this arbitrarily; I explain below and in the following parts how that is done.
There are two one-to-many relationships: a one-to-many relationship between the supplier class and the Purchase Order class and also a one-to-many relationship between the Employee class and the Purchase Order class. For the first one-to-many relationships, the company can send many purchase orders to a particular supplier but only one supplier can appear in a purchase order. In the other one-to-many relationships, one employee can create many purchase orders but only one purchase order can be created by one employee.
Note: we are in the process of creating normalized tables (tables in at least 3NF) from a class diagram, which we had from a package. The two one-to-many relationships are connected as can be seen in fig. 19.1, so they will be treated together. The tables (classes) do not have any columns yet: Put a primary key in each of the tables that are on the side of one in the one-to-many relationships (Supplier and Employee). The two tables should now be like these:
Supplier(SuplierID, )
Employee(EmpID, )
At this point, if there are any columns that you are sure will go into any of the tables you write them. If you are not sure for any table, do not write anything. You may end up with something like:
Supplier(SuplierID, Name, Address, City, State, ZipCode, PhoneNumber)
Employee(EmpID, Name, Address, Salary, )
For these two tables, we can say one is sure of the new columns in the Supplier table, but one is sure of only three new columns of the Employee table and not sure of the rest or even if they exist; that is why you have the ellipse .
Recall that all class (table) diagrams are linked by keys. So place the SuplierID column from the supplier table and the EmpID column from the Employee table in the PurchaseOrder table (class). The PurchaseOrder table is on the many side of the two one-to-many relationships. You should have this:
PurchaseOrder(SuplierID, EmpID, )
These two columns in the PurchaseOrder table form a composite primary key for the Purchase Order. Now add columns that you are sure should be in the PurchaseOrder table; if you are not sure, do not put anything. You should have something like:
PurchaseOrder(SuplierID, EmpID, Date, )
Remember, primary keys, whether composite or single, should be underlined in the table notations.
We have arrived at three normalized tables; they are in the third normal form. Note: There is a slightly different way you can derive the tables to arrive at tables such that the PurchaseOrder table will be at a form higher than the 3NF, called the BCNF. I hope to write a separate article on that; the article will be titled, something like, Arriving at Boyce-Codd Normal Form.
So for the connected one-to-many relationships, the three classes of the class diagram are now established in 3NF as
Supplier(SuplierID, Name, Address, City, State, ZipCode, PhoneNumber)
Employee(EmpID, Name, Address, Salary, )
PurchaseOrder(SuplierID, EmpID, Date, )
Look at fig19.1 in the tab window again. There is a many-to-many relationship in the PurchaseOrder class and the Item class. A many-to-many association is always broken down into two one-to-many associations. How do you create normalized tables from many-to-many relationship tables? It is simple: Give a primary key to each of the tables at either ends of the connection. Create a new table in the middle of the two tables. This new table represents the many-to-many relationship. Go to this new table; place the keys of the tables at the end, in it. The two keys you have placed form the composite key for the new table. That gives you one-to-many relationship between each table at the end and the middle table. The three tables are now in at least the 3NF.
For our example, the PurchaseOrder table already has a primary key; the primary key is a composite key, which is SuplierID, EmpID. Let us give the key, ItemID, to the Item table. The Item table is now,
Item(ItemID, )
At this point, if you know certain columns and you are sure that they can go into the Item tables, put the columns; if you are not sure, do not put in any columns; you will still be able to do that at the end. You should have something like,
Item(ItemID, Description, Price)
You need to give a name for the middle table between PurchaseOrder and Item. At this point, you should be able to give a meaningful name to the middle table. If you cannot give a meaningful name it means you have made an error somewhere in the derivation. Let us give the name ItemPurchased. This name logically links the two tables. So the three tables are
PurchaseOrder(SuplierID, EmpID, Date, )
Item(ItemID, Description, Price)
ItemPurchased(SuplierID, EmpID, ItemID, )
The primary key of the PurchaseOrder table, is the composite key, SuplierID, EmpID. The primary key of the Item table is ItemID. The primary key of the ItemPurchased table is the new composite key, SuplierID, EmpID, ItemID. You can now add columns in the ItemPurchased table, if you are sure of them. If you are not sure, then do not do that; you will be able to do that at the end. For the ItemPurchased table you may have something like,
ItemPurchased(SuplierID, EmpID, ItemID, Quantity, PaidPrice)
Well, you now know how to produce normalized tables from one-to-many relationships and from many-to-many relationship. Remember, tables will finally be connected by their keys.
I have to point out here that strictly speaking, the employee table is not in the Purchase package, but we had to put it here and you will have to do similar things in your commercial projects, for the analysis to be complete.
Time to take a break; we continue in the next part.
Chrys
Related Links
Major in Website DesignWeb Development Course
HTML Course
CSS Course
ECMAScript Course
NEXT