What should every developer know about databases?

The impedance mismatch problem, and know the common deficiencies or ORMs.

The order of columns in a non-unique index is important.

The first column should be the column that has the most variability in its content (i.e. cardinality).

This is to aid SQL Server ability to create useful statistics in how to use the index at runtime.

**RDBMS Compatibility**

Look if it is needed to run the application in more than one RDBMS. If yes, it might be necessary to:

- avoid RDBMS SQL extensions
- eliminate triggers and store procedures
- follow strict SQL standards
- convert field data types
- change transaction isolation levels

Otherwise, these questions should be treated separately and different versions (or configurations) of the application would be developed.

From my experience with relational databases, every developer should know:

**- The different data types**:

Using the correct type for the correct job will make your DB design more robust, your queries faster and your life easier.

**- Learn about 1xM and MxM**:

This is the bread and butter for relational databases. You need to understand one-to-many and many-to-many relations and apply then when appropriate.

**- "[K.I.S.S.][1]" principle applies to the DB as well**:

Simplicity always works best. Provided you have studied how DB work, you will avoid unnecessary complexity which will lead to maintenance and speed problems.

**- Indices**:

It's not enough if you know what they are. You need to understand when to used them and when not to.

---

also:

- Boolean algebra is your friend
- Images: Don't store them on the DB. Don't ask why.
- Test DELETE with SELECT


[1]:

[To see links please register here]

Don't depend on the order of rows returned by an SQL query.

Understand the tools that you use to program the database!!!

I wasted so much time trying to understand why my code was mysteriously failing.

If you're using .NET, for example, you need to know how to properly use the objects in the `System.Data.SqlClient` namespace. You need to know how to manage your `SqlConnection` objects to make sure they are opened, closed, and when necessary, disposed properly.

You need to know that when you use a `SqlDataReader`, it is necessary to close it separately from your `SqlConnection`. You need to understand how to keep connections open when appropriate to how to minimize the number of hits to the database (because they are relatively expensive in terms of computing time).

The very first thing developers should know about databases is this: **what are databases for**? Not how do they work, nor how do you build one, nor even how do you write code to retrieve or update the data in a database. But what are they for?

Unfortunately, the answer to this one is a moving target. **In the heydey of databases, the 1970s through the early 1990s, databases were for the sharing of data.** If you were using a database, and you weren't sharing data you were either involved in an academic project or you were wasting resources, including yourself. Setting up a database and taming a DBMS were such monumental tasks that the payback, in terms of data exploited multiple times, had to be huge to match the investment.

**Over the last 15 years, databases have come to be used for storing the persistent data associated with just one application.** Building a database for [MySQL][1], or [Access][2], or [SQL Server][3] has become so routine that databases have become almost a routine part of an ordinary application. Sometimes, that initial limited mission gets pushed upward by mission creep, as the real value of the data becomes apparent. Unfortunately, databases that were designed with a single purpose in mind often fail dramatically when they begin to be pushed into a role that's enterprise wide and mission critical.

The second thing developers need to learn about databases is the whole **data centric view** of the world. The data centric world view is more different from the process centric world view than anything most developers have ever learned. Compared to this gap, the gap between structured programming and object oriented programming is relatively small.

The third thing developers need to learn, at least in an overview, is data modeling, including conceptual data modeling, logical data modeling, and physical data modeling.

**Conceptual data modeling** is really requirements analysis from a data centric point of view.

**Logical data modeling** is generally the application of a specific data model to the requirements discovered in conceptual data modeling. The relational model is used far more than any other specific model, and developers need to learn the relational model for sure. Designing a powerful and relevant relational model for a nontrivial requirement is not a trivial task. You can't build good SQL tables if you misunderstand the relational model.

**Physical data modeling** is generally DBMS specific, and doesn't need to be learned in much detail, unless the developer is also the database builder or the DBA. What developers do need to understand is the extent to which physical database design can be separated from logical database design, and the extent to which producing a high speed database can be accomplished just by tweaking the physical design.

The next thing developers need to learn is that **while speed (performance) is important, other measures of design goodness are even more important**, such as the ability to revise and extend the scope of the database down the road, or simplicity of programming.

Finally, anybody who messes with databases needs to understand that **the value of data often outlasts the system that captured it**.

Whew!

[1]:

[To see links please register here]

[2]:

[To see links please register here]

[3]:

[To see links please register here]

I would like everyone, both DBAs and developer/designer/architects, to better understand how to properly model a business domain, and how to map/translate that business domain model into both a normalized database logical model, an optimized physical model, and an appropriate object oriented class model, each one of which is (can be) different, for various reasons, and understand when, why, and how they are (or should be) different from one another.

I would say strong basic SQL skills. I've seen a lot of developers so far who know a little about databases but are always asking for tips about how to formulate a quite simple query. Queries are not always that easy and simple. You do have to use multiple joins (inner, left, etc.) when querying a well normalized database.

About the following comment to Walter M.'s answer:

"Very well written! And the historical perspective is great for people who weren't doing database work at that time (i.e. me)".

The historical perspective is in a certain sense absolutely crucial. "Those who forget history, are doomed to repeat it.". Cfr XML repeating the hierarchical mistakes of the past, graph databases repeating the network mistakes of the past, OO systems forcing the hierarchical model upon users while everybody with even just a tenth of a brain should know that the hierarchical model is not suitable for general-purpose representation of the real world, etcetera, etcetera.

As for the question itself:

Every database developer should know that "Relational" is not equal to "SQL". Then they would understand why they are being let down so abysmally by the DBMS vendors, and why they should be telling those same vendors to come up with better stuff (e.g. DBMS's that are truly relational) if they want to go on sucking hilarious amounts of money out of their customers for such crappy software).

And every database developer should know everything about the relational algebra. Then there would no longer be a single developer left who had to post these stupid "I don't know how to do my job and want someone else to do it for me" questions on Stack Overflow anymore.