What list of database features/elements are necessary for a programmer to understand?

Question

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?

Answer 1

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)

Answer 2

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

Answer 3

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.

Answer 4

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.

Answer 5

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.

Answer 6

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