> representing the things you absolutely dont want another part of your app doing via FKs is...pretty normal
"Normal" has never been compelling in a world where most software sucks and most projects overrun their budgets.
> If you are arguing it spreads out your business logic into multiple places, that's fair, but so does... a lot of things
Stop doing those things too!
> Databases with decent fks and constraints have a tendency to outlive the apps that build atop them
This is oft repeated, but I think there are some flaws with it:
1. I'd ask the question: is that because those apps sucked? It's possible to have a mediocre database and then an even worse app on top of it; in that case it wouldn't be surprising if the app itself died first.
2. Did the next app use the existing schema as-is, or did it adapt/migrate the schema to a different form that worked for that next app? There's a big difference between saying "this data turned out to be useful for other purposes" (of course!) and "this data representation turned out to be so perfect and elegant that we kept using it"
3. Do you have an overly narrow definition of "apps" here? I'd argue if six teams are all doing their own things with the database -- some writing code, some building views, some hooking into it with Excel and PowerBI -- then I'm not overly interested in the fate of the "OG app" that the database was built for. If that one dies, you still have five other "apps" using that database. We're not talking about a schema sticking around because it's so useful, we're talking about an entrenched, locked-in tech that nobody can change because it's too costly. That's a predictable symptom of a bad representation! That's exactly what happens with bad code! Good systems can handle change in isolated parts without breaking things for everybody else. Database schemas are so entangled with themselves, and SQL is so bereft of proper abstraction, that they can't change easily once they have many users.
1. no, its because they were so useful they expanded beyond their original containers.
2. The cluster of apps that then lived on the top of the schema (the four times I have worked on versions of this) mostly just extended things occasionally when they needed to, but oddly mostly did not and just read and wrote to the same schema; this required more coordination and slower development release cycles, but not really a problem for these companies.
3. Somewhat fair, but the real world and real shippable software (which you seem interested in) has a lot of shared concerns, a lot of trouble when you ETL or use APIs for everything instead of just... here's the data. Short term that seems entirely reasonable instead of creating contracts everywhere and spending the time and effort understanding more and more of the changes introduced into smartly decoupled systems.
In a nutshell you're right, SQL makes it easy to mix a bunch of concerns and ship them and let them grow organically. Which I would argue almost any software structure does, ESPECIALLY with data, except now you dont even get to know where all the bodies are buried.
Edit: for instance, I have worked on/converted many useful databases that were created in the 1970s, before I was born. Can you imagine a stateful application that you could easily understand and utilize all the data that's been ingested on for 50+ years?
> I have worked on/converted many useful databases that were created in the 1970s, before I was born
"Converted" makes it sound like you found a database with lots of useful data and then developed a different schema for it that was useful for your own needs. This is kinda my point: when people say the database outlives its application, they mean the data itself. That's somewhat irrelevant to the discussion of how to properly architect database schemas.
Did you find relational database schemas from the 1970s that made you go "ahh, this is perfect for what I need!"?
> Can you imagine a stateful application that you could easily understand and utilize all the data that's been ingested on for 50+ years?
I don't think I understand this question. Yes, I can certainly imagine an application that remains understandable, relevant, and maintainable for 50+ years. I hate the current expectation that apps and code have shelf-lives in the single-digit years. I hate the term "legacy code". These are immature attitudes from the "move fast and break things" eternal startup crowd whose only goal is a profitable exit. That is not "engineering", it's apathetic planned obsolescence, and everyone who participates in planned obsolescence should be ashamed of themselves. Code that could reasonably last 50+ years should be your default if you call yourself a "software engineer". Otherwise your title should be "YOLO coder-bro".
But isn't that the opposite of letting every random team have unfettered access to the internal private details of a database and entrenching every little design decision under a mountain of unmaintainable SQL? "Growing organically" sounds great until you realize you're just describing cancer. It took life trillions of tries before "growing organically" ended up with something half-passing as kinda-elegant if you squint. Do you want bridge engineers to let the design of a suspension bridge "grow organically"? Will that bridge last 50+ years?
"Normal" has never been compelling in a world where most software sucks and most projects overrun their budgets.
> If you are arguing it spreads out your business logic into multiple places, that's fair, but so does... a lot of things
Stop doing those things too!
> Databases with decent fks and constraints have a tendency to outlive the apps that build atop them
This is oft repeated, but I think there are some flaws with it:
1. I'd ask the question: is that because those apps sucked? It's possible to have a mediocre database and then an even worse app on top of it; in that case it wouldn't be surprising if the app itself died first.
2. Did the next app use the existing schema as-is, or did it adapt/migrate the schema to a different form that worked for that next app? There's a big difference between saying "this data turned out to be useful for other purposes" (of course!) and "this data representation turned out to be so perfect and elegant that we kept using it"
3. Do you have an overly narrow definition of "apps" here? I'd argue if six teams are all doing their own things with the database -- some writing code, some building views, some hooking into it with Excel and PowerBI -- then I'm not overly interested in the fate of the "OG app" that the database was built for. If that one dies, you still have five other "apps" using that database. We're not talking about a schema sticking around because it's so useful, we're talking about an entrenched, locked-in tech that nobody can change because it's too costly. That's a predictable symptom of a bad representation! That's exactly what happens with bad code! Good systems can handle change in isolated parts without breaking things for everybody else. Database schemas are so entangled with themselves, and SQL is so bereft of proper abstraction, that they can't change easily once they have many users.