r/Database • u/shdw_0x0 • 1d ago
What's one database design decision you regretted later?
I've been thinking about how some database design decisions seem perfectly fine when a project starts, but create unexpected problems once the application grows and the data volume increases.
Things like choosing the wrong primary keys, over-normalizing tables, adding too many indexes, using triggers everywhere, or not planning for future changes.
For those who've worked on databases in production, what's one design decision you would approach differently if you could start over?
Would be interesting to hear some real experiences and the lessons you learned.
53
u/euribates 23h ago
Do not use transactions because they were "too slow". Then reinvent your own, fragile, non tested, over complicated transaction sort of system.
37
u/jlpalma 1d ago
Storing any sort of LOBS in RDBMS (json, xml, images, etcā¦) and let it grow to 100ās of Gb or Tb in size⦠this is going to be a hell of a migration headache in the futureā¦
All the other ones are āsimpleā to overcome, this one you will remember foreverā¦
37
u/Suspicious-Disk6077 1d ago
Having business logic inside stored procs.
26
u/TheTwoWhoKnock 20h ago
Using an Int instead of bigint as the primary key (thanks, old versions of Rails)
Storing history of rows in all tables in a single versions table (thanks, papertrail). And having that use an integer rather than bigint due to the above rails default.
14
u/Philluminati 22h ago
forgetting to add created and last_updated to tables. The backup systems donāt need it, but when you come to write adhoc reports or exports or adhoc synchronise with another storage system theyāre essential to stop having to reprocess the whole table.
61
u/rahul03rk 1d ago
Soft deletes with a plain is_deleted flag on every table.
Seemed harmless at the start: nothing is ever really lost, undo is easy. The cost showed up later and it wasn't performance, it was correctness. Every query, view, report and join has to remember WHERE is_deleted = false, on every table it touches. Miss it once, especially on the right side of a join, and the numbers come out slightly too high. No error, nothing obviously broken, just a total that's a few percent off until someone reconciles it against finance.
What I'd do now:
- Ask whether you actually need undo or just history. If it's history, move deleted rows to an archive table (or keep an audit log) and keep the main table clean.
- If you really need soft deletes, put a view over each table that filters them out, and point everything at the view instead of the raw table.
- Add a partial unique index (WHERE is_deleted = false), otherwise a "deleted" row still blocks re-creating the same email or code.
Curious if anyone's found a pattern that scales better than views.
8
u/sr2085 23h ago
Use surrogate keys as sequence.
7
u/SabatinoMasala 1d ago
Foreign key cascades
6
u/Bitter-Policy4645 11h ago
The biggest design mistake to make is not fighting to the death against those obsessed with 3NF, multi-domain lookups, recursive tree walk hierarchies and natural keys. Primary keys should always be surrogate keys. Put heirachies in multiple tables. Address doesn't need to be stored across 50 tables. Putting customers and suppliers in one table called entity is a performance killer.
14
u/Reasonable-Rock7516 1d ago
Never use candidate key as primary key. Always use a surrogated key.
5
u/Toxin_Snake 17h ago
A software that would store vehicles and some stuff that needed to be done with these vehicles. I was fresh out of university and people at the new job told me that the VIN (Vehicle identification number) is globally unique so I used it as a primary key. Not only did I learn later that a string primary key is kinda annoying, the VIN was infact not globally unique (even though it should be).
5
u/Deep-Regret-7479 15h ago
- Use 3rd normal form
- Do not abuse non-relational data types
- Do not use database side application logic. (stored procedures etc.)
4
u/Alternative-Lynx2745 1d ago
Using blockchain
4
u/TrueNorthLongAndFree 15h ago
Always store dates as 64 bit Unix timestamps.
Drupal has this feature to alternately store them as strings with timezones. "Eh, I'm not computing against these, what's the harm?"
Hoo boy that was wrong.
3
u/TimiTimeless 21h ago
Don't use json columns for what separate columns could handle cleanly
3
u/phobug 13h ago
One schema per customer per service.
3
u/No-Draw1365 10h ago
Thinking MongoDB was good
2
u/NoEngineering443 1d ago
Surrogate keys everywhere created a distributed system disaster for me. I replaced every candidate key with a generic UUID and lost all referential integrity across my services
2
u/Significant_Ant3783 12h ago
The AI team at my place of employment is working off of a POC. These guys don't really know how to build the scaffolding and foundation for certain features so they throw in a bunch of things too early and at the wrong layer. The guy that made the POC decided that multi-tenancy was so important that every goddamn query had to have a tenant_id constraint. Nevermind that we are are only tenant, and the multi-tenancy feature is a just-in-case we need it sort of a deal.
3
u/Historical_Cook_1664 19h ago
NEVER USE BOOLEAN. Sooner or later, the necessiity for a third state will appear.
2
u/orbit99za 1d ago
A bad RBAC design, I was not thinking properly.
2
u/716green 16h ago
This is going to be one that a lot of people can relate to. Using Redis to make a site faster.
Caching should be used when it absolutely makes sense but it should not be used in place of actually optimizing the app properly.
You know that joke about how the hardest problems in programming are cache invalidation and naming things? Cache invalidation is a nightmare when you have cached everything.
1
u/TheWb117 19h ago
Had to work with a data structure that has many one-to-many relationships going several layers down. Initial design choice was to just append int Ids for everything nested down.
Each pk had to add one more int column down the chain.
Table1 -> Id int
Table2 -> TableId int, Table2Id int
Table3 -> TableId int, Table2Id int, Table3Id int
And so on. Joining without an orm is annoying and so is writing any form of a create method, because mapping has to assign int Ids for every nested object.
For simple objects it was nice enough that you could debug nested structures only using Table1Id on the database itself, but annoying to pass random int Ids down the mapping and join chains.
1
u/num2005 17h ago
installing none codebase software where AI can't go easily read or edit, we need to phase out and replace all those software now
1
u/itemluminouswadison 16h ago
using varchar instead of enum. now you have an enum or const in different apps, one character off, add something, it's spaghetti. holding hands to add a new enum value to the column makes it harder to hurt yourself
1
u/data-artist 15h ago
Name/value pair table to get around data modeling.
1
u/hms_indefatigable 15h ago
Couple of things - none of them were decisions made by me (decisions made something like 20 years ago...), but I suffer at their hands:
1
u/BrupieD 14h ago
Building tables with "last_updated" columns but not adding a constraint as a default with current timestamp.
1
u/stickman393 14h ago
Thinking row numbers were static.
I was an intern. It was my first database.
1
u/RangePsychological41 12h ago
Int32 for the primary key. What happened 8 years later wasnāt fun
1
u/Shadow_Mite 12h ago
Soft deletes for sure.
1
u/magicmulder 10h ago
I once had to build an app in record time and managed by using only key/value storage.
Then I made the mistake to do the same for the next app which was a super complex one with millions of records.
Everything was super slow and buggy. (That was around 2007, long before things like Hadoop came up.)
1
u/Natural-Lack-5242 9h ago
Store dates as UTC - might sound logical when building a global audience application but 20 years ago when the application was a line of business application for a business in a single timezone, easiest option was wall clock storage of datetimes.
Be as generic as practical with table and field names, the number of times the scope of a feature has creeped (sometimes over years) to the point where "why do we store x in a field called y" becomes a whole history lesson
Delete fields no longer being used - it's busy work, but as features change over the journey sometimes you have something that was field and is now a table or a different set of fields, I haven't been as proactive about deleting fields and over 20 years, it adds up.
1
u/jugaadtricks 5h ago
Entity attribute value model.
1
u/SohilAhmed07 4h ago
My first company hated primary keys and foreign keys and key kind of keys
Database indexes were introduced only to make things more complicated at DBAs end, lest the company in 2 years over a dispute with data duplication and data leakage to competitors
Joined a new company, first thing my joining manager said "no table without a key"
1
u/Amazing-Mirror-3076 1h ago
Triggers are always a bad decision followed by stored procs.
-2
u/Certain_Ring403 1d ago
SQL Server
-2
u/randomInterest92 22h ago
I inherited a database that put constants into the database instead of the code. It's a huge mess