Physical Design   «Prev  Next»

Lesson 1

Common Database Design Mistakes

The previous modules in this course described the logical design, physical design, and, to a lesser extent, implementation stages of the DBLC. This module offers a number of insights into the overall design process by identifying common design mistakes and providing tips on how to avoid them.

Learning objectives

After you have completed this module, you should be able to:
  1. List the four areas of common design mistakes
  2. Describe mistakes associated with business objects[1] and business rules[2]
  3. Describe mistakes associated with columns[3]
  4. Describe mistakes associated with constraints and keys
  5. Describe mistakes associated with relationships[4] and referential integrity[5]
  6. Describe mistakes associated with international issues[6]
  7. Interpret the statement: "There are no wrong databases, just useless ones."

Lack of Preparation

Database design is often one of the first steps in development. It is only natural for developers to want to rush ahead and get some serious coding done. That gives them something to show management to prove that they are developing software. Writing application software that maps to the ERD models is more fun than working on plans, designs, use cases, documentation, and all the other things that you need to do before you can roll up your sleeves and get to work.
Before you start cranking out tables and code, you need to do your homework. Some of the things you need to do before you start wiring up the database include:
  1. Make sure you understand the problem.
  2. Write requirements documents to state the problem.
  3. Build use cases to see if you have solved the problem.
  4. Design a solution.
  5. Test the design to see if it satisfies the use cases.
  6. Document everything.

Poor Documentation

This is part of preparation but is so important and under-appreciated that deserves its own section. Many developers think of documentation as busy work or a chore to keep managers who have no real talents off their backs while they build elegant data structures of intricate beauty and complexity. I confess that occasionally that’s a handy attitude, but the real purpose of documentation is to keep everyone on the project focused on the same goals. The documentation should tell people where the project is headed. It should spell out the project’s design decisions so everyone knows how the pieces will fit together. If the documentation is weak, different people will make different and often contradicting assumptions. Eventually those assumptions will collide and you’ll have to resolve the conflict. That will require developers to go back and fix work that they made under the wrong assumptions. That leads to more work, more errors, and copious bickering over whose fault it was. The real fault was poor documentation.
The next lesson lists the four areas of common design mistakes.

[1]Business objects: Items in a business environment that are related, and about which data need to be stored (e.g., customers, products, orders, etc.)
[2]Business rules: A set of rules or conditions describing the business polices that apply to the data stored on a company databases.
[3]column: Part of the structure of a database table; also known as a field or, during early database design, as an attribute.
[4]relationship: If the same attribute occurs in more than one table, a relationship exists between those two tables.
[5]Referential integrity: The means of maintaining the integrity of data between one or more tables that relate to each other--in other words, that a column of data in a table has a null or matching value in a corresponding table. Referential integrity is usually enforced with foreign keys.
[6] international issues: Database design considerations based on other countries differing data standards, alphabets, and writing systems.

Ad Database Design and Theory