3 Rules of Database App Aging

I wonder if you could use an organization's DB to predict when they'll be too big to function.

I've seen too many places where reasoning similar to that of 2 results in developers prematurely punting to quick and dirty hacks to get a trouble ticket closed faster. It may not be possible to get by with anything but a one-off conditional, especially in tax calculation , but I worry about the claim that it is inevitable. It provides a justification to not look for more maintainable alternatives first.

The same thing with 4. The only thing that bothers me is the sense of inevitablility. There are places where specific cases need to be dealt with in code, and it is an entirely different argument of how they should be dealt with once that much is assumed. But the inevitability bothers me, if only because I have run into several cases where things were claimed inevitable, and upon closer inspection caused large amounts of pain that could have been avoided if the existence of alternatives was considered.

The weird thing about that is in some organizations (like my own), databases are considered easier to change than the software. Hence the push by management to move the logic out of the view or controller layers and into the "model" (i.e. use of stored procedures, constants fetched from the DB instead of in config files or headers, etc.) Where I work, they only pay one person to do database work, but three to write code. Yet the DB guy is always crushed by work, since he gets change/reporting requests all the time.

I'm a big proponent of enforcing business rules right into the data model. Constraints such as not null, reference constraints, or even conditional unique constraints (yes, there's such a thing!) should be enabled wherever possible.

Just remember, your app will not be the final app that makes use of the data. Your app comes and goes, along with many other apps written by many others. But the data lives "forever."

On and on like that. In fact, there were entire internal courses to teach these kludgy schemes, and they had fancy sounding official names. (Yes, entire week long courses, not just 1-day workshops.)

If a field needs to be optional, then it needs to be optional. Remove the NOT NULL and change your validation code.

How else can you plan for this?

'Get it right the first time' is just wishful thinking.

A nice sentiment, but once a transactional system is in production, it becomes very difficult to change, IME. The company I work at has a legacy schema we've been wanting to retire for years, but we haven't been able to due to number of (often hidden) constraints and overall complexity. I realize this fact suggests a badly designed system, but much of real world database work consists of dealing with poorly designed legacy systems.

DBAs aren't the only hard-nosed folks involved here.

I think we may be in violent agreement: I also think kludges and hacks are unprofessional. In the post I'm writing about my experience with the way that models are exposed as imperfect over time and they're fixed to deal with it. As much as I gripe about exceptions and outliers, capturing them is a big part of design.

Mainly because the actual Model code changes so much in between someone running the "rails" command and the "mature app" stage - the validation logic and callbacks being the main culprit.

Eventually, it's easier to bootstrap the schema from schema.rb (rake db:schema:load IIRC) than it is to maintain migrations from a zero state.

Then you've just got to make sure you keep up with the deltas :)

Not really. Business rules change because the nature of business changes. Over-engineering databases to account for future changes takes a lot of time and effort. If the company does not sell outside of US and does not have plans to sell outside of US anytime soon, I would not spend a week implementing Norwegian, UAE, and Indian addressing and tax/tariff system.

When I designed the payroll system for my company, I took into account that hourly employees can get promoted and become salaried. This happens once a quarter or two and the system can deal with it. Now if a salaried employee wants to become an hourly employee, my system cannot auto-calculate the change in their vacation days etc. It would require a considerable amount of time for me to build that functionality in. However, it is possible for this situation to happen but in five years, it has only happened once. In that instance, I manually edited the records and documented the entries. So does that mean I designed the database schema poorly? No it just means I did not want to make it so rich and extensive that it can handle every remote case, just most of them.

Surely you must have known about the guy from Guam or the one living in Virginia but commuting to D.C. when you analyzed your app, right?

Nope. For most of the apps I've worked on recently, we're pulling incomplete datasets that are augmented from other, slightly-incompatible sources and manual entry and must be designed and built to meet the deadline for a newspaper story. So I do the best I can to capture the data and then change the schema as these uncaptured/new requirements make themselves known.

In HN there are often questions on How to manage schema changes in the DB. I know there are tools for this but the mere fact, that is still a common question is an indication of the inflexibility of database schemas.