The modern database paradigm is defined by relations, so of course that’s what falls apart as soon as you get an app into production.
Only poorly designed data base schemas "fall apart".
If you find yourself making all fields optional or needing only many-to-many relationships, that's a sure sign that you already did something wrong when you designed the data base.
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? And even if you didn't, you did design a data base structure flexible enough to accomodate the natural evoluation of the app, right?
Please don't confuse the limitations of 3rd and 4th normal form with the shortcomings of data bases built with them.
> If you find yourself making all fields optional or needing only many-to-many relationships, that's a sure sign that you already did something wrong when you designed the data base.
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.
For what it's worth, the "what falls apart" line was intended to be tongue-in-cheek.
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.
The analysis for the app was incomplete (for whatever reason). I'm not looking to place blame, I'm just saying that a well designed data base by definition must handle the wear and tear of the app.
Some of the worst crap I ever had to maintain was originally considered a success because it met its original deadline. Sigh.
> I'm just saying that a well designed data base by definition must handle the wear and tear of the app
Impossible. This is pure theory and has no ground on reality. Name me one database that was designed to handle Sarbanes-Oxley needs in the last decade.
Business requirements change, and so does the app and the database.
Tell me how you would deal with this situation in your current database designs: A few years ago, a born male person here in Brazil got his sex legally changed to female (also had a sex-change operation). So, s/he's a female for most of legal concerns, but s/he's got the genome presented on males, which means:
1) He needs to be a "female" on your employees records;
2) She needs to be a "male" on your company health insurance records;
Sorry if I sounded abnoxius, I can't argue softly in English :p
Something a little like this happens on our elections app: politicians have a name and party, but sometimes run in an election under a different name (eg. "Robert" runs as "Bob") and party (eg. Joe Lieberman lost his primary but won in the general as an independent). And sometimes they're doing this in multiple elections at once.
Impossible. This is pure theory and has no ground on reality.
I have apps still running well after 15 years that would beg to differ.
You example is clearly an extreme outlier.
I never said that the business requirements don't change. That would be silly. I simply said that a rigorously designed data base structure should easily handle the changes you expect in your apps.
Did you build in audits? Transactions? Date effectivity? Sequentially numbered keys? Multi-part keys? I can go on and on, but you get the idea. I've made a career rewriting data base applications where the original designer failed to anticipate the simplest of new requirements.
Your English is fine. And you don't sound obnoxious. I just found your "no ground on reality" a bit at odds with my opinion a millions lines of code later.
I think all the Lightweight (agile) methods in sw development put emphasize in being able to prototype ideas easily.
DB simply don't do that.
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.
Only poorly designed data base schemas "fall apart".
If you find yourself making all fields optional or needing only many-to-many relationships, that's a sure sign that you already did something wrong when you designed the data base.
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? And even if you didn't, you did design a data base structure flexible enough to accomodate the natural evoluation of the app, right?
Please don't confuse the limitations of 3rd and 4th normal form with the shortcomings of data bases built with them.