3 ms·
My simple rule for databases is that if you are not actively querying on it, it doesn't need to be a separate table or column. Use the YAGNI rule here and you'l
by jillesvangurp 3y ago
My simple rule for databases is that if you are not actively querying on it, it doesn't need to be a separate table or column. Use the YAGNI rule here and you'll be better off. The classic example here is persons that have addresses and phone numbers. Most applications have no requirements to query on most of that: street name, postal code, phone number, etc. Less tables and columns mean simpler joins (or better, no joins at all). Any structured data that you don't query, just store it in json form in a blob and simplify your schema. Anything that actually requires complex querying, consider using a proper search engine. Or extract it to a dedicated field with some index on it that actually helps you querying effectively. The golden rule here is that if that query is connected to user input you probably need some notion of ranking, fuzzy searches, etc.
Either way, everything gets easier with a simple schema. Faster query and insert performance, easier to reason about when doing transactions, easier to maintain, etc.
- mixmastamyk 3y agoMaybe, but not sure you’re getting much from that trade. If you’re not querying often, then performance shouldn’t be an issue. Modern hardware/dbms can definitely handle a couple of joins without blinking. So you’ve mostly lost flexibility. Also having to redo later will wipe out the time savings of many, many simplifications. Not to mention doc and teaching reqs you’ve added to new devs.
- eddd-ddde 3y agoI think they mean that adding a bunch of uncommon columns to your user table is just gonna decrease performance of common queries. In those cases I like offloading uncommon columns to separate tables that are joined only when needed.
- jillesvangurp 3y agoI've fixed a few systems by simplifying their schemas. Over-engineering at the database level can lead to a lot of issues beyond just performance. The extra complexity causes a lot of overhead with technical debt, lots of ceremony around changes, etc. Performance issues are usually a good indicator of a team that is a bit out of their depth. Usually goes hand in hand with over engineered data models being mapped 1 to 1 to a table structure.
- zo1 3y agoAnd I've fixed a few systems by properly engineering their schema on the database level. Every single system I've encountered that had issues with scaling, performance, tech debt, etc have all been due to badly denormalized (and broken) database schemas, with no one understanding who did what why or what piece of code owns what part of the schema. You start getting arcane knowledge and little fiefdom silos and grumpy grey-beards with inflated savior complexes that are the "goto person" despite the whole thing being ridiculously simple.
- mixmastamyk 3y agoIndeed—never heard proper normalization described as "over engineering" before.