Identifying Logical Relationships and Foreign Keys
Designing Database Tables – Part 17
Forward: In this part of the series, we see how to identify logical relationships and foreign keys in a 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.
Logical Relationships
A one-to-one relationship between two tables is a logical relationship. A one-to-many relationship between two tables is also a logical relationship.
Foreign Key
A foreign key is a column in one table and in another table, it is a primary key. A foreign key does not need to have unique values in its table; however, where it is a primary key it should have unique values.
Step 6: Identifying Logical Relationships and Foreign Keys
This is the last step in the table creation process. In this step you identify the logical relationships between the tables and you also identify the foreign keys. Hey, you also have to identify the reflexive relationships at this stage. Two tables can be linked through primary keys in both tables and they can also be linked through a primary key in one table and a foreign key in another table. The tables of our example again are:
Customers(CustomerID, Address, City, State, Country, Phone)
Sales(SaleID, Date, EmployeeID, CustomerID)
SaleDetails(SaleID, ProductID, SoldPrice)
Products(ProductID, Category, Number, CostPrice SellingPrice)
Orders(OrderID, Date, EmployeeID, SupplierID)
OrderDetails(OrderID, ProductID, BoughtPrice, Discount)
Suppliers(SupplierID, Address, City, State, Country, Phone)
Employee(EmployeeID, Address, City, State, Country, Phone)
The logical relationships are as follows:
- One-to-many relationship between the Sales table and the Customers table, through the SaleID column (the same customer can come for different sales).
- One-to-many relationship between the Sales table and the SaleDetails table, through the SaleID column.
- One-to-many relationship between the Products table and the SaleDetails table, through the ProductID column.
- One-to-many relationship between the Products table and the OrderDetails table, through the ProductID column.
- One-to-many relationship between the Suppliers table and the Orders table, through the SupplierID column (the same supplier can supply for different orders).
- One-to-many relationship between the Employee table and the Sales table through the EmployeeID column.
- One-to-many relationship between the Employee table and the Orders table through the EmployeeID column.
- A reflexive relationship within the Employee table, where one manager supervises many employees.
Are your tables truly normalized? Step 3 of this process is the most important step, where you produce the entities. However, when you do it, you cannot guarantee that your resulting tables (entities) are normalized (higher or equal to 3NF). To be sure that your tables are normalized, use the Form and Report Approach with the table notations on all the tables.
The above tables are normalized.
Assignment
I normally do not give assignments in my tutorials, but I will give one here because I think you will find it interesting:
Draw the class diagram, indicating the relationships between entities of a Whole Sale Company whose normalized tables are given above.
We have come to the end of this division. I hope you appreciated it. In the next division titled, “Efficient Database Design” we shall see how to make a database efficient, when implemented (coded).
Chrys
Related Links
Major in Website DesignWeb Development Course
HTML Course
CSS Course
ECMAScript Course