1

I have a complex database application. There are a lot of lookup tables containing a few values, in particular one contains pass / fail / waiting / unknown.

In the application I find many of the queries depend on the pass/fail status of a model. So I want to display everything with a pass. Or everything that isn't a fail.

I am using Django (though I am sure the question is relevant outside of Django).

So when I am querying via the ORM, I can join the extra table in and say for example. 

Model.objects.filter(passfail__status='pass')

Alternatively I can use the ID. 

Model.objects.filter(passfail_id=1)

The first example will join in the passfail table and query based on the "status" field - the actual text 'pass' / 'fail' / 'waiting' text.

Is either of these considered good / bad practice?

Using the ID should be slightly better performance, as there is one less join. And it will avoid the problem of the passfail status changing (it shouldn't but I never know what users will do).

Using the status field should make the code more readable and more obvious what we are trying to achive. Though I don't expect the passfail table to change.

wobbily_col
  • 1,861
  • 3
  • 16
  • 25
  • What else is in the passfail table? If all it has is id and status then just drop the table completely. I used to make a gazillion look up tables but gradually shifted to getting rid of them if there was no realistic chance of them needing to be changed. – Cerad Feb 11 '15 at 16:17
  • pass fail waiting and unknown. It may get updated in the future, not likely, but always a possibility. (I think it started as a boolean). – wobbily_col Feb 11 '15 at 16:38
  • Not the values but the columns themselves. If the table holds only one piece of useful information per row then it might be that the table itself is not needed. – Cerad Feb 11 '15 at 20:06

1 Answers1

4

Use enums. Good ORM framework can easily handle mapping of enums to IDs. Then you benefit two ways:

  1. Simple use in code and clear business logic - you operate (compare, etc.) on descriptive names, so business logic is clear.
  2. Performance efficiency: No joins for descriptive names in db queries (ORM will operate on Ids only)

If you can't - you can fallback to named constants just to make your code clear. You unfortunately won't benefit in ORM then.

Marcin Wachulski
  • 207
  • 1
  • 6
  • Other answers recommended against using enums in case the values change or get added to. It certainly feels like a less flexible solution. Is there any reason not to go for the extra join? – wobbily_col Feb 11 '15 at 14:02
  • If there is high variability in values then I would use a proper implementation of a dictionary. Please note that given requirements are contradictory: 1. compile time safety (enums) 2. values can be added/removed frequently. Then business rules clarity and clean code are more important than the additional join mentioned previously. – Marcin Wachulski Feb 11 '15 at 15:34