What are common database development mistakes made by application developers?

+8  A: 

Forgetting to set up relationships between the tables. I remember having to clean this up when I first started working at my current employer.

+4  A: 

a) Hardcoding query values in string
b) Putting the database query code in the "OnButtonPress" action in a Windows Forms application

I have seen both.

"Putting the DB query code in the "OnButtonPress" action in a Windows Form application" What's the database mistake here?
@recursive: it's a huge SQL injection vulnerability. Anyone can send arbitrary SQL to your server and it will be run verbatim.
Bill Karwin
Agreed with @recursive. These really have nothing to do with DB issues.
b) is an architecture mistake. Of course, coding queries directly in your app is a bad idea, anyway.
David Lively
+451  A: 

1. Not using appropriate indexes

This is a relatively easy one but still it happens all the time. Foreign keys should have indexes on them. If you're using a field in a WHERE you should (probably) have an index on it. Such indexes should often cover multiple columns based on the queries you need to execute.

2. Not enforcing referential integrity

Your database may vary here but if your database supports referential integrity--meaning that all foreign keys are guaranteed to point to an entity that exists--you should be using it.

It's quite common to see this failure on MySQL databases. I don't believe MyISAM supports it. InnoDB does. You'll find people who are using MyISAM or those that are using InnoDB but aren't using it anyway.

More here:

3. Using natural rather than surrogate (technical) primary keys

Natural keys are keys based on externally meaningful data that is (ostensibly) unique. Common examples are product codes, two-letter state codes (US), social security numbers and so on. Surrogate or technical primary keys are those that have absolutely no meaning outside the system. They are invented purely for identifying the entity and are typically auto-incrementing fields (SQL Server, MySQL, others) or sequences (most notably Oracle).

In my opinion you should always use surrogate keys. This issue has come up in these questions:

This is a somewhat controversial topic on which you won't get universal agreement. While you may find some people, who think natural keys are in some situations OK, you won't find any criticism of surrogate keys other than being arguably unnecessary. That's quite a small downside if you ask me.

Remember, even countries can cease to exist (for example, Yugoslavia).

4. Writing queries that require DISTINCT to work

You often see this in ORM-generated queries. Look at the log output from Hibernate and you'll see all the queries begin with:


This is a bit of a shortcut to ensuring you don't return duplicate rows and thus get duplicate objects. You'll sometimes see people doing this as well. If you see it too much it's a real red flag. Not that DISTINCT is bad or doesn't have valid applications. It does (on both counts) but it's not a surrogate or a stopgap for writing correct queries.

From Why I Hate DISTINCT:

Where things start to go sour in my opinion is when a developer is building substantial query, joining tables together, and all of a sudden he realizes that it looks like he is getting duplicate (or even more) rows and his immediate response...his "solution" to this "problem" is to throw on the DISTINCT keyword and POOF all his troubles go away.

5. Favouring aggregation over joins

Another common mistake by database application developers is to not realize how much more expensive aggregation (ie the GROUP BY clause) can be compared to joins.

To give you an idea of how widespread this is, I've written on this topic several times here and been downvoted a lot for it. For example:

From SQL statement - “join” vs “group by and having”:

First query:

SELECT userid
FROM userrole
WHERE roleid IN (1, 2, 3)
GROUP by userid

Query time: 0.312 s

Second query:

SELECT t1.userid
FROM userrole t1
JOIN userrole t2 ON t1.userid = t2.userid AND t2.roleid = 2
JOIN userrole t3 ON t2.userid = t3.userid AND t3.roleid = 3
AND t1.roleid = 1

Query time: 0.016 s

That's right. The join version I proposed is twenty times faster than the aggregate version.

6. Not simplifying complex queries through views

Not all database vendors support views but for those that do, they can greatly simplify queries if used judiciously. For example, on one project I used a generic Party model for CRM. This is an extremely powerful and flexible modelling technique but can lead to many joins. In this model there were:

  • Party: people and organisations;
  • Party Role: things those parties did, for example Employer and Employer;
  • Party Role Relationship: how those roles related to each other.


  • Ted is a Person, being a subtype of Party;
  • Ted has many roles, one of which is Employee;
  • Intel is an organisation, being a subtype of a Party;
  • Intel has many roles, one of which is Employer;
  • Intel employs Ted, meaning there is a relationship between their respective roles.

