Translating Business Rules

1. Bidirectional Relationships

Some relationships between entities are bidirectional, or go both ways. Let's take a look at some business rules that help to define relationships.

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 help us to answer and define those relationship rules.

2. Identifying Constraints

EXAMPLE

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 business rule in force, we would set a CHECK constraint on the quantity of tracks column to verify that it is between 1 and 100.

EXAMPLE

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. However, if a value is set, it must be equal to or after the order date because it wouldn’t make sense to have a shipping date set prior to the order date.

EXAMPLE

Perhaps the customer's email is meant to be used to set up customer accounts. As such, it may not be useful as the primary key, because we may separately create the primary key. However, because it has to be set up for the customer and it has to be different for each customer, it could be set up as NOT NULL and UNIQUE value, very similar to a candidate key.

Identifying and translating these business rules are key to ensuring that we are designing the database to meet the needs of the underlying database system. Remember, though, that not all business rules directly apply to the database, as they may apply to the application system where data is input or modified.