Improve data integrity by enumerating constants in the database

[jonasraoni]

Describe the problem you would like to solve
Several key fields in the database are related to hard coded constants in the code, which breaks the possibility of adding some integrity checks.

Describe the solution you'd like

• Identify and ensure all of such fields (e.g. submission.stage_id, user_grous.role_id, ...) have a properly linked entity in the database.
• Ensure the primary key of the table isn't auto incremental.
• Add an extra name/identifier field to improve the debugging experience when filtering/listing data, also ensure the field has an unique key.
• Add a migration script to break the process in case the field has invalid values.

Who is asking for this feature?
Developer

[asmecher]

@jonasraoni, are you proposing to have a set of constant tables, e.g. roles, with a list of values equivalent those defined in PHP? Presumably the PHP constants in that case would remain (and stay synced)?

[jonasraoni]

Exactly, that's how I've been dealing with such hard coded enumerations/types.
Besides helping with the integrity, it's also easier to grasp what the data mean (and also the possible values) by watching the database itself.

In general, I also prefer to use strings than numbers, so when a constant disappear I won't stay with holes, but it has some drawbacks.

[asmecher]

I'm not convinced that adding constant tables in the database is going to improve things enough to justify the work. We'd need a lot of them in order to do this comprehensively -- roles, workflow stages, file stages, assoc types, etc. etc.

There are a couple of other options we could consider:

• Database enums ((counter-argument)[http://komlenic.com/244/8-reasons-why-mysqls-enum-data-type-is-evil/])
• String constants (less performant than numeric but no big objection here)
• Converting bitfield (hex) constants to integers (we're already doing this)

We don't tend to run into data integrity problems with these in practice, so I think the issue is more around developer orientation.

I'd like to avoid bikeshedding on this, though! It's way down our dev priority list and would take a lot of work to apply throughout. And a partial application is maybe worse than the original issue.

[jonasraoni]

Yeah, I totally agree with the priority! I'm creating some issues just to share notes publicly/discuss, just a reminder for future cleanups.