could you elaborate on 40,000 tables DB? I want to learn what could be valid reasons for that?
* Most simple things like a "Person" were multiple tables because you had to include audits and historical changes for each field
* A "Person" wasn't even all that useful because it included guests or other fairly transient entities like vendor contacts so you had an explosion of more tables as you classified roles into "Student", "Faculty", "Employee", etc... (many with histories as above).
* Addresses and other non-core demographic information were usually sharded into all sorts of categories like "primary", "parent's", "last known good", "good for mailing", etc... (more histories, etc...)
* All coded information like label types such as "STUDENT", or "MAILING" were always handled as separate validation tables with strict FK constraints and usually included extra meta information like descriptions and usage notes within parts of the system.
* Each functional sub-system (HR, Payroll, AR, AP, etc.) had its own dedicated schema.
* All external jobs, processes, and external integrations were configured separately.
* All enterprise integrations usually had a whole a dedicated schema for configuration.
* Most parts of the interactive web UI were database driven (Oracle's Apache mod PL/SQL) with many templates and other components stored in large collections of tables.
It probably feels weird for devs to drive the UI off the db but it's just Wordpress by another name.
When I rewrote the application I just hardcoded the form fields, nobody should need to do a database migration to change an otherwise mostly static form.
Would it be "better" if they had one table with json/xml/whatever and handled schema in code?
They made a trade-off they found right. When they hit the limit with their approach, they even implemented their own DB (S4/Hana) to support their system.
edit: I tell a lie, I separated the forums and wordpress databases on a website I run.
Thankfully, our product has customer specific use patterns that we've been able to manage/plan/predict peak load for and what not. Of those 300, a random subset of 20-30 would be 'busy/critical' at any given time, and the others can easily tolerate a delay as migrations + schema changes lock and manipulate things.
The alternative would be we both have access to the same tables with a permission layer to grant access to row.
Both choices have trade offs but if company makes a mistake and I now have access to your rows? Seems easier to control access at the table layer rather than the column layer.
Or... you can just split things by tables. Or even shard by databases where I don't have access to your database and vice versa.
doing stuff in the application and leaving everything in one database/schema is an option... but don't think you aren't making trade offs and leaving open possible issues by not taking the more comprehensive option like sharding.
And that's just one question to ask. Another is what about upgrading the database and segregating customers. can't do that if everyone is on the same database/schema. What if a customer doesn't want to be updated or upgraded? Much like companies paying for Windows XP support because stuff they have relies on the older version of software?
"where user.id = 123" is a simple solution that quickly becomes more complicate to put it mildly.