#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
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.