So there are five tables joined to link Ted to his employer. You assume all employees are Persons (not organisations) and provide this helper view:

CREATE VIEW vw_employee AS
SELECT p.title, p.given_names, p.surname, p.date_of_birth, p2.party_name employer_name
FROM person p
JOIN party py ON =
JOIN party_role child ON = child.party_id
JOIN party_role_relationship prr ON = prr.child_id AND prr.type = 'EMPLOYMENT'
JOIN party_role parent ON = prr.parent_id =
JOIN party p2 ON parent.party_id =

And suddenly you have a very simple view of the data you want but on a highly flexible data model.

7. Not sanitizing input

This is a huge one. Now I like PHP but if you don't know what you're doing it's really easy to create sites vulnerable to attack. Nothing sums it up better than the story of little Bobby Tables.

Data provided by the user by way of URLs, form data and cookies should always be treated as hostile and sanitized. Make sure you're getting what you expect.

8. Not using prepared statements

Prepared statements are when you compile a query minus the data used in inserts, updates and WHERE clauses and then supply that later. For example:

SELECT * FROM users WHERE username = 'bob'


SELECT * FROM users WHERE username = ?


SELECT * FROM users WHERE username = :username

depending on your platform.

I've seen databases brought to their knees by doing this. Basically, each time any modern database encounters a new query it has to compile it. If it encounters a query it's seen before, you're giving the database the opportunity to cache the compiled query and the execution plan. By doing the query a lot you're giving the database the opportunity to figure that out and optimize accordingly (for example, by pinning the compiled query in memory).

Using prepared statements will also give you meaningful statistics about how often certain queries are used.

Prepared statements will also better protect you against SQL injection attacks.

9. Not normalizing enough

Database normalization is basically the process of optimizing database design or how you organize your data into tables.

Just this week I ran across some code where someone had imploded an array and inserted it into a single field in a database. Normalizing that would be to treat element of that array as a separate row in a child table (ie a one-to-many relationship).

This also came up in Best method for storing a list of user IDs:

I've seen in other systems that the list is stored in a serialized PHP array.

But lack of normalization comes in many forms.


10. Normalizing too much

This may seem like a contradiction to the previous point but normalization, like many things, is a tool. It is a means to an end and not an end in and of itself. I think many developers forget this and start treating a "means" as an "end". Unit testing is a prime example of this.

I once worked on a system that had a huge hierarchy for clients that went something like:

Licensee ->  Dealer Group -> Company -> Practice -> ...

such that you had to join about 11 tables together before you could get any meaningful data. It was a good example of normalization taken too far.

More to the point, careful and considered denormalization can have huge performance benefits but you have to be really careful when doing this.


11. Using exclusive arcs

An exclusive arc is a common mistake where a table is created with two or more foreign keys where one and only one of them can be non-null. Big mistake. For one thing it becomes that much harder to maintain data integrity. After all, even with referential integrity, nothing is preventing two or more of these foreign keys from being set (complex check constraints notwithstanding).

From A Practical Guide to Relational Database Design:

We have strongly advised against exclusive arc construction wherever possible, for the good reason that they can be awkward to write code and pose more maintenance difficulties.

11. Not doing performance analysis on queries at all

