Take the 2-minute tour ×
Programmers Stack Exchange is a question and answer site for professional programmers interested in conceptual questions about software development. It's 100% free, no registration required.

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?

share|improve this question

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)
share|improve this answer
    
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 Dec 12 '10 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

share|improve this answer

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.

share|improve this answer
    
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. –  Pratik Dec 12 '10 at 23:22
    
+1 and @Pratik: Expert, maybe not but very proficient: definitely. –  Steve Evers Dec 13 '10 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.

share|improve this answer

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

share|improve this answer
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.

share|improve this answer

Your Answer

 
discard

By posting your answer, you agree to the privacy policy and terms of service.

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