What are the common mistakes of database design to avoid?

#1 Default settings

Even though many of us use default settings while creating a database with our tools, they could often cause problems. Those problems can be operational problems or

pros and cons of database design

 performance problems as well.

#2 Primary key abuse

The worst database development mistake is developers who have no idea how a primary key should be used. A primary key value should have nothing to do with the data in the row. Do not use application data for primary keys. Do not use concatenations or calculations of application data for primary keys. Do not use values that have any meaning for primary keys. Primary keys should be sequentially or randomly generated by the database upon row insertion and never changed except under the most unusual of circumstances.

#3 Not having primary key

You should strive to have a primary key in any non-trivial table where you’re likely to want to access (or update or delete) individual records by that key. Primary keys can consist of multiple columns, and formally speaking, will be the shortest available superkey; that is, the shortest available group of columns which, together, uniquely identify any row.

#4 Not enough indexes

Obviously, indexes are a central concept to databases and database performance. But horror tales still abound of naive developers who “forget” to index their tables, and encounter massive performance and scalability problems down the road as their tables grow. However, keep in mind that you shouldn’t have too many or too little indexes since the number of indexes on a table is the most dominant factor for insert performance. The more indexes a table has, the slower the execution becomes.

#5 Hard deletes

There are times when a hard delete of data makes sense, but in my experience, those times are much less common than the number of times you will be restoring the database to a separate server or trolling through transaction logs in order to retrieve data that was deleted by mistake, or to audit a problem with the application.

#6 Failing to plan for data purging

If you never plan to remove data from your tables then they will grow and grow and grow and become unmanageble eventually.

#7 Stored procedure overuse

Stored procedures are useful, but for most modern applications (not all), using them as often as was done in the past is overkill. Modern ORMs and other techniques make stored procedures a lot less necessary, and are much more nimble in the development process. More importantly, stored procedures are a maintenance disaster. The next time you consider writing a stored procedure, make sure it is truly justified.

#8 Not using normalization

We often build ad-hoc databases without any attempt at normalization; that’s fine, as long as database is, and remains, small and simple.

#9 Not looking at real data

We often base database design on our imagined, ideal model, without having a look at real data. Then we get all sorts of troubles with (not) nulls, constraints… worst trouble being wrong cardinality (i.e. mistaking N:N for 1:N).

#10 Too much normalization

Sometimes we normalize too much. Yep that can happen too. Then we only get more complex queries, and add more burden to database server, without saving any important storage space.

Leave a Reply

Your email address will not be published. Required fields are marked *