With relationships, we need to consider those that go both ways or are bidirectional. Let us take a look at some varying business rules that are quite similar to one another that are bidirectional. A customer has a single support rep and an employee could support many customers. In this scenario, this helps to define our relationships between these entities of the customer and employee. There is a one-to-many relationship between the employee and customer table.
Say we had defined this business rule as a customer has a single support rep and an employee could only support one customer. This would now make the relationship a one-to-one relationship. Or perhaps we had the following scenario where a customer can have many support reps and an employee could support many customers. This would generate a many-to-many relationship. All three of these options would have a different design within the dataset.
To properly identify the relationship type between two entities, we have to ask how many instances of the second entity are related to the first and how many instances of the first entity are related to the second. Those questions that are posed will help us answer and define those relationship rules.
Another example could be a business constraint where we may define that the quantity in the invoice must be greater than 0 but we would only allow the maximum number of tracks per invoice to be set to 100. With this option, we would set a CHECK constraint on the quantity column to verify that it is between 1 and 100.
Perhaps there may be an order system that required the shipping of a product. We may define that the order date must be the current date while the shipping date could be empty but if a value is set, it must be equal to or after the order date as it wouldn’t make sense to have a shipping date set prior to the order date.
Perhaps the email of the customer is meant to be used to setup accounts for customers. As such, it may not be setup as the primary key as we have the primary key. However, since it has to be setup for the customer and it has to be different for each customer, it could be setup as NOT NULL and UNIQUE very similar to a candidate key.
Identifying and translating these business rules are key to ensure that we are designing the database to meet the needs of the underlying database system. Note though that as a reminder, not all business rules directly apply to the database as they may apply to the application system.
Source: Authored by Vincent Tran