Data Modeling and Logical Data Design

A data model is the result of developing the logical design for a proposed system and ends up being the definition around which all applications in the system must work. Once completed, each element of the model might end up being a table definition, an XML schema, or some other form of defined implementation. Before we get to the implementation, there is a lot of work to accomplish, and attention to detail will save time and effort later.

A common basis for data modeling is the Entity Relationship Model (ER Model). Although there are other modeling techniques, ER modeling has become the favorite of most data experts.

Entity Relationship Modeling

The ER Model allows a database to be set up in a fairly simple and organized manner. The simplicity of a data model gives it the advantage of flexibility; this benefit enables you to easily create and enhance databases based on business requirements as needed. Many other model formats are also commonly used, each having similar characteristics for mapping out a potential structure. Figure 8.1 represents a partially completed ER model. Note that this ER diagram is not intended to contain the complete set of tables for a solution, nor are any of the tables necessarily complete. Errors included in this figure are discussed and corrected later. We will refer to this model as we work through this chapter's concepts.

Figure 8.1. A partial ER diagram for Billington Pharmaceuticals.

Entity structuring accommodates the initial stages of data design. When you're designing an appropriate logical model, the data must be organized into separate elements called entities that will later make up the physical tables within a database. Entities can be categorized as kernel, associative, or characteristic and are characterized by their attributes, which define the data elements (fields and columns) of an entity.

The key attribute is referred to as an entity's identifier, much as a person's name identifies that person as being an individual different from all others. Entities are usually interdependent: Each holds information that relates to other entities. These relationships can be defined by their correlated dependencies. The development of actual physical elements will undergo various changes from the point that a rough plan is put on paper.

Often, at the outset of design, the entity structure is represented by rough drawings that organize the data elements. After a developer has all the data elements, the division of elements into a logical entity structure can begin. Entities, attributes, identifiers, and dependencies are characterized as follows:

• Entity An entity is the basic division of data. In a logical design, entities are representative of the tables that will be present when data development moves into the physical design. Entities can be divided into three basic types: kernel, associative, and characteristic. Each type represents a reason for the entity existing as a separate individual, unique from all the others.

  • Kernel entity This entity type exists on its own; it doesn't define or provide descriptive information for other entities. An example of a kernel entity would be a product listing in an inventory model.

  • Associative entity This entity type is needed to allow multiple kernel entities to be tied together. In the inventory system, a sales entity would be needed to tie a customer kernel entity to the products he has purchased. This same sales entity could be tied to another kernel entity, such as salespeople.

  • Characteristic entity This entity type provides additional information for a kernel or associative entity. Information in characteristic entities can be updated independently of the related entity. A product entity could have a characteristic parts entity, for example. A given product could be made up of a number of parts, and a part that becomes unavailable could affect the product's availability. Changes over time to parts information could be made more easily if a parts entity existed, instead of your having to make changes against the product kernel entity.

• Attribute An attribute is a descriptive element or property of an entity. It is represented by fields when the logical design progresses into a physical structure. A product would have a description attribute and attributes relating its color, size, and so on.

• Identifier An identifier is a special kind of attribute. It is usually a single attribute that defines one unique element of an entity. The use of identifiers allows for the individual selection of records from an entity. As the design progresses to the physical stage, identifiers become the primary and foreign keys, allowing entities to be tied together through associations or relationships. A product's identifying attribute is usually a unique product ID.

• Dependency A dependency is a circumstance in which one entity either can't exist or has little meaning without at least one other entity in the database. When a dependency exists, it becomes a table relationship in the physical database design. There are three basic types of entity dependencies:

  • One-to-one dependency This dependency is the rarest form because each record in one entity correlates to exactly one record in the other.

  • One-to-many dependency This dependency is the most common form of relationship. One record in a primary entity has ties to many records in a secondary entity.

  • Many-to-many dependency This dependency exists when many records in one entity can relate to many records in another entity.

Modeling data into entities is simple, and a number of data modeling tools are available to assist in this design. Microsoft has the Visio product, which is an excellent design tool for a variety of purposes, including databases, programming, flowcharting, network design, and more. There are also many third-party products that can give you the tools needed to document a logical design. One of the most popular tools in a lot of business environments is ERWIN, now owned by Computer Associates.

Business Rules

A business rule is the implementation of a portion of company policies and procedures that help enforce data integrity or data correctness. A business rule is defined in the logical design as a limitation placed on the data. These rules can be implemented as a property of the data or as a product of another tier in the overall logical design of a project.

The database server can enforce an attribute so that it cannot exceed an upper limit or one that must have a value equal to one of a selection of choices. Also, data type requirements can be more strictly enforced for additional control over attributes that contain alphanumeric information but require a specific format or picture.

When you implement business rules, you can immediately take action to correct the data before it is permanently saved to the database. For example, a company can have a guideline that customers not be allowed to owe more than $1,000 or that free delivery of a product is limited to one of three states.

These types of rules can be placed at the entity or attribute level; however, it is important to note that the implementation of many of these rules can be performed in the user interface or as a separate business rules tier between the user interface and the data storage.

By defining business rules that apply to your data during the logical design phase of development, you allow for a smoother implementation. Also, as the logical design becomes further defined, other rules that were missed in the initial process will be easier to spot.

Now that you understand some concepts of designing the data model, take a closer look at the definitions provided in the following sections. You will note a definite crossover between the logical and physical design. Although these design stages are usually separate, they have been combined to more easily fit the structure of this book and better prepare you for the exam.