Database Design   «Prev  Next»

Lesson 5 Business rules
Objective Explain the importance of business rules.

Database Business Rules Importance

Business rules are a set of formal, logical statements that define the operational policies, procedures, and constraints of an organization. These rules dictate how data is managed, manipulated, and transformed within a company's databases to ensure consistency, accuracy, and compliance with business objectives and regulatory requirements. In essence, business rules describe the underlying principles governing a company's data management practices, serving as a blueprint for ensuring that data remains reliable and meaningful throughout its lifecycle.
Business rules are typically formulated in a structured language, such as Decision Model and Notation (DMN), which allows for clear communication and understanding among stakeholders. These rules are then implemented and enforced through a combination of database management systems, middleware, and application software. Key aspects of business rules in relation to data stored on a company's databases include:
  1. Data Integrity: Business rules help maintain the integrity of data by defining constraints and validation criteria. These rules ensure that data entered into the database is accurate, consistent, and complete, preventing errors or discrepancies that could lead to poor decision-making or non-compliance with regulations.
  2. Data Transformation: Business rules guide the process of transforming raw data into meaningful information. They define the calculations, aggregations, and other operations required to convert data into useful insights that can be leveraged for strategic decision-making and analysis.
  3. Access Control: Business rules establish the conditions under which data can be accessed, modified, or deleted. They define the roles and permissions required to interact with the data, ensuring that sensitive information is protected from unauthorized access and maintaining compliance with data privacy regulations.
  4. Workflow Management: Business rules dictate the flow of data through various processes and systems within an organization. They determine the sequence of tasks and events required to complete specific business processes, ensuring that data moves efficiently and accurately between systems and stakeholders.
  5. Audit and Compliance: Business rules play a critical role in ensuring that a company's data management practices adhere to relevant industry regulations and standards. They provide a framework for monitoring and evaluating data-related activities, allowing organizations to demonstrate compliance with legal and regulatory requirements.

Business rules are essential for describing the business policies that apply to the data stored on a company's databases. By defining and enforcing these rules, organizations can maintain data integrity, streamline workflows, control access, and ensure regulatory compliance. Ultimately, effective implementation of business rules enables companies to make better-informed decisions, reduce risk, and optimize overall performance.
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[1] of the database.
There are certain constraints[2] that designers apply to ensure that a database honors a company’s business rules. These constraints help preserve data integrity[3]. Business-rules constraints fall into two categories:
  1. field constraints within tables, and
  2. relationship constraints between tables.
For purposes of this lesson, we will resort to the language of general database discussions (for instance, table, field, record).

Field Constraints

There are various field constraints that can be imposed on a database to honor business rules. Consider the example below
  1. Business rule: We ship our fertilizer to just four states: Texas, New Mexico, Oklahoma, and Louisiana.
  2. 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.

Relationship constraints

There are also various constraints that can be placed on the relationships (links) between tables. Consider the example below:
  1. Business rule: Every vendor must supply at least one product.
  2. 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.

Each vendor record must match at least one product record
Each vendor record must match at least one product record. The Matrix Company with VendID 63890 maps to the productID (ProdID) 018.

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

[1]logical schema: The overall logical plan of a database; typically a completed ER diagram.
[2]constraints: Rules a database designer imposes upon certain elements in a database to preserve data integrity.
[3]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.