When I have required model relationships, how do I guard against errors?

Question

I have an application with a lot of database relationships that depend on each other to successfully operate the application. The hinge in the application is a model called the Schedule, but the schedule will pull Blocks, an Employee, a JobTitle, and an Assignment (in addition to that, every Block will pull an assignment from the database along with it as well) to assemble an employees schedule throughout the day.

When I built the app, I put a lot of emphasis on validations that would ensure that all of the pieces had to be in place before everything was saved to the database. This has worked out fantastically so far, and the app has been live and pounded on for almost 6 months, serving approximately 150,000 requests a month with no hiccups or errors. Until last week.

Last week, while someone was altering a schedule, it looks like the database erred, and a Schedule was saved to the database with its Assignment missing. Because the association is called in every view, whenever this schedule was called from the database, the application would throw an NoMethod error for calling on nil.

When designing an application in the way that I state, do you guard against a possible failure on the part of the database/validations? And if so how do you programatically defend against it? Do you check every relationship to make sure that it is not nil before sending it to the view?

I know this question is awash in generality, and if I can be more specific in what I mean, please let me know in the comments.

Answer 1

I would recommend adding database-enforced foreign key constraints and wrapping important groups of operations into transactions.

If there is a foreign-key between Schedule and Assignment somewhere, a database-enforced foreign key constraint would have prevented the errant insert. Additionally, if you wrap the particular action in a transaction, you can be sure that either the entire stream of inserts/updates/deletes happens or fails, reverting to a clean state.

Answer 2

If it's something that must be true in order for the app to function, that's really what assert()s are for. I've barely ever used Ruby, but I imagine it must have that concept. Use them to enforce preconditions in various places throughout your code. That combined with sanitizing and validating your external (user) inputs should be enough to protect you. I think if something goes wrong after that amount of checking, your app is righteously allowed to crash (in a controlled manner, of course).

I doubt the problem you're experiencing is a bug in your database. More likely there's some edge case in your validations that you've overlooked.

Answer 3

In addition to your validations, and adding some database constraints as mentioned in other answers, you might also run a background job that periodically sweeps the database looking for orphans.

When it finds one, it cleans it up (if possible), or deletes it, or just marks it inactive and sends you email so you can look at it later. Depending on the amount and nature of your data, once a minute, once an hour, once a day...

That way, if bad data does get in despite whatever safeguards you have in place, you'll know about it sooner rather than later.

Answer 4

I'll argue the non-conventional wisdom on this. The constraints you describe don't belong in the database, they belong in your OO code. And it's not true that "the database erred", it's unquestionably true that the application is what inserted improperly validated data.

When you start expecting the database to carry the burden of these checks, you're putting business rules into the schema. At a minimum, this makes it a lot harder to write unit tests (which is where you should probably have caught this in the first place; but now is your chance to add another test.)

Ideally, you should be able to replace the RDBMS with some other generic data store and still have all the functional logic properly active and unchanged in the appropriate other places. The UI shouldn't be talking to the DAL much less dealing with database exceptions directly.

You can add the additional database constraints if you want, but it should be strictly as a backup. As you can see, handling database structural errors gracefully (especially if the UI is involved) is a lot harder.