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.
3
Use enums. Good ORM framework can easily handle mapping of enums to IDs. Then you benefit two ways:
- Simple use in code and clear business logic – you operate (compare, etc.) on descriptive names, so business logic is clear.
- 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.
2