Should I define the relations between tables in the database or just in code?

Question

In my experience, many of the projects I have read in the past didn't have relationship definitions in the database, instead they only defined them in the source code. So I'm wondering what are the advantages/disadvantages of defining relations between tables in the database and in source code? And the broader question is about other advanced features in modern databases like cascade, triggers, procedures... There are some points in my thoughts:

In the database:

• Correct data from design. Prevent application errors which can cause invalid data.

• Reduce network round trip to application when inserting/updating data as application has to make more query(s) to check data integrity.

In source code:

• More flexible.

• Better when scaling to multiple databases, as sometimes the relation can be cross-database.

• More control over data integrity. The database doesn't have to check every time the application modifies data (complexity can be O(n) or O(n log n) (?)). Instead, it's delegated to application. And I think handling data integrity in the application will lead to more verbose error messages than using the database. Eg: when you create an API server, if you define the relations in the database, and something goes wrong (like the referenced entity doesn't exist), you will get an SQL Exception with a message. The simple way will be to return 500 to the client that there is an "Internal server error" and the client will have no idea what is going wrong. Or the server can parse the message to figure out what's wrong, which is an ugly, error-prone way in my opinion. If you let the application handle this, the server can generate a more meaningful message to client.

Is there anything else?

Edit: as Kilian points out, my point about performance & data integrity is very misguided. So I edited to correct my point there. I totally understand that letting the database handle it will be a more efficient and robust approach. Please check the updated question and give some thoughts about it.

Edit: thank you everyone. The answers I received all point out that the constraints/relations should be defined in the database. :). I have one more question, as it is quite out of scope of this question, I've just posted it as a separate question: Handle database error for API server. Please leave some insights.

Answer 1

TL;DR: Relationship constraints should go in the database.

---

Your application ain't big enough.

You are correct, indeed, that enforcing relationships across databases may require enforcing them in the application.

I would point out, however, that you should first check the documentation of the database software you are using, and check existing product offers. For example, there are clustering offers on top of Postgres and MySQL.

And even if you end up needing to have some validation in the application, don't throw out the baby with the bath water. After all, the less you have to do, the better off you are.

Finally, if you are worried about future scalability issues, I am afraid that your application will have to undergo significant changes before it can scale anyway. As a rule of thumb, every time you grow 10x, you have to re-design... so let's not sink too much money into failing to anticipate scalability issues, and instead use money to actually reach the point where you have those issues.

Your application ain't correct enough.

What is the chance that the database you use have a faulty implementation of the check compared to the chance that your application has a faulty implementation of the check?

And which one do you alter most often?

I'd bet on the database being correct, any time.

Your developers ain't thinking distributed enough.

Reduce network round trip to application when insert/update data as application has to make more query(s) to check data integrity.

Red Flag!¹

If you are thinking:

• check if the record exists
• if not, insert record

then you failed the most basic concurrency issue: another process/thread might be adding the record as you go.

If you are thinking:

• check if the record exists
• if not, insert record
• check if the record was inserted as a duplicate

then you failed to account for MVCC: the view of the database that you have is a snapshot at the time your transaction started; it does not show all the updates that are occurring, and maybe not even committed.

Maintaining constraints across multiple sessions is a really hard problem, be glad it's solved in your database.

¹ Unless your database properly implements the Serializable property; but few actually do.

---

Last:

