JCK - A Veteran-Owned Small Business

since 1989

America Support You
4348 Pine Grove Ave
Fort Gratiot MI USA
V: 810 982-8639
The Weakest Link in Business

Data Structure and User Reality

Business Events and Mathematical Modeling

What is User Focused Data Architecture?
Sample: A small operation tracks employees, vehicles, departments, and customers. Employees can "draw" a vehicle for use, but only one at a time. The user wants to track the current vehicle an employee has drawn, and the date he drew it, but isn't interested in past draws. Employees also work in departments and have names and hire dates. Employees are assigned to customers, with each employee being assigned to one or more customers and each customer having one or more employees assigned. The user also wants to know the purpose of an assignment; i.e. why the employee was assigned to a customer.

A mathematically normalized model of this situation would look like this:

Notice the three relationships. The many-to-many relationship between Employee and Customer creates the new Employee/Customer table (called by some an "associative entity"). But the 1:1 relationship between Employee and Vehicle is expressed as a relation column on the Employee entity, as is the 1:M relationship between Employees and Departments. The attributes all fit in cleanly.

This is classic normalization, but notice the one small oddity: the Draw Date attribute on the Employee table. By a simplistic interpretation of normalization rules, some would consider that misplaced, because it's the draw date of the vehicle by the employee it doesn't depend on just the Primary Key (EmpNum).

But a more subtle understanding would recognize that the Draw Date is correctly placed not because it is an attribute of Employee, but because it is an attribute of the Employee/Vehicle relationship. But where is this relationship? Recognizing that is the key to understanding how normalization theory informs business event modeling. Let's see how we could have modeled these relationships:


Back | Next