Neophyte database designers (and professionals, on occasion) are prone to several common mistakes. Here's a checklist to ensure that you haven't made any of the most common database design errors.
Spreadsheet design. If you need a spreadsheet, use a spreadsheet. Your database shouldn't be a single table of all sorts of business data, especially if it included a number of calculated values. Combining different types of data in one table defeats the whole purpose of using a relational database. And while storing calculated values can speed up query and report performance, databases generally calculate values on the fly so the data is always as current as possible.
Too much data. The goal of a successfully designed database is to provide all of the information necessary for making decisions based on the data. There is an overwhelming desire, especially in neophytes, to encapsulate every possible nugget of data in the database. Too many fields in an entry form guarantee users will lose interest in filling in the data or will increase the amount of time to fill out the form. Plus this information increases the overall storage requirements for the database. Proper research can help identify what is essential, what might be useful in the future, and what is irrelevant.
Compound fields. Fields containing multiple discrete pieces of data lead to problems in searching, alphabetizing, and calculating those fields. It's much harder to get a report of customers by ZIP code when that value is buried in a field with the address, city, and state. If it's a distinct piece of data, make it a distinct field.
Missing keys. Every table needs some sort of key to identify individual records. Most database packages alert you if you leave one out during the design process, but if you use your own system (such as a flat file system), make sure there is a distinct and unique key for each record in each table.
Bad keys. A key has to be unique for each record. Existing database fields may appear to be good candidates for keys, but it is usually best to create an artificial key that is guaranteed to be unique. Phone numbers seem like a great key for personnel records until you run into people who live in the same home or who have multiple phone lines.
Missing relations. If two tables are supposed to be related, there must be a field that relates the two databases. A well-designed table relationship is useless if the appropriate foreign keys are not added to the related tables (or if the linking table for a many-to-many relationship is not created).
Unnecessary relationships. Just because every table can be linked to every other table does not mean that they have to be related. There is a temptation to relate tables that are logically unrelated just because you can.
Incorrect relations. Creating relationships between tables does not require changes in each table. A one to many relationship requires the primary key from the "one" table to be inserted as a foreign key in the "many" table. It does not need a foreign key placed in the "one" table since the relationship is already established in the many table. In fact, this arrangement may yield incorrect query results.
Duplicate field names. DBMS products prevent duplicate field names in a single table, but do not prevent duplicate names in different tables. While there is no programmatic reason to follow this practice, it becomes very difficult for humans to keep track of 15 relational tables where the primary key in each is called ID. It is much easier to write and debug queries if each field name is unique in the entire database.
Cryptic field and table names. Even more frustrating than duplicate names are cryptic names. There is no reason to limit the length of a field or table name, so use as descriptive a name as possible. Writing queries and debugging are much easier when the focus is the logic and not what T1C1x means.
Missing or incorrect business rules. Many businesses have strict rules that have nothing to do with program or database logic. Do not neglect these rules. The old adage of "garbage in, garbage out" applies since decisions made on incorrectly entered data may lead to erroneous query results and reporting.
Missing or incorrect constraints. A very easy way to ensure that data are entered correctly is to use constraints. These can be implemented as checks to see if an entered value is within an approved list or range of choices. Constraints can also be implemented as masks that require phone numbers or ZIP codes to fit a specified format.
Referential integrity. Data records that participate in relationships need to be checked when they are created or deleted to ensure that they are not orphans. Deleting one record usually requires the deletion of that record in linked tables. Ensuring referential integrity involves making sure that table declarations ensure the existence of the appropriate relationships and that integrity checks are triggered when records are deleted.
Database security. Almost all databases have methods to control access and user rights. For instance, end users of an invoice system probably should not have permission to create new tables or delete existing tables. Use the available security features to prevent unauthorized access and control permissions of various users and classes of users.
International issues. As business becomes more global, keep in mind that there are a number of formats for business data other than those of the United States. Most databases understand the various European and American date, currency, and address formats so think about whether your application will need to understand those as well.
Next Month: SQL, the Esperanto of Databases
Next month, I'll go through this process to build two different databases for Web applications that we'll use for some of our future application-building activities. In the following column, I'll discuss the final bit of technology needed to use most Web database applications: Structured Query Language (SQL), the Esperanto of databases.
From there, I'll review specific tools that are used to develop Web database applications.
I'd also like the thank the nearly 100 folks who commented on the first article. I appreciate all the suggestions, criticism, and compliments. I learned about all the databases and application servers I left out and now have a pretty good sense of the reader base for the column. Look for more on the Cold Fusion, the ZOPE python-based Web application server, ASP, and quick-and-dirty Perl/flat-file information in future issues.
