Tips for Designing a Better SQL Database

Keeping the following subjects in mind helps ensure that your database design and updates go smoothly.

Don’t design a database that copes with the norm. Tempting as it is to design a database that covers most situations, doing so is dangerous. The unexpected happens just when you least expect it, so make sure you design your database to cover all situations that could arise, or at least ensure that it can cope with the unusual situations. Even if a client tells you not to worry about the possibility of two people attempting to reserve the same holiday cottage at the same time, assume that it will happen.

Choose meaningful names for tables and fields. Try to use field and table names that help give an idea of what data they store. For example, the MemberDetails table stores members’ details, which makes it fairly obvious without further explanation what the table holds. Name tables so that further explanation or looking into the table is unnecessary. The same applies to column names.

Try to keep names simple. Maybe this seems to contradict the previous point, but it doesn’t: Names should be as descriptive as possible, but they shouldn’t be overly long or complex. Long names increase the likelihood of errors.

Be consistent in your naming and choice of data type. To prevent confusion, don’t call a field ZipCode in one table and PostalCode in another if they refer to the same data. Also make sure that both fields are the same data type and can store the same width of data. If you define one as varchar(12) in one table and varchar(8) in another, you risk truncation if you ever insert from one table into another.

Analyze your data needs on paper first. It’s very tempting when asked to create a database to rush off and start designing on the fly, as it were. However, take time out first to sit down with pen and paper and consider what data needs to be stored and, most importantly, what answers the database is expected to supply. If the person needing the database already operates some other system (for example, a paper-based storage system), take a look at that and use it as your starting point for the data input.

Pick your primary key carefully. Choose a field that is unlikely to change and preferably one that is a whole-number-based field. The primary key must always be unique. If no field is obvious, then create your own whole-number field for the purpose of creating a unique primary key.

Create an index. Indexes help speed up searches, so adding them to fields that are regularly used in searches or joins is worthwhile. Indexes are especially worthwhile where you have lots of different values—for example, the ZipCode field in the MemberDetails table. Including an index is not a good idea, however, if you have only a few values, such as the MemberAttended column in the Attendance table. Indexes also slow down data entry, something particularly important to note if the column is likely to have lots of inserts or updates.

Add a multicolumn index. Multicolumn indexes come in particularly handy in fields where users often search more than one column. For example, if you often search for City and State together, add an index based on both columns.

Avoid using reserved words as table or field names. Reserved words are words used by the SQL language and are therefore reserved for its use only. For example, words such as select, join, and inner are exclusive to SQL. Although you can sometimes use reserved words by putting square brackets around them, avoiding them altogether is easier.

Consider storage space requirements. When selecting a field’s data type, allow for the maximum storage space likely to be required, and then add a little bit! If you think the greatest number of characters to be stored is probably 8, make your definition 10, or varchar(10). Doing so adds a little bit of a safety net. The same goes with numbers.