Physical Design   «Prev  Next»

Lesson 2Common relational database design mistakes
ObjectiveList the four areas of common database design mistakes.

Common Database Design Mistakes

Every company and organization is different. Designing a relational database requires careful research and attention to detail to ensure your design meets your client's needs and performs efficiently.
Database Design Considerations
Despite the differences among particular databases, there are a number of common types of mistakes you can make while designing a database. These occur most often in one of four areas:
  1. Business objects and rules
  2. Constraints, columns, and keys
  3. Relationships and referential integrity
  4. International issues

In the following lessons, we will take a closer look at these areas and at the mistakes most common to each.

Poor Naming Standards

In a sense, naming standards are part of documentation. When done properly, an object's name should give you a lot of information about the object. For example, if I tell you to build an Employees table, you probably know a lot about that table without even being told. You know that it will need name, address, phone, email, and Social Security Number information (in the United States, at least). In most companies, it will also need an employee ID, hire date, title, department, salary, and payroll information (deductions, bank account for automatic deposit, and so forth). Somehow it should probably link to a manager and possibly to projects. You got all of that from the single word "Employees."
Now suppose I told you to build a People table but I really want to use the table to hold employee data. You would probably only put about half of the necessary fields in this table. You would get the name and address stuff right, but you would completely miss the business-related fields.
The problems become worse when you start working with multiple related tables and fields. For example, suppose you use employee IDs to link a bunch of tables together but one table calls the linking field EmpNo, another calls it EmployeeId, and a third calls it Purchaser.
This may seem like a small inconvenience and in isolation it is, but together a lot of little inconveniences can add up to a real headache. Inconsistent naming makes developers think harder about names than the things they represent and that makes them less productive and less accurate.

Poor Naming Conventions

I have worked on projects where poor naming conventions made small changes take days instead of hours because developers had to jump back and forth through the code to figure out what was happening. Inconsistent naming by itself is unlikely to sink a project, but it is enough to nudge an already leaky ship toward the rocky shoals.
Write down names for fields that will be used in more than one table and stick to them so the same concept gets the same name everywhere. Then use those names consistently. Consistency is more important than following particular arcane formulae to generate names, although I will mention two useful conventions for naming database objects such as tables and fields.
First, do not use keywords such as TABLE, DROP, and INDEX. Though these may make sense for your application and they may be allowed by the database, they can make programming confusing. If one of these words really fits well for your project, try adding something to make it even more descriptive. For example, if your database will hold seating assignments and it really makes sense to have a field named Table, try naming it TableNumber or AssignedTable instead.
Second, do not put special characters such as spaces in table or field names even if the database allows it. Although there are ways to use these sorts of names, it makes working with the database a lot more confusing and remember, the point of good naming conventions is to reduce confusion.
Picking good names for tables is like a vocabulary test. You need to think of a word or short phrase that sums up as many of the features in the related items as possible so someone else who looks at your table’s name will immediately understand the characteristics that you’re trying to record. The following table shows some examples.

Examples of Good Naming Converntions
Examples of Good Naming Converntions

The next lesson describes mistakes associated with business objects and rules.
Ad Database Systems