And I think, handle data integrity in application will let to more verbose error message than using database. Eg: when you create an API server. If you define relations in database, and something go wrong(like the referenced entity doesn't exist), you will get an SQL Exception with message.

Do not parse error messages, if you use any production-grade database it should return structured errors. You'll have some error code, at least, to indicate what is possibly wrong, and based on this code you can craft a suitable error message.

Note that most of the times the code is enough: if you have an error code telling you that a referenced foreign key does not exist, then it's likely that this table only has one foreign key, so you know in the code what the problem is.

Also, and let's be honest here, most of the times you will not handle errors that gracefully anyway. Just because there are so many of them and you'll fail to account for them all...

... which just ties in to the correctness point above. Each time you see a "500: Internal Server Error" because a database constraint fired and was not handled, it means the database saved you, since you just forgot to handle it in the code.

Answer 2

The database doesn't have to check for data integrity every time application modify data.

This is a deeply misguided point. Databases were created for precisely this purpose. If you need data integrity checks (and if you think you don't need them, you're probably mistaken), then letting the database handle them is almost certainly more efficient and less error-prone than doing it in application logic.

Answer 3

The constraints should lie within your database, as (with the best will in the world), your application will not be the only thing to ever access this database.

At some point, there may need to be a scripted fix within the database, or you may need to migrate data from one table to another on deployment.

Additionally you may get other requirements e.g. "Big customer X really needs this excel sheet of data imported into our application database this afternoon", where you will not have the luxury of adapting your application code to suit when a dirty SQL script will get it done in time.

This is where database level integrity will save your bacon.

---

Additionally, picture the developer who takes your role at this company after you leave and is then tasked with making database changes.

Will he hate you if there are no FK constraints within the database so that he can tell what relationships a table has before he changes it? (Clue, the answer is yes)

Answer 4

You should have relations in the database.

As the other answer notes, performance of constraint checking will be far better inside that database than inside your application. Database constraint checks are one of the things that databases are good at.

If you ever need additional flexibility - e.g. your noted cross database references - then you can remove the constraints deliberately and with consideration. Having consistency within your database means that you have the option of modifying those constraints, and certainty of referential integrity.

Answer 5

• We no longer live in one back-end <-> one front-end world.
• Most solutions involve a web front-end, a mobile front-end, a batch-front-end, and iPad front-end, etc.
• Database engines already have thousands of tested lines of code optimized to enforce referential integrity.

Can you really afford writing and testing referential integrity enforcing code when you have domain problem solving code to write?

Answer 6

If you don't validate your data integrity, constraints, relationships etc. at the database level that means it is much easier for anyone with production database access (through any other client including a DB access tool) to mess up your data.

It is great practice to enforce the strictest possible data integrity at the database level. Trust me, this will save you enormous headaches over time in any non-trivial system. You will also pick up application logic errors or business requirement errors and inconsistencies faster if careful thought is put into this.

As a side note to this, design your database in a way that is as normalized and atomic as possible. No "God" tables. Spend a lot of effort designing your database to be as simple as possible, ideally with many small tables that are individually very well defined, with a single responsibility and carefully validated on all columns. The database is the last guardian of your data integrity. It represents the Keep of the Castle.

Answer 7

Most people are essentially saying "yes, in general thou shalt always define the relations in the database". But if disciplines in computer science were so easy, we would be called "Software Manual Readers" instead of "Software Engineers". I do actually agree that the constraints should go in the database, unless there is a good reason they shouldn't, so let me just provide a couple reasons that might be considered good in certain situations:

Duplicate Code

Sometimes a certain amount of functionality that could be handled by the database will naturally exist in application code. If adding something like constraints to the database would be redundant, it might be better not to duplicate the functionality, because you are violating DRY principles, and you might worsen the juggling act of keeping the database and application code in sync.

Effort

If your database is already doing what it needs to do without using advanced features, you might want to evaluate where your time, money, and effort should be placed. If adding constraints would prevent a catastrophic failure and thus save your company a lot of money, then it is probably worth it. If you are adding constraints that should hold, but are already guaranteed to never be violated, you are wasting time and polluting your code base. Guaranteed is the operative word here.

Efficiency

This is normally not a good reason but in some cases you might have a certain performance requirement. If application code can implement a certain functionality in a faster way than the database, and you need the extra performance, you might need to implement the feature in application code.

Control

Somewhat related to efficiency. Sometimes you need extremely fine grained control about how a feature is implemented, and sometimes having the database handle it hides it behind a black box that you need to open.

Closing Points

• Databases are written in code. There's nothing magic they do that you can't do in your own code.
• Nothing is free. Constraints, relations, etc. all use CPU cycles.
• People in the NoSQL world get along just fine without traditional Relational features. In MongoDB for example, the structure of JSON documents is good enough to support an entire database.
• Blind and ignorant use of advanced database features can't guarantee any benefits. You might accidentally make something work only to break it later.
• You asked a very general question without listing specific requirements or constraints. The real answer to your question is "it depends".
• You didn't specify if this was an enterprise scale problem. Other answers are talking about things like customers and data integrity, but sometimes those things aren't important.
• I'm assuming you are talking about a traditional SQL Relational database.
• My perspective comes from having moved away from using tons of constraints and foreign keys in small (up to 50 tables) projects, and not noticing any drawbacks.

The last thing I will say is that you will know if you shouldn't be placing the functionality in the database. If you're not sure, you are probably better off using the database features, because they usually work really well.

Answer 8

As always, there are many answers. For me I found a simple rule (well it only works for a model-centric approach). Usually, I only focus on the different layers of applications.

If the model consists of several entities and there are dependencies between the entities, the persistance layer should reflect those dependencies with its possibilities. So if you're using a RDBMS, then you should also use foreign keys. Reason is simple. That way the data is always valid structurewise.

Any instance doing work on this persistance layer can rely on it. I'm assuming, that you're encapsulating this layer via interface (service). So here is the point where the design ends and the real world begins.

Looking at your points, especially cross-database references. In that case, yes there should not be a reference implemented in the RDBMS itself, but in the service. But before following this way, wouldn't it be better to consider this already during the design?

Means, if I know already, that there are parts which need to be stored in a different DB, then I can put them already there and define it as seperate model. Right?

You're also pointing out that implementing this in code is more flexible. Right, but doesn't that sound like you're dealing with an incomplete design? Ask yourself, why do you need more flexibility?

The performance issue, due to the integrity checks in DB is not real. The RDBMS can check such things much faster than any implementation by you. Why? Well, you have to deal with the media disruption, the RDBMS doesn't. And it can optimize such checks by using its statistics a.s.o.

So you see, it all comes back to design. Of course you can say now, but what if an unknown requirement is appearing, a game changer? Yes it might happen, but such changes should be designed and planned a.s.o.. ;o)

