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
methodologyAfter 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.
Table A |
 |
Logical and physical data
models |
Implementing the logical modelIn 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.
Figure A |
 |
The relationship between different
entities |
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 flawsYour 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.