What database features/elements are necessary for a programmer to understand in order to create non-trivial applications?

I was once asked in a job interview (by a DBA) to rate my understanding of database concepts -- I thought it would be interesting to get opinions.

I'm looking for basic and advanced skills, also any opinions on where developers are typically the weakest (what lack of skills or knowledge creates the most problems). Also, is it better that developers are need-to-know, and pass more advanced work onto a DBA -- where does that "line in the sand" exist?

  • 1
    Your questions uses "non-trivial" as if it (by itself) adequately describes every possible non-trivial use case that it can encompass. By definition of being non-trivial, this is simply not possible - barring a complete reading of everything about databases, which is massively off topic and nigh impossible to succinctly answer.
    – Flater
    Commented Jun 2, 2022 at 10:09

6 Answers 6


Normalisation - and all the other details of how to properly structure a database, couple of random examples:

  • That you almost always want to use a synthetic key (in my case most often an auto incrementing int called ID)
  • That you want to store dates, times and the like as native types (or as close as you can get)
  • I agree with you about synthetic keys, but I remember fierce debattes about that topic with other experienced developers who insisted on natural keys.
    – user281377
    Commented Dec 12, 2010 at 23:21

For DBA :-
1) Configuration change/upgrading to new version
2) Backup and recovery

For designer :-
1) Normalization
2) indexing, keys, constraints, triggers, views etc.
3) Performance tuning, memory vs. speed

For Developer :-
1) Basic SQL
2) Low level connectivity (OCI,ODBC, JDBC, Hibenrate etc. depending on their field)
3) multiple connections behavior


Developer ought to to know:

  • SQL
  • Performance tuning
  • Keying and Partitioning (as far as what you can control)
  • Application deployment

As for what's left to the administrator:

  • Installation; creating the database
  • Backup / Recovery

You may have to understand components that are specific to your environment, like ORM and LINQ.

  • Which developer ? By all means normal developers should know the basic concepts. But a good development team should have specialist SQL developer to help out developers in C#/Java/Python etc. SQL is a big, complex topic and a specialised language. I would not expect a developer in general purpose programming language to be an expert in SQL.
    – softveda
    Commented Dec 12, 2010 at 23:22
  • +1 and @Pratik: Expert, maybe not but very proficient: definitely. Commented Dec 13, 2010 at 0:51

Non-SQL Developers should know the concept or RDBMS, PK/FK/CK, normalisation, indexing, transactions, table design etc. They should have an idea of how data is stored on disk and moved to memory. I would say basic to intermediate SQL along with understanding query plans, joins is a must too.
Specialist SQL developers should know the whole gamut of Database topics, optmisation and advanced queries with in-depth knowledge of particular RDBMS product like Oracle etc in which they specialise.

explain plan;

You need to be able to debug your SQL. This means you need to understand what the database chooses to dó and knowing how to change it's mind.


What database features/elements are necessary for a programmer to understand in order to create non-trivial applications?

The more experience with database design and normalization the better. Too little (e.g. tables failing to be 1NF) can be just as bad a too much (EVA).

Basic SQL can solve most problems anything beyond can be handled by asking questions on SO.

Developers should also at least know what ACID stands for, with some idea of what it means.

The other thing developers should know is that bad things sometimes happen to transactions and your application should expect every transaction to always work. Here's a quote from an article by Rico Mariani

One Last Warning

If you consider what I said, about the natural occurrence of failures in a database, then you’ll soon realize that it is normal, using Linq parlance, for db.SubmitChanges() to throw an exception from time to time. If you are trying to write a robust application with high reliability you need to think about that.

In addition to obvious things like, “the network went down”, “the database went down”, there are less obvious things like, “there was a deadlock”, “there was an optimistic lock conflict” that can and do happen. Those latter two things should be appropriately retried because nothing bad has happened. The strategy you choose, especially for cases where the optimistic lock failed, can have a profound impact on your performance and certainly you can’t just let those exceptions flow to the user. I think I can safely say that my mom doesn’t want to hear about how table X on connection A deadlocked with table Y on connection B.

If you’ve been reading carefully then you’ll see that it’s also “normal” for a foreach operation over a Linq query to fail from time to time – you need a retry strategy for those too to be fully robust.

Don’t get down on Linq though, those problems exist with all data solutions, the productivity benefits you get from Linq will go a long way to helping you to add the robustness you need in the areas you need it.

Don’t read “too much” at once. Don’t write “too much” at once. Handle deadlocks, they’re normal. Handle optimistic lock failures, they’re also normal. You should land in the Pit of Success.

As for that line in the sand? I would say Developers don't need to know anything about the physical operation, sizing, partitioning, monitoring, (backup/restore), security, High Availability, Disaster recovery, initial setup, etc

  • Additionally one should know about "transaction isolation levels" Commented Jun 2, 2022 at 10:45

Not the answer you're looking for? Browse other questions tagged or ask your own question.