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,891

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