CODEWITHSUNDEEP
databasedatabase-design
Abe Miessler
Abe Miessler

Reputation: 85056

Bad practice to have IDs that are not defined in the database?

I am working on an application that someone else wrote and it appears that they are using IDs throughout the application that are not defined in the database. For a simplified example, lets say there is a table called Question:

Question
------------
Id
Text
TypeId
SubTypeId

Currently the SubTypeId column is populated with a set of IDs that do not reference another table in the database. In the code these SubTypeIds are mapped to a specific string in a configuration file.

In the past when I have had these types of values I would create a lookup table and insert the appropriate values, but in this application there is a mapping between the IDs and their corresponding text values in a configuration file.

Is it bad practice to define a lookup table in a configuration file rather than in the database itself?

Upvotes: 0

Views: 126

Answers (2)

aneroid
aneroid

Reputation: 15962

Is it bad practice to define a lookup table in a configuration file rather than in the database itself?

Absolutely, yes. It brings in a heavy dependence on the code to manage and maintain references, fetch necessary values, etc. In a situation where you now need to create additional functionality, you would rely on copy-pasting the mapping (or importing them, etc.) which is more likely to cause an issue.

It's similar to why DB constraints should be in the DB rather than in the program/application that's accessing it - any maintenance or new application needs to replicate all the behaviour and rules. Having things this way has similar side-affects I've mentioned here in another answer.

Good reasons to have a lookup table:

  1. Since DBs can generally naturally have these kinds of relations, it would be obvious to use them.
  2. Queries first need to be constructed in code for the Type- and SubType- Text vs ID instead of having them as part of the where/having clause of the query that is actually executed.
  3. Speed/Performance - with the right indexes and table structures, you'd benefit from this (and reduce code complexity that manages it)
  4. You don't need to update your code for to add a new Type or SubType, or to edit/delete them.

Possible reasons it was done that way, which I don't think are valid reasons:

  • The TypeID and SubTypeID are related and the original designer did not know how to create a complex foreign key. (Not a good reason though.)
  • Another could be 'translation' but that could also be handled using foreign key relations.
  • In some pieces of code, there may not be a strict TypeID-to-SubTypeID relation and that logic was handled in code rather than in the DB. Again, can be managed using 'flag' values or NULLs if possible. Those specific cases could be handled by designing the DB right and then working around a unique/odd situation in code instead of putting all the dependence on the code.
  • NoSQL: Original designer may be under the impression that such foreign keys or relations cannot be done in a NoSQL db.
  • And the obvious 'people' problem vs technical challenge: The original designer may not have had a proper understanding of databases and may have been a programmer who did that application (or was made to do it) without the right knowledge or assistance.
  • Just to put it out there: If the previous designer was an external contractor, he may have used the code maintenance complexity or 'support' clause as a means to get more business/money.

Upvotes: 1

mflaming
mflaming

Reputation: 1157

As a general rule of thumb, I'd say that keeping all the related data in a DB is a better practice since it removes a tacit dependency between the DB and your app, and because it makes the DB more "comprehensible." If the definitions of the SubTypeIDs are in a lookup table it becomes possible to create queries that return human-readable results, etc.

That said, the right answer probably depends a bit on the specifics of the application. If there's very tight coupling between the DB and app to begin with (eg, if the DB isn't going to be accessed by other clients) this is probably a minor concern particularly if the set of SubTypeIDs is small and seldom changes.

Upvotes: 0

Related Questions