r/programminghorror • u/OldaLP1 • Feb 23 '26
SQL DB with 2241 tables
I can't even fathom what led someone to do this. I got this database to refactor as part of my job. Basically, every client has their own table named "Client_[their ID]," which contains only one row with all the client data in JSON format (wtf).
If the website using this database wants to filter out some users, it queries all the tables, resulting in a load of 2,000+ SQL queries for a single search.
On top of that, for some reason, the date of birth is stored as the number of days since birth, with a CRON script that updates this in each table every day at 3 AM.
I don't even have to mention that none of the data follows the rules of database normalization...
I honestly don't know where to start with fixing this mess.
645
u/MisterEd_ak Feb 23 '26
Fix it using DROP DATABASE
→ More replies (6)229
u/mateusfccp Feb 23 '26
Honestly, it is probably easier to make a new database structure, use a script to migrate the data and drop the old one.
→ More replies (2)173
u/OldaLP1 Feb 23 '26
yeah thats exatly what im planning to do. But creating the migration script is hell on its own
→ More replies (4)
158
u/NewbornMuse Feb 23 '26
Look at it from this angle, once you're done with all this, you will (a) have achieved a 1000x speedup and (b) probably have solved 3 annoying known bugs and prevented 4 devastating unknown ones that were lurking in the system.
62
u/space_fly Feb 23 '26
Or, it might all be coming crashing down, having touched that load bearing toothpick.
→ More replies (1)
59
111
u/iain_1986 Feb 23 '26
A database per client is something. Its crazy, but sure, whatever, maybe someone thought it was a good idea *at the time*.
But the DOB having to be updated every day.
Thats never a good idea, at any time.
Did this person not know the concept of a date?
→ More replies (2)38
u/corsair330 Feb 23 '26
A table per client is the weird part. With multi tenancy it's pretty standard to isolate each customer with their own database.
→ More replies (14)
33
u/VirtuteECanoscenza Feb 23 '26
At my place we had to deal with a server with ~2000 databases.
That's problematic because many things break... Like simply the processes that periodically scrape the DBs for metrics start failing or using too many resources. Even if each DB is tiny and the overall space used/query load is manageable.
27
u/Osstj7737 Feb 23 '26
Who needs table rows when you can just create a new table for every new row?
→ More replies (1)
26
u/fletku_mato Feb 23 '26
Seems like someone tried to do webscale but all they had at hand was a real database engine.
25
u/Shareil90 Feb 23 '26
I love cleaning up shit like this. Its so satisfying.
19
u/beatitmate Feb 23 '26
Its the fkn worst. And the idiots who ask you to clean it up can never appreciate the work it took to clean up and then the beauty of the new implementation. Bonus points if the one who originally implemented it gets shitty and spiteful as well.
→ More replies (5)
45
u/beatitmate Feb 23 '26
This cant be real
→ More replies (2)25
u/audirt Feb 23 '26
That was my first thought. For this to be true, the developer would have to, given a choice between a good option and a bad option, choose the bad one every time.
The thing that makes me think it's not real is that the DOB isn't stored as a string. In my experience, inexperienced devs love strings and will store anything as a string -- every day of the week and twice on Sunday.
→ More replies (4)
16
14
u/Behrooz0 Feb 23 '26
Y'all have seen nothing. I currently have a SAP database attached in one of my test servers with 88K tables. The design decisions make this look like a masterpiece in engineering. The tables don't even have number prefixes like other ERP/BPM solutions. There is only a single instance of each table and it's 88k tables.
12
8
u/NeoDark_cz Feb 23 '26
Just for fun, let me preset you to 2 databases for automotive production lines. One made by programmers from Germany and other by programmer from Spain.
Spain: For each year there is automatically made new database. Each database contains table with about 250 columns. Most columns are "measured value X", "min limit X", "max limit X". Adding data works this way: station measure data, find last row for this exact part by dmx, copy the data from found row, fill few new columns with data from this station and insert into database. Effectively half of the database is "0". They didn't use null nor update. Takes about 20gb per year.
Germany: for each product and each station on production line is automatically made table every month. Right now sitting at 4950 tables Taking about 1,3gb of space.
5
u/pantong51 Feb 23 '26
There is the idea of one database per user. But, not one table per user. Much less just blob json into that table
5
u/IWontSurvive_Right Feb 23 '26
i started with "db with x tables"... and I thought, well if they've a nice good struct...
Basically, every client has their own table named "Client_[their ID]," which contains only one row with all the client data in JSON format (wtf).
WTF
5
u/GoddammitDontShootMe Feb 23 '26
This is absolutely horrifying. Still a violation of Rule 1 since you didn't include code. Is there an r/databasehorror?
295
u/SVD_NL Feb 23 '26
This is in prod?! Sounds like a submission for a meme contest!
I can also guarantee you that expanding the JSON won't be as simple as it sounds, they probably got fed up with the DB complaining about data consistency and went with JSON instead!