How to implement a logical data model

In the development business, there’s no excuse for a bad design. Unfortunately, you probably know developers who skip through or completely ignore the design process because they don’t understand the steps. Few of us are so smart or skilled that we can bypass the most important stage of a project’s development and brag about it.
Implementing an efficient and trouble-free design is close to impossible if you don't take the time to actually create a logical model. Skipping this step leads to errors that take valuable time to debug and correct when you finally discover them. In this article, part one, we’ll briefly discuss the logical and physical models and then work through the process of creating a logical model. In the next installment, part two, we’ll alter our initial design based on the flaws we discover.
Database design methodology
After assessing and analyzing the requirements for a database project, the next step is to devise a plan that will help you meet the project’s requirements and goals. In the development world, this is known as database design methodology. It’s a structured approach that supports the design process and includes a number of tools, such as company procedures, conventions, and documentation. A step-by-step process gets the job done efficiently by helping you plan, manage, and control design and implementation.
What this means is that you have a set method—a list of items in a specific order—that will guide you through the process of creating a data model. Don’t make the mistake of viewing this process as mundane and unnecessary. Explore the process with the goal of fully understanding the data and the needs of the users who will be using that data.
Each project, no matter how small, benefits from the following three models:

• Conceptual: Identify and describe the main objects to create a global view of the data, with little detail. Many companies are restricted to a specific database management system (DBMS), and this step is skipped or combined with the logical model.
• Logical: Construct a model of the data using a specific model, but without consideration for the actual database system that will eventually store the data and run the application. Since SQL Server is a relational database management system (RDBMS), we’ll rely on the Entity-Relationship (ER) model. During this phase you’ll identify entities, relationships, and attributes and normalize your data. The logical model is built on sets of data. For a more in-depth explanation of the ER model, visit the ITS Database Services Web site or the Mapping an ER Model to the Relational Model Web site (a .PDF file).
• Physical: Design a model for implementing the logical model that considers the RDBMS to be used. During this phase, you’ll describe tables, indexes, and so on. The physical model is built on tables.

The real purpose of building a logical model is to confirm that the application will satisfy its requirements—both input and output. In other words, the logical model must be able to produce all known reports, queries, and so on. In addition, the user must be able to enter and manipulate data in a reasonable fashion. Once the logical model is in place, you can start applying what you’ve learned to the project’s physical requirements, i.e. the physical model. Table A should help you work through the differences in the logical and physical models at this stage.

Implementing the logical model
In the current context, “implementing” really means working through the logical model’s components. By identifying identities, relationships, and attributes, you’ll reveal flaws that could produce anomalies in a working application:

• Missing entities
• Multiple entities that represent the same conceptual entity
• Many-to-many relationships that need additional entities to resolve
• Multivalued and redundant attributes

Now let’s work with an actual project and see just how creating a logical model can help you avoid later problems. Suppose your newest project is a simple, order-processing database application for a travel agency that sells both wholesale and retail to four types of clients:

• Agency: Another travel agency that’s entitled to a commission on the order.
• Aggregator: A club whose members receive a discount on the order.
• Corporate: Companies that place orders on behalf of their employees. They receive no discount but require full support from the agency. For instance, the agency must assist in resolving problems such as cancelled and overbooked flights. The corporate client is always the same, although the traveler can be any employee.
• Retail: Individual clients that aren’t entitled to any kind of discount.

At this point, you’re ready to define the application’s main objects, or entities. In order to accommodate the above business rules for client types, you might consider each client type as a separate entity, as listed in Table B. The data types and other information are specific to SQL Server.
Table B

Entity Attribute Description Data Type Key Relationships Multi-plicity AgencyClients ClientID Uniquely identifies each client System-generated auto-number Primary key Orders: Each client has zero, one, or many orders 1..* ClientName Client's name Text CommissionRate Percentage commission due to agent client Numeric StreetAddress Client's street address Text City Client's city Text State Client's state Text ZIPCode Client's ZIP code Text Telephone Client's telephone number Text AggregatorClients ClientID Uniquely identifies each client System-generated auto-number Primary key Orders: Each client has zero, one, or many orders 1..* ClientName Client's name Text AggregatorDiscount Percentage discount due aggregator client Numeric StreetAddress Client's street address Text City Client's city Text State Client's state Text ZIPCode Client's ZIP code Text Telephone Client's telephone number Text CorporateClients            ClientID Uniquely identifies each client System-generated auto-number Primary key             Orders: Each client has zero, one, or many orders. CorporateTravelers: Each corporate client may have many travelers 1..*             ClientName Client's name Text StreetAddress Client's street address Text City Client's city Text State Client's state Text ZIPCode Client's ZIP code Text Telephone Client's telephone number Text RetailClients            ClientID Uniquely identifies each client System-generated auto-number Primary key            Orders: Each client has zero, one, or many orders             1..*            ClientName Client's name Text StreetAddress Client's street address Text City Client's city Text State Client's state Text ZIPCode Client's ZIP code Text Telephone Client's telephone number Text CorporateTravelers    TravelerID Uniquely identifies corporate travelers System-generated auto-number Primary key         TravelerName Traveler's name Text   ClientID ClientID value from CorporateClients that relates the traveler to the corporate client Numeric Foreign key CorporateClients: Each traveler belongs to one corporate client *..1 Orders OrderID Uniquely identifies each order System-generated, auto-number Primary key       OrderDate Identifies the order by date Date/time         ClientID ClientID value from any of the four client tables that relates the order to a client Numeric Foreign key AgencyClients, AggregatorClients, CorporateClients, RetailClients: Each order belongs to one client

Defining application entities
Viewing the illustration in Figure A, you can reduce the current model to simple statements:

• Clients have orders.
• One particular type of client, the corporate client, identifies individual travelers.

As mentioned earlier, business rules require that we treat clients differently. Consequently, clients don’t always share the same attributes. Our first solution is to create a table that contains attributes specific to each client for each client type. This initial design introduces the potential for a few problems:

• All the client tables use system-generated primary keys, which presumably will begin with a seed value of 1. That means you’ll end up with duplicate ClientID values. Consequently, there’s no way to properly relate each order to its specific client since each client table will contain duplicate values.
• There are a number of redundant attributes as each client table repeats common fields: ClientID, ClientName, Address, and Telephone.
• Will clients have more than one address? Perhaps they’ll have a local address and a corporate address for billing.
• Will clients have only one telephone number? Perhaps you’ll want to list multiple telephone numbers or even a fax number.
• Will it be necessary to identify an order by the originating client’s type? As is, you can’t.

Next: Revealing and resolving design flaws
Your first try might be very different from ours, but that’s not a critical issue. What’s important is that you might not recognize flaws in your design—flaws like the ones spelled out above. In our next installment, part two, we’ll apply a known and proven methodology to reveal and resolve these and other design flaws before they can work their way into your application.