Return to Revisions

The reasons for such design decisions are often not technical ones, but organizational ones. I have seen this happen in the real world, in situations of the following kind:

• At the time when the system is designed, there is only one application which has exclusive access to the database, so referential constraints and ID columns are not that important at the beginning.

• The developers of the system are well-trained in application development, but less trained in database design.

• The designers may had some bad experiences with referential integrity constraints in the database, because they usually don't allow easily to implement "exceptions in special cases" - such exceptions are way easier to handle at the application side. For example: "this column in table A must be a not-null foreign key ref to the ID column of table B" - and then someone says "oh, except when the isTemplate column in table A is true. To colve such kind of requirements at the DB side one would need to replace the FK constraint by some more or less complex DB trigger.

• The - normally very good - approach of "let's start with a most simple working solution first and improve later" is interpreted as let's start without relationships in the DB first and add them later (instead of let's start with strict constraints and make them less strict later).

Unfortunately, once a system is build without referential integrity and it first version goes into production, sooner or later it will contain a lot of data which could not have been inserted into the db with some sensible contraints enabled.

Then it becomes hard to add those contraints to the db afterwards. It is often easier for the devs to modify the application instead to make it handle this "low quality" data, so the data can stay unmodified in the database, instead of initiating a "data improvement process", which would be necessary to fix the problems at the DB side, but may require support by users and administrators.

Of course, when a system grows, and it is extended to have more than one application accessing the DB, especially more than one which writes data into it, there is surely a point where it would be better to have a more rigid the DB design. But the later one tries to get there, the harder it becomes.