Rebuilding the model
Yesterday I decided to stay with MySQL, but today I face something a little more creative: What should the data model look like?
So far today I've collected a list of all the information I would like to store, and marked out all the information that is currently stored. It doesn't look like I have to add a lot of information, but I'm certainly changing how it's stored.
In the current version of the application a whole lot of the information is stored in static configuration files.
For example, the concept of a "Morph Rule" is a configuration variable.
So a case type is changed in accordance with a "Morph rule".
When this was designed it was not clear how often these things would change or need tuning, so it was hard coded in a way that would make it very fast to load at start-up, and very easy to access while running, but very impractical for the application to change.
Changes are currently done by manually editing .pm files.
This would have been fine if the changes were few and far between, but I find myself editing this "configuration" a whole lot more often than the design documentation of the application indicates necessary. In the past, these things "never" changed, but this business is becoming more and more dynamic, and changes more and more frequent.
Clearly, this has to be on-the-fly changeable by administrative users. If they have to toss it across my desk every time there is a change I won't be able to do much else.
Another thing that's very wrong about the existing model is the lack of foreign keys anywhere.
This is my doing of about a year and a half ago, and it shows my lack of experience with complex databases. My reasoning behind it is that if something goes wrong it's a lot less complicated to untangle the mess if you're not constrained by a bunch of foreign keys. Since then it has become clear to me that in such an emergency you can simply suspend foreign key enforcement.
Oh, the sins of the past... I'm just glad I have a chance to undo them. With what I've learned since then I'm sure I can build a better model.
MySQL (or variant), InnoDB, and foreign keys, all supported by some nice DBIx::Class as model for a quick and snappy Catalyst application.
Yep, this is starting to taste like a real project now.