Pragmatism reigns supreme, particularly in the database world. If you're sticking to principles to the point that they've become a dogma then you've quite probably made mistakes. Take the example of the aggregate queries from above. The aggregate version might look "nice" but its performance is woeful. A performance comparison should've ended the debate (but it didn't) but more to the point: spouting such ill-informed views in the first place is ignorant, even dangerous.

12. Over-reliance on UNION ALL and particularly UNION constructs

A UNION in SQL terms merely concatenates congruent data sets, meaning they have the same type and number of columns. The difference between them is that UNION ALL is a simple concatenation and should be preferred wherever possible whereas a UNION will implicitly do a DISTINCT to remove duplicate tuples.

UNIONs, like DISTINCT, have their place. There are valid applications. But if you find yourself doing a lot of them, particularly in subqueries, then you're probably doing something wrong. That might be a case of poor query construction or a poorly designed data model forcing you to do such things.

UNIONs, particularly when used in joins or dependent subqueries, can cripple a database. Try to avoid them whenever possible.

13. Using OR conditions in queries

This might seem harmless. After all, ANDs are OK. OR should be OK too right? Wrong. Basically an AND condition restricts the data set whereas an OR condition grows it but not in a way that lends itself to optimisation. Particularly when the different OR conditions might intersect thus forcing the optimizer to effectively to a DISTINCT operation on the result.


... WHERE a = 2 OR a = 5 OR a = 11


... WHERE a IN (2, 5, 11)

Now your SQL optimizer may effectively turn the first query into the second. But it might not. Just don't do it.

14. Not designing their data model to lend itself to high-performing solutions

This is a hard point to quantify. It is typically observed by its effect. If you find yourself writing gnarly queries for relatively simple tasks or that queries for finding out relatively straightforward information are not efficient, then you probably have a poor data model.

In some ways this point summarizes all the earlier ones but it's more of a cautionary tale that doing things like query optimisation is often done first when it should be done second. First and foremost you should ensure you have a good data model before trying to optimize the performance. As Knuth said:

Premature optimization is the root of all evil

Good points. Can somebody elaborate on the points?
On the MySQL statements about foreign keys, you're right that MyISAM doesn't support them, but you imply that merely using MyISAM is bad design. A reason I've used MyISAM is that InnoDB doesn't support FullText searches, and I don't think that's unreasonable.
Great Answer I wish could give you more than 1 up vote
Charles Faiga
I don't mean to imply that MyISAM is bad, just that you don't have that option if you're using MyISAM.
Very good answer +1 (should have been +5)
This kind of answer is what makes SO such a great place. +1.
Dan Vinton
Do not use Social Security Numbers as keys ! (Unless you are the Social Security Administration) Yes they are unique however they CAN change and they can be "reused".
Wasn't it dijkstra who said that premature optimization is the root of all evil ?
Frederik Gheysels
Agree except #4 -Let me borrow a response to 'Why I hate DISTINCT': Developers have be so ass whipped by management and sales people deciding on how the code should be written and how long it should take to write, a 'distinct' can be a great friend. A developer doesn't care that some over paid marketing head bought a junk list of data with tons of dupes. He just wants to load it and get on with his life. Developers are just trying to keep their hours under 60 a week and occasionally see sunlight
I have to ask about #6. Using views like this is one of my favorite things to do, but I recently learned, to my horror, that with MySQL indexes on the underlying tables are only obeyed if the structure of the view allows use of the merge algorithm. Otherwise, a temp table is used and all your indexes are useless. It's even more alarming when you realize that a bunch of operations cause this behavior. It's a great way to turn a .01 sec query into to a 100 second one. Does anyone else here have experience with this? Check the links in my next comment.
Peter Bailey
MySQL View algos MySQL View Performance
Peter Bailey
Great answer. Very helpful for starting developers. Thanks
Fábio Antunes
You're missing one important item here, Not using transactions. I've seen so many cases of insert/updates that needs to be wrapped in a transaction - the only thing keeping the system from breaking horribly is there hasn't been concurrent updates to those records - Yet. (or noone have yet noticed the broken data)
@Frederik Gheysels: for info on the premature optimization quote. The origin of the quote is not totally clear, but seems likely to have been first used by Knuth.
Bill Karwin
Odd, I've used Hibernate a lot and it doesn't routinely add "distinct" on the front of queries. I wouldn't use it if it did!
Shouldn't this be 14 separate answers so they can be votedd on effectively?
I think this post needs to be normalized..
James Jones
Better Quality Answer than most Wikipedia articles.
Warren P
WOW! Great points!
Completely disagree with #3. Yes, countries can cease to exist, but the country code will continue to represent the same thing. Same with currency codes or US States. It is dumb to use a surrogate key in these cases and creates more overhead in your queries as you must include an extra join. I would say that it is safer to say that you *probably* ought to use a surrogate for user-specific data (thus, not countries, currencies and US States).
RE: #11 The check constraint needed to enforce data integrity is trivial. There are other reasons for avoiding that design, but the need for "complex" check constraint isn't one of them.
i think that using natural keys could leat to less optimal - sparse - internal database index structure, while autoincremented index would be compact. please correct me, if i'm wrong
With oracle (10.2) it can be better to actually not use a prepared statement for some queries. Consider hardcoding the argument values if the query is heavy (over a minute execution time) and realtively rare (once a day).
John Nilsson
+20  A: 

Not using indexes.

Christophe Herreman
Nice one. Sadly it's more like "not even knowing that indexes exist".
+1  A: 
  • Very large transactions, inserting/updating a lot of data and then reloading it. Basically this is down to not considering the multi-user environment the database works in.

  • Overuse of functions, specifically as results in selects and in where clauses which causes the function to be called over and over again for the results. This, I think, fits under the general case of them trying to work in the procedural fashion they're more used to rather than use SQL to its full advantage.

+5  A: 

Not doing the correct level of normalization. You want to make sure that data is not duplicated, and that you are splitting data into different as needed. You also need to make sure you are not following normalization too far as that will hurt performance.

Nathan Voxland
How far is too far? If no data is duplicated how can you take it further?
Normalization is a balance of removing redundant data and increasing flexibility vs decreased performance and increased complexity. Finding the correct balance takes experience and it changes over time. See for information on when to denormalize
Nathan Voxland
+9  A: 

Using Access instead of a "real" database. There are plenty of great small and even free databases like SQL Express, MySQL, and SQLite that will work and scale much better. Apps often need to scale in unexpected ways.

Nathan Voxland
+13  A: 

In my experience:
Not communicating with experienced DBAs.


Trusting a DBA to do even the simplest task properly.

Im not a dba but hands down -1 for this remark. DBAs are assets not liabilities. Good dba's that is.
DBA means database administrator, not database architect. Saying that a good DBA can do good database design is like saying a good system administrator can do good software. Some really can, I don't object. But usually people predisposed to administering are not that good in design and development, and vice versa. +1
+1  A: 

Blaming the db engine when the query that ran sooo fast on your development machine blows up and choke once you throw some traffic at the application.

+42  A: 
  1. Not using version control on the database schema
  2. Working directly against a live database
  3. Not reading up and understanding more advanced database concepts (indexes, clustered indexes, constraints, materialized views, etc)
  4. Failing to test for scalability ... test data of only 3 or 4 rows will never give you the real picture of real live performance
+17  A: 

Number one problem? They only test on toy databases. So they have no idea that their SQL will crawl when the database gets big, and someone has to come along and fix it later (that sound you can hear is my teeth grinding).

Bob Moore
+18  A: 

Over-use and/or dependence on stored procedures.

Some application developers see stored procedures as a direct extension of middle tier/front end code. This appears to be a common trait in Microsoft stack developers, (I'm one, but I've grown out of it) and produces many stored procedures that perform complex business logic and workflow processing. This is much better done elsewhere.

Stored procedures are useful where it has actuallly been proven that some real technical factor necessitates their use (for example, performance and security) For example, keeping aggregation/filtering of large data sets "close to the data".

I recently had to help maintain and enhance a large Delphi desktop application of which 70% of the business logic and rules were implemented in 1400 SQL Server stored procedures (the remainder in UI event handlers). This was a nightmare, primarily due to the difficuly of introducing effective unit testing to TSQL, lack of encapsulation and poor tools (Debuggers, editors).

Working with a Java team in the past I quickly found out that often the complete opposite holds in that environment. A Java Architect once told me: "The database is for data, not code.".

These days I think it's a mistake to not consider stored procs at all, but they should be used sparingly (not by default) in situations where they provide useful benefits (see the other answers).

Stored procedures tend to become an island of hurt in any project where they are used, thus some developers make a rule "No stored procedures". So it looks like there is an open conflict beteween them. Your answer makes a good case for when to actually choose one way, or the other.
Warren P
+5  A: 

Treating the database as just a storage mechanism (i.e. glorified collections library) and hence subordinate to their application (ignoring other applications which share the data)

A corollary to to this is offloading too much query work to the application instead of keeping it in the db where it belongs. LINQ is particularly bad about this.
David Lively
+6  A: 

Not using parameterized queries. They're pretty handy in stopping SQL Injection.

This is a specific example of not sanitizing input data, mentioned in another answer.

+1  A: 

Here is a link to video called ‘Classic Database Development Mistakes and five ways to overcome them’ by Scott Walz

Charles Faiga
+45  A: 

Key database design and programming mistakes made by developers

  • Selfish database design and usage. Developers often treat the database as their personal persistent object store without considering the needs of other stakeholders in the data. This also applies to application architects. Poor database design and data integrity makes it hard for third parties working with the data and can substantially increase the system's life cycle costs. Reporting and MIS tends to be a poor cousin in application design and only done as an afterthought.

  • Abusing denormalised data. Overdoing denormalised data and trying to maintain it within the application is a recipe for data integrity issues. Use denormalisation sparingly. Not wanting to add a join to a query is not an excuse for denormalising.

  • Scared of writing SQL. SQL isn't rocket science and is actually quite good at doing its job. O/R mapping layers are quite good at doing the 95% of queries that are simple and fit well into that model. Sometimes SQL is the best way to do the job.

  • Dogmatic 'No Stored Procedures' policies. Regardless of whether you believe stored procedures are evil, this sort of dogmatic attitude has no place on a software project.

  • Not understanding database design. Normalisation is your friend and it's not rocket science. Joining and cardinality are fairly simple concepts - if you're involved in database application development there's really no excuse for not understanding them.

I'd give you a 1000 upvotes if I could for that first point especially
One might argue that transactions should be done in transactional database and reporting and MIS should be done in a separate analysis database. Therefore you get the best of both worlds and everyone is happy (except for the poor mug who has to write the data transformation script to build the latter out of the former).
Chris Simpson
Not just the poor mug writing the ETL - anyone using data from the system, the poor quality data in the MIS application that's boxed in because several key relationships aren't actually recorded at source, anyone involved in the endless reconciliation bun-fignts that ensue from the poor data quality.
Hey writing ETL processes is FUN! (Ok so I'm a bit wierd but I do this for a living.)
From time to time I do too, although these days I tend to be drifting further into the dark abyss of consultancy (Your anger will make you strong ...). However, all too often I see operational systems designed with no thought to actually doing MIS off them.
I could not possibly disagree more with point one. Databases are for persistence, they are not for inter-process communication. There are almost always better solutions to that problem. Unless there is an explicit requirement for it, you absolutely SHOULD treat the database as if nobody except for your application will ever use it. Even if there IS an explicit requirement, do some user story and root cause analysis on it and you will quite often discover a much better way of filling the requestor's intent. Then again, I do work at a company where the phrase CQRS is somewhat common
George Mauer
Trivial example: I have an insurance policy administration system and need to load the state of 5 million claims into a ceded reinsurance system to calculate potential recoveries. The systems are older client-server COTS packages, designed to interface to even older mainframe systems. Both have to be reconciled for financial control purposes. This job is done once per month. By your logic I would write a series of user stories defining the requirements and ask the vendors to quote on adding a web service wrapper to their existing products.
But then again, I do work in companies where phrases like 'Audit', 'FSA' and 'Sarbannes-Oxley' are somewhat common.
Wow, really? Let's be even snippier next time! Sounds like you already have a requirement to your vendors that their systems be accessible for bulk import operations, wouldn't it be better if this was stated explicitly than just by happenstance? 90% of the time if the two of you analyze this requirement, you will realize that there are a variety of ways they can deliver a product that achieves your goals. As for audit trails - that along with data mining are one of the origins of the discipline of cqrs, in its event sourcing incarnation it leaves perfect audit trails.
George Mauer
+3  A: 

1 - Unnecessarily using a function on a value in a where clause with the result of that index not being used.


where to_char(someDate,'YYYYMMDD') between :fromDate and :toDate

instead of

where someDate >= to_date(:fromDate,'YYYYMMDD') and someDate < to_date(:toDate,'YYYYMMDD')+1

And to a lesser extent: Not adding functional indexes to those values that need them...

2 - Not adding check constraints to ensure the validity of the data. Constraints can be used by the query optimizer, and they REALLY help to ensure that you can trust your invariants. There's just no reason not to use them.

3 - Adding unnormalized columns to tables out of pure laziness or time pressure. Things are usually not designed this way, but evolve into this. The end result, without fail, is a ton of work trying to clean up the mess when you're bitten by the lost data integrity in future evolutions.

Think of this, a table without data is very cheap to redesign. A table with a couple of millions records with no integrity... not so cheap to redesign. Thus, doing the correct design when creating the column or table is amortized in spades.

4 - not so much about the database per se but indeed annoying. Not caring about the code quality of SQL. The fact that your SQL is expressed in text does not make it OK to hide the logic in heaps of string manipulation algorithms. It is perfectly possible to write SQL in text in a manner that is actually readable by your fellow programmer.

John Nilsson
+2  A: 

Not paying enough attention towards managing database connections in your application. Then you find out the application, the computer, the server, and the network is clogged.

+2  A: 
  • Dismissing an ORM like Hibernate out of hand, for reasons like "it's too magical" or "not on my database".
  • Relying too heavily on an ORM like Hibernate and trying to shoehorn it in where it isn't appropriate.
Adam Jaskiewicz
+7  A: 

I'd like to add: Favoring "Elegant" code over highly performing code. The code that works best against databases is often ugly to the application developer's eye.

Believing that nonsense about premature optimization. Databases must consider performance in the original design and in any subsequent development. Performance is 50% of database design (40% is data integrity and the last 10% is security) in my opinion. Databases which are not built from the bottom up to perform will perform badly once real users and real traffic are placed against the database. Premature optimization doesn't mean no optimization! It doesn't mean you should write code that will almost always perform badly because you find it easier (cursors for example which should never be allowed in a production database unless all else has failed). It means you don't need to look at squeezing out that last little bit of performance until you need to. A lot is known about what will perform better on databases, to ignore this in design and development is short-sighted at best.

+1 - Database programming involves optimising the behaviour of mechanical components. Note, however, that Knuth says premature optimisation is the root of all evil about 97% of the time (or words to that effect). Database design is one area where you really do have to think about this up front.
+2  A: 

This has been said before, but: indexes, indexes, indexes. I've seen so many cases of poorly performing enterprise web apps that were fixed by simply doing a little profiling (to see which tables were being hit a lot), and then adding an index on those tables. This doesn't even require much in the way of SQL writing knowledge, and the payoff is huge.

Avoid data duplication like the plague. Some people advocate that a little duplication won't hurt, and will improve performance. Hey, I'm not saying that you have to torture your schema into Third Normal Form, until it's so abstract that not even the DBA's know what's going on. Just understand that whenever you duplicate a set of names, or zipcodes, or shipping codes, the copies WILL fall out of synch with each other eventually. It WILL happen. And then you'll be kicking yourself as you run the weekly maintenance script.

And lastly: use a clear, consistent, intuitive naming convention. In the same way that a well written piece of code should be readable, a good SQL schema or query should be readable and practically tell you what it's doing, even without comments. You'll thank yourself in six months, when you have to to maintenance on the tables. "SELECT account_number, billing_date FROM national_accounts" is infinitely easier to work with than "SELECT ACCNTNBR, BILLDAT FROM NTNLACCTS".

Peter B
If you set them up correctly they won't but this involves the use of triggers which many people are allergic to.
+3  A: 

Not executing a corresponding SELECT query before running the DELETE query (particularly on production databases)!

+1  A: 

There is one thing I might add, learn using analytic functions like PARTITION BY, RANK, DENSE_RANK (for Oracle). They are absolutely essential for complex queries.

Other advice is, if possible, to have a dedicated database developer in your development team who is expert in SQL, database modelling, tuning, etc. (Not a DBA though). Such skill is a great asset.

+2  A: 

Not having an understanding of the databases concurrency model and how this affects development. It's easy to add indexes and tweak queries after the fact. However applications designed without proper consideration for hotspots, resource contention and correct operation (Assuming what you just read is still valid!) can require significant changes within the database and application tier to correct later.

+5  A: 

Using Excel for storing (huge amounts of) data.

I have seen companies holding thousands of rows and using multiple worksheets (due to the row limit of 65535 on previous versions of Excel).

Excel is well suited for reports, data presentation and other tasks, but should not be treated as a database.