A database styleguide

Most databases seems to be well designed when the systems needing them are launched in the first version – but the slowly as the systems evolve (often grow beyond their initial requirements) – the database design seems to go down the drain. Here are a few tips on what to think about when you design a database or alter it.

First rule: Remember the goal of the system.

Different systems have different requirements and thus require different database designs. Some system are build for the web, some are build for medical journals and others just to solve a practical problem. If you’re building websystems, your main concern is probably speed. If you’re building financial system, data integrity and consistency is probably your main concern. Make sure your database design is made for the goal.

Second rule: Know your database

Various databases have different strengths and weaknesses. Make sure you know them. SQL is a nice common base shared by many databases, but most databases offers extensions which you should exploit if it makes your system perform better (as applied to the goals set by the first rule above). Read performance guides on how the databases handles different column types and choose those how offer the best match to your needs for every column in every table.

Third rule: Choose a language

If English isn’t your first language most people will probably make a mess of their native language and English – in table names, column names, stored procedures and so on – choose which language the database is designed in and stick to it. Consistency is nice.

Fourth rule: Be consistent

Try to follow a consistent pattern across your entire database. Here are a few suggestions:

• Primary keys in a table are always named id.
• When used as a foreign key columns are always named tablename underscore id.
• Reuse the table and column names in your systems datastructures, which utilize data from the database.
• Use the same name in all tables for the same foreign key (or relations).

Fifth rule: Annotate your database

It’s quite helpful to have a graphical illustration of the database. Make it and keep it safe and make annotations which remind you how your database works – and why you did it the way you did.

There are probably plenty other rules which can help you to a healthy database design, but sticking to the few above pragmatic rules should help you go a long way. If you best tips isn’t one of the above – leave a comment and share it with the rest of us.