Answer 9

You have some very good answers but some more points

Data integrity is what a database is designed to do

Doing proper concurrency of like a FK delete at the application level would be horrendous

Expertise in data integrity is with a DBA

At the program level you insert, update, bulk update, bulk insert, bulk delete ...
Thin client, thick client, mobile client ....
Data integrity is not the expertise of a programmer - lots of duplicate code and someone will mess it up

Say you get hacked - you are in trouble either way but a hacker can do a lot of damage via a small hole if there is no integrity protection at the database

You may need to manipulate data directly via SQL or TSQL
No one is going to remember all the data rules

Answer 10

Your question doesn't make sense: if you can change the database, it's code, if you can't change the database, you'll have to create your constraints elsewhere.

A database which you can change is every bit as much code as any line of ruby, javascript, c# or ada.

The question about where to put a constraint in your system should boil down to reliability, cost and ease of development.

Answer 11

There are tons of good answers here. I'll add that if you have an app written in language Y, you can create database-constraint-like code in Y. And then someone wants to access your database using language Z, you'll have to write the same code again. God help you if the implementations aren't exactly the same. Or when a knowledgeable business user connects to your database using Microsoft Access.

My experience tells me that when people don't want to use database constraints, it's because they're actually trying to do something the wrong way. For example, they're trying to bulk load data, and they want to leave not-null columns null, for a while. They intend to "fix that later" because the situation that made the not-null constraint critical "can't possibly happen in this case." Another example could be when they're trying to shoe-horn two different types of data into the same table.

More experienced people will take a step back and find a solution that does not involve attempting to bypass a constraint. The solution could simply be the constraint is no longer proper because the business changed, of course.