RelationalDBDesignRelationalDBDesign


Physical Design   «Prev  Next»
Lesson 3 Business objects and rules
Objective Describe mistakes associated with business objects and rules.

Business objects and rules

As mentioned in Database Design the first course in this series, one of the first steps in designing a relational database is to identify which business rules will need to be represented in the database. In the case of Stories on CD and the CDs the company orders from distributors, there are four business objects that need to be represented in the database:
  1. CDs,
  2. Categories,
  3. Distributors, and
  4. Orders.
While this set of objects seems small, these four objects are all that are needed to track Stories on CD's orders from their distributors.
Design mistakes associated with business objects[1] generally fall into one of two categories:
  1. Representing more than one business object in a single table
  2. Including too many objects or too many attributes of an object in the database

More than one object per table

Perhaps the most basic mistake in creating database tables is attempting to represent more than one business object in a single table. It might be tempting to store the name and address of a CD's distributor in the same table as the CD's information, but it's much more efficient to store alldistributor data in a separate table and link to that table using a primary key/foreign key pair.
Note: Just because a business object can be converted into a database doesn’t always mean that it should be.

Relational Database Design

Too much information

Another common mistake of database design is to include too many objects, or too many attributes of an object, in a database. Remember that a database is a representation of reality, not an exact replica.
For instance, it might be tempting to create an object representing Stories on CD employees in this database, but the owners may have decided that they didn't need to know which employee placed a customer’s order.
If they change their minds, a table with that information can be created later, or, if an Employee table exists in another database on the same system and managed by the same RDBMS, you should be able to use the Employee table from the other database in the views and queries for this database.
A similar mistake is to represent too many details about business objects. It might seem useful to store the name of the studio where a particular CD was recorded, but entering that data for every CD would mean a lot of work for very little gain.

Business rules

Designers sometimes mistakenly attempt to enforce business rules[2] in the database itself. For example, a Stories on CD business rule may be stated as: "We only sell CDs created for children ages 14 and younger." Attempting to enforce that rule in the database is difficult because distributors may use different coding systems to indicate a CD’s target age group. In the end, it might be better to allow the employee who places an order to use their judgment in determining which CDs are appropriate for Stories on CD's target audience. In general, then, implementing business rules as database constraints is appropriate if and when those constraints:
  1. Help to preserve data integrity
  2. Don’t undermine potential growth in the company (which would require database? revision).

The next lesson describes mistakes associated with columns.

Business Objects-Quiz


Before moving on to the next lesson, click the Quiz link below to reinforce your understanding of common database design mistakes.
Business Objects-Quiz
[1]business objects: These are the items used in a business environment which are related and contain store data with respect to the business process. (customers, products, orders).
[2]Business rules: A set of rules or conditions describing the business polices that apply to the data stored on a company database and tables. Policies that apply to the data stored in a database.