Packages and Reflexive Relationships
Designing Database Tables – Part 9
Division 2
Forward: In this part of the series, we look at reflexive relationships in packages as you design your database.
By: Chrysanthus Date Published: 5 Aug 2012
Introduction
In the last part of the series, we were looking at the purchasing package. There are relationships in the package. We have seen three of them. Let us look at the fourth in this part of the series. It is the reflexive association.
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.
Meaning of Reflexive Relationship
A reflexive association is a relationship from a class (table) back to itself.
Example
You will have to open the following link in a browser tab in order to see the table and diagram for this tutorial (link below).
A very common business situation for the reflexive relationship is with an employee table. Table 10.1 shows a sample Employee table. In this table there is an EmployeeID column and a Manager column. A relationship exists between employees and managers. A manager is also an employee and this makes the relationship reflexive.
There are three managers in the table. These managers are the CEO, the Sales Manager and the Factory Manager. Information for each employee is in a row. In the manager cell of each row, you have the ID of the employee's manager. Since there are three managers among the employees, you can only find three possible values (IDs) in the Manager column.
The business rule for the above table is that each employee can only have one manager. This means a one-to-many relationship between manager and employee. It is possible to have a company where each employee can have more than one manager; however, I will not address that.
Class Diagram
Fig 10.1 shows the class diagram for the above table.
In one of the previous parts of this series, the employee class was drawn in the Purchasing Package. That is alright since if it was not done that way, the analysis of the Purchasing class would not be complete. The Employee class actually is a package on its own. This is because the company that asks you to write the database treats it as a major unit. It can be expanded to include things like professions, salary, increments, age, retirement and human recourse management. So for a big company the Employee package can have several tables.
The Employee table as it is, in our example, is in at least, 3NF.
For simplicity our motorcycle company has just one table for the package. It is linked to the Purchasing Package through the EmpID (EmployeeID) of the Employee table in the Employee package and the EmpID of PurchaseOrder table in the Purchasing package.
At this point, we have finished with the analysis inside the Purchasing package and because of the simply nature of our Employee package, we have finished with the analysis inside the Employee package.
Well, a rather short tutorial. Let us end here and continue in the next part of the series.
Chrys
Related Links
Major in Website DesignWeb Development Course
HTML Course
CSS Course
ECMAScript Course
NEXT