Business rules describe the business policies that apply to the data stored on a company's databases. In other words, business rules reflect how a business perceives its use of data. Some business rules are especially important to the database designer because they can be incorporated into the logical schema of the database.
There are certain constraints that designers apply to ensure that a database honors a company’s business rules. These constraints help preserve data integrity. Business-rules constraints fall into two categories:
field constraints within tables, and
relationship constraints between tables.
For purposes of this lesson, we will resort to the language of general database discussions (for instance, table, field, record).
There are various field constraints that can be imposed on a database to honor business rules. Consider the example below
Business rule: We ship our fertilizer to just four states: Texas, New Mexico, Oklahoma, and Louisiana.
Field constraint: These states are represented in a Customers table in a field called State as: TX, NM, OK, and LA.
A constraint is placed on the State field so that only those four state abbreviations are accepted into the database for that specific table.
Constraints are especially common on date fields, where dates would become meaningless in a database if a product’s ship date, for example, was earlier than the customer-order date for that product,
or if an employee’s termination date was prior to his or her hire date.
There are also various constraints that can be placed on the relationships (links) between tables. Consider the example below:
Business rule: Every vendor must supply at least one product.
Relationship constraint: The relationship between the Vendors table and Products table must be governed by a participation constraint wherein a single record in the Vendors table must be related to at least one record in the Products table.
The following tables illustrate the relationship.
Relationship constraints (discussed later in this course) dictate that certain entities in a relationship have mandatory status, while others have optional status. Documentation continues for the designer, who should be creating a list of business rules for the organization as reflected in any constraints (if applicable) placed on the existing database(s). Developing an eye for constraints is mostly a matter of experience.
The next lesson discusses the process of interviewing users of data.
Business Rules - Exercise
Before moving on to the next lesson, click the Exercise link below to check your understanding of business rules and constraints. Business Rules - Exercise
logical schema: The overall logical plan of a database; typically a completed ER diagram.
constraints: Rules a database designer imposes upon certain elements in a database to preserve data integrity.
data integrity: A term used to describe the quality (in terms of accuracy, consistency, and validity) of data in a database, in the sense that values required to enforce data relationships actually exist. Problems with data integrity occur when a value in one table that’s supposed to relate to a value in another can’t, because the second value either has been deleted or was never entered.