Part of the joy of new experiences is making mistakes and learning from them. For this month’s invitation, Louis Davidson asks us to share one of those mistakes to help others learn.
The lesson I learned: don’t let order become meaning
TL;DR: Don’t reuse numeric sequences for things they’re not designed for.
With the prevalence of identity columns on tables, it can be easy for green-field development to be tripped up by assigning meaning to something which is designed for order.
The classic example of this is user roles:
- 1: Read-only
- 2: Associate
- 3: Supervisor
- 4: Manager
- 5: Administrator
On face value that might look acceptable, until we want a Super User role to manage the system which would sit between roles 4 and 5. Or if you wanted additional roles below read-only to indicate when an account is Suspended or Blocked.
The identifier has been conflated with the sequence of hierarchy and level of authority. They are 3 distinct elements, and should be treated accordingly.
- Identity is for uniqueness (schema)
- Hierarchy is for ordering (presentation)
- Authority is for permissions (logic)
The key is to separate these 3 elements.
This is something you want it baked in from the start, not an afterthought. If you don’t, this single identifier will seep into application code and logic will be layered throughout the application to handle this.
What we have above are key-value pairs. There is nothing to describe their purpose so it must be inferred by the application. Therefore if you want to change either the key or value, something will break.
This is a fragile design. This is a bad code smell.
So, how do we avoid this issue? – plan table design accordingly and be intentional that each field has a specific responsibility within entity:
- Avoid
ORDER BY IDand addDisplayOrderfor presentation - Avoid
WHERE Description = ...and add aKeyvalue for logic to reference and avoid dependencies onIDorDescriptionfields - Avoid
WHERE ID > ...to check privileges and add a foreign key to aRoletable, or a Bit Mask referencing permissions
Being intentional is key. Once you’ve implemented that design, the pattern will be clear to other developers, and it’ll make those entities much easier to maintain, less fragile, and more flexible.
As a bonus – and adjacent to this issue – is sequencing. It’s worth mentioning that with any sequence it’s good practice to leave them open ended, and with growing room. In the example above, instead of 1…5, we could have used 10…50. Now we can easily add a role which slots in at 45 for our Super User, or drop Blocked in at 3 and Suspended in at 6. (Does this sound like Top of the Pops?)
In summary, the lesson is that each field should have its own distinct purpose when it comes to sequencing. The identity is there for the table structure. If you want to order records for presentation, that should be its own field too. If it were a hierarchy like this role example, that’s another field.
I’ve found that over time, developers avoid these systems as they become unwieldy with layers of complexity added to work within its constraints. The application is rightly seen to be fragile and folks don’t want to touch it. In reality, it’s held back by a bad design. This lesson should help move the needle from fragile to robust.
Andy Brownsword 