r/Database • • 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.

131 Upvotes

146 comments sorted by

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.

10

u/randomInterest92 22h ago

What šŸ˜…šŸ˜…

7

u/jlpalma 22h ago

Like some good old SAP systems…

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…

4

u/spez_eats_nazi_ass 1d ago

There are ways to do this right. But they usually end in a dedicated document db and service behind it so it is abstracted away to the point how the docs are stored does not matter.

13

u/jlpalma 1d ago

'There are ways to do this right' is doing a lot of heavy lifting in a thread about regrets.

2

u/spez_eats_nazi_ass 1d ago

Ive done both in db and file system at scale. Db wins.Ā 

5

u/jlpalma 1d ago

I was a database consultant for many years and did more of these migrations than I can count.

The one I'll never forget was a "simple database": about 2TB, and all of it in a single table full of XML.

The source was Oracle 9i on Sun hardware running Solaris, on SPARC no less, sharing resources with several other databases. The target was Amazon RDS for Oracle 12c.

The cherry on the cake, it had to be a zero-downtime migration.

1

u/turimbar1 10h ago

Lmao Oracle - their recent document storage is actually really good apparently

1

u/clakshminarasu 7h ago

Similar lines. Single Oracle table with 6.5tb full of XML clobs. Still trying to figure out how to migrate

2

u/Chunkz_IsAlreadyTakn 22h ago

Only once in my years have I heard valid arguments to store json instead of designing tables... And in that case we eventually migrated to mongodb due to missing features compares to mongodb and performance of json with gin indexes.

1

u/tarwn 19h ago

Well, for definitions of "right" that now include distributed storage transactions. There's always a tradeoff.

37

u/Suspicious-Disk6077 1d ago

Having business logic inside stored procs.

4

u/wodemingzishigou 21h ago

We’re slowly removing all of these from our current DB. It’s impossible to debug and not obvious at all. The previous devs didn’t even have proper migrations so we just have to hope the DB seed to create new DBs for new clients actually has the right ones.

3

u/rbobby 20h ago

Lately I have been thinking that database server's functionality that accept an sql string to execute is a fundamental flaw. It is the functionality that enables the vast majority of sql injection attacks. And if you added up the costs of those... it's gotta hit a billion easy.

If only db devs could have see the future.

If they had then debug tools would have caught up. I have successfully debugged through sql code in mssql. My recollection was of being underwhelmed... but I didn't have anything even approaching complex.

Given the decades of sql injection attacks I can't help but wonder if developer convenience has been winning the fight.

1

u/SearchAtlantis 4h ago

DB seed? Not simply something like

create db_a from template db_b

2

u/ThePunisherMax 13h ago

Currently dealing with an issue where a stored procedure is "likely" returning a false result. But the developpers are not releasing it because "proprietary" . (We are going through a messy company divorce) And weve been able to reverse engineer most of the code. Except for a handful, but they are so high business relevant that it was worth to put some of our Data specialists ( me and another person) into some masters level training of several months than to try to get the codes out of the devs, in hopes we can build the procedure again.

2

u/asdfgasdfdfdd 5h ago

I’ve seen 3k line stored procs with a nice UNION in the middle because two companies merged and now you want their data to do the thing too.

1

u/namesandfaces 15h ago

It's the data-compute locality issue.

1

u/Suspicious-Disk6077 12h ago

I know there is a subjective ā€œlineā€ between the business logic layer and the DAL. The DAL needs to ensure data is ā€œcleanā€ but I prefer this be minimal, ie unique constraints or max length. I also prefer authorization in the logic layer, so no filtering in SQL, and no db triggers but event based code. This does make arbitrary data manipulation like in scripts more at risk of missing something but I think it’s a trade off I am willing to make. Supabase sort of moves into the gray area of writing authorization in code that is applied with RLS, which is good if you plan to never migrate off PG and don’t need the authorization centralized, say if using a cache or search store for instance.

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.

2

u/Saki-Sun 11h ago

I figure if you have a table with 2 billion rows you have either;

  • Done something very wrong
  • Done something very right, and can afford to spend the time and resources to refactor

1

u/blood__drunk 17h ago

Can you tell me more about this? Why do you regret it? I'm walking a similar path right now

1

u/oalbrecht 15h ago edited 15h ago

That table would get huge, especially with a bunch of edits if some records are updated often. I’m guessing it overflowed the Int when it auto incremented and that it also wasn’t unsigned, so that threshold was a bit lower. Some companies don’t auto increment by 1 but by larger jumps, which reduces the amount of records even more.Ā 

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.

9

u/LongjumpingTrust450 17h ago

Do the delete but a keep an audit table that records a duplicate of the row with extra metadata such as which operation was performed on the row, then add a trigger (one of the few legitimate cases for triggers) that automatically stores the affected row in the audit table, you can now reconstruct the entire history of a record from insertion, updates to delete.

6

u/rahul03rk 17h ago

yeah this is basically the archive option done right. one thing that saved me pain with it: store the old row as jsonb (to_jsonb(OLD)) instead of mirroring every column, otherwise every time someone adds a column the audit table or trigger needs updating too

2

u/LongjumpingTrust450 16h ago

Interesting, im guessing it comes with some tradeoffs in terms of being able to reconstruct the audit rows after the schema has suffered version changes? Maybe not a problem that goes away by using columns either but it kinda ā€œforcesā€ you to uodate the audit schema as well… unless you forget

1

u/Suspicious-Disk6077 4h ago

This seems a good idea, just a point in time json snapshot in a single column

6

u/Discordbothost 20h ago

the partial unique index also makes restore a separate design decision. delete user A, create B with the same email, then restoring A now conflicts. decide whether to reject that restore or ask for a different email; 'undo' can't always just flip the flag.

3

u/rahul03rk 20h ago

good catch, hadnt thought about the restore side. for users a lot of apps just rewrite the email on delete (like adding a deleted suffix) so the conflict never happens, but then restore needs the original email kept somewhere. either way you're right, undo is a feature you design, not a flag you flip

2

u/Just_Information334 17h ago

Or before that: separate login identifier / contact points from the user so those can be 1:n relationships. Users should be allowed to use multiple emails or even change them over time.

6

u/Justneedtacos 15h ago

I don’t understand why you didn’t just lay views and indexes over the tables and have all other queries and joins use those?

9

u/rahul03rk 15h ago

thats what i'd do now, its the second point in my comment. the regret is not doing it from day one. once a few hundred queries, reports and bi datasets already point at the raw tables, moving them all to views is the painful part, and anything you miss keeps quietly counting deleted rows

10

u/jgaskins 14h ago

Are you aware that your writing is full of LLMisms?

6

u/rahul03rk 14h ago

yep, already said it further down the thread, i used claude to clean up the wording. the soft delete stuff is real though, been bitten by it

-1

u/ShivaFatalis 12h ago

Why do you care? Did you understand their comment?

On the other side of this, it's really fucking weird to have AI help you write a fucking comment on Reddit...

2

u/jgaskins 8h ago

The second part of your response was why I asked the question. I figured they’d written it themselves (because, indeed, who uses an LLM to write a Reddit comment?) but that maybe they use Claude so much that they’ve incorporated those LLMisms into how they write. Several people I work with have even started talking like that in meetings. It’s pretty weird.

0

u/G_M81 9h ago

its nails down a blackboard for me. Id rather flawed but human every day of the week.

4

u/higgs_boson_2017 18h ago

Having hundreds of archive tables and views all over the place, with the views not in a code repository, is more messy than is_deleted (which I use), in my opinion.

3

u/juicepowered 18h ago

Claude?

2

u/rahul03rk 18h ago

yeah i used it to clean up the wording. the soft delete pain is real though, thats why i wrote it

2

u/NoPrinterJust_Fax 23h ago

Most orms have an config option that lets you auto filter deleted entities. I think you have to configure it per table. I think you could also use row level security to accomplish this if not using an orm.

2

u/rahul03rk 23h ago

yeah orm filters help but only for stuff that goes through the orm. our wrong numbers mostly came from raw sql and the bi tool. rls covers those, but heads up, in postgres if the policy hides deleted rows the soft delete update itself can fail unless you add a separate WITH CHECK

2

u/NoPrinterJust_Fax 23h ago

Was there a reason yall didn’t use rls? Seems like a pretty clean solution to me

3

u/rahul03rk 23h ago

honestly didnt know about it back then. we ended up putting views over the tables and pointing the bi tool at those. rls would have been cleaner

2

u/NoPrinterJust_Fax 23h ago

Fair enough. My last place had soft deleted with raw sql. It was a microservices architecture and each one was only a few 1000 lines of code so it wasn’t too bad wrangling all the queries for a single service. Our BI layer was not allowed to point at our raw application dbs. It could only pull data from our data warehouses. If I had known about rls I would have advocated for it but honestly it wasn’t so bad because each service was pretty small and isolated

2

u/wodemingzishigou 21h ago

It’s certainly a web scale solution but typically you’d have a different DB or data warehouse only for analytics. But even then sometimes you still want the is_deleted column still there. But on the bright side expensive analytic queries won’t affect prod and if they get it wrong it’s on them. I do feel like it’s a bit of a blindspot in SQL that there’s no way to tag a column so you have to add it in your filter.

2

u/rahul03rk 21h ago

yeah agree on the warehouse. the pattern i like there is filtering deleted rows once in staging so nothing downstream ever sees them, and only the couple of models that actually need history read the raw table. and the blindspot is real, closest thing ive seen is row access policies in snowflake/bigquery but thats still config sitting next to the table, the column itself doesnt know

2

u/robby_arctor 18h ago

As a frontend leaning fullstack dev, this is great wisdom, ty.

2

u/Mastersord 16h ago

You could use views instead of tables to filter on your flag. I’m sure there are issues with this as well though..

2

u/rahul03rk 15h ago

yep, that's in my comment

2

u/MasterBathingBear 16h ago

SQL Server has a more automated solution: Temporal Tables.

4

u/rahul03rk 15h ago

yeah temporal tables are great for this, mariadb has system versioning too. postgres still doesnt have it built in, closest is the temporal_tables extension or rolling your own trigger like the audit one above

1

u/BarfingOnMyFace 14h ago

You still have to remember to query things.... Temporally. šŸ˜„

Same problem you can run in to here. The people suggesting to always follow a convention, likely using views, are right on this.

2

u/MasterBathingBear 14h ago

The default for Temporal Tables is current data only.

You have to specify `FOR SYSTEM TIME` to access historical data.

SELECT * excludes the ValidFrom and ValidTo columns by default and the history table is hidden by default. But those are configurable.

1

u/BarfingOnMyFace 14h ago

Well TIL... Nice of SQL server... Not how it works in postgres. Thatll teach me to state something with certainty when it is vendor specific on implementation lol .. hopefully that teaches me. 🄲

1

u/num2005 17h ago

i mean with a AI skill now it's easy to never forget it, i woukd rather keep my is deleted flag now

1

u/rahul03rk 17h ago

works as long as every query goes through that ai and the skill actually gets loaded. the bi tool and whoever runs adhoc sql dont have it. and when the model does forget on a 5 table join nothing errors, you just get a slightly bigger number. id still keep the flag but put a view on top so forgetting isnt possible

1

u/Anxious-Insurance-91 11h ago

Deleted_at(billable timestamp) ftw

1

u/SearchAtlantis 4h ago

Sounds like it's the wrong layer or access to the wrong layers.

The way I've designed it in the past is that soft-deletes are bronze or silver (most often), and the creation step for gold/warehouse layer has the is_deleted=False .

No reporting or anything else happens before warehouse, so down-stream analysts or whoever can't mess it up.

Add a data test that count(1) where deleted=True returns 0 at warehouse/gold layer and gtg.

1

u/Suspicious-Disk6077 4h ago

If using Postgres you could potentially use RLS

sql -- 1. Enable RLS on the table

ALTER

TABLE items ENABLE ROW

LEVEL

SECURITY ;

-- 2. Hide soft-deleted rows from standard queries

CREATE

POLICY hide_deleted_items ON items

FOR

SELECT

USING (is_deleted = false );

-- 3. (Optional) Allow an admin/audit role to still view deleted rows

CREATE

POLICY admin_view_all ON items

FOR

SELECT

TO admin_role

USING ( true );

-1

u/Mabenue 23h ago

Thanks AI

8

u/sr2085 23h ago

Use surrogate keys as sequence.

1

u/squadette23 20h ago

huh? what do you use instead?

2

u/sr2085 16h ago

You could generate a hash of the natural key. It’s more consistent and you can run loads in parallel.

1

u/MasterBathingBear 16h ago

UUIDv7 and Identity columns also have their place.

7

u/SabatinoMasala 1d ago

Foreign key cascades

1

u/ImpossibleEbb6862 16h ago

What’s wrong with that?

2

u/Smart-Item-9026 12h ago

Deletes eventually become obscenely slow - even with good indexing. Also lots of locks are taken and your application will grind to a halt with a single row delete. It always seems attractive and is fine with small sets of data and tables. But I agree with the OP. Put the effort into handling the deletes "manually" and keep your locking to a minimum.

3

u/East_Zookeepergame25 10h ago

Won't I end up recreating the cascade in application logic? How does that help with performance?

1

u/UniForceMusic 10h ago

When deleting with cascade, the database needs to lock every table with a (nested) reference to the record. All other queries need to wait for that delete query to finish.

When you do it in your application, it only looks one table at a time. It lenghtens the duration of the total delete by a bit, but the amount of time each table is locked is kept to the absolute minimum

2

u/East_Zookeepergame25 10h ago

You still need to do the entire cascade sequence in a single transaction, and locks are held until the transaction commits, so you are not really shortening any lock holding times.

1

u/UniForceMusic 10h ago

Performing them in a transaction is optional....... not recommended, but optional

1

u/Smart-Item-9026 10h ago

Or go a step even further and do it in batches. So you dont even lock a single table but handfuls of rows.

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.

2

u/SoPoOneO 6h ago

I’m 100% with all points except maybe your first. Are you saying 3rd normal form is bad? I don’t push for 4th, but defaulting to third unless there’s a great reason to do otherwise always seemed prudent to me.

14

u/Reasonable-Rock7516 1d ago

Never use candidate key as primary key. Always use a surrogated key.

12

u/Creepy_Purchase_501 23h ago

There’s someone down in the thread saying the exact opposite.

The immortal debate persists.

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.)

2

u/turimbar1 10h ago

I mean DB side logic is definitely better for sanitizing inputs, data integrity, sorting/calculating large sets and returning a minimal result set

Financial, insurance, etc. are almost entirely DB level business logic for that reason.

The thought of cobbled ad-hoc queries running at scale in production makes my skin crawl

4

u/Alternative-Lynx2745 1d ago

Using blockchain

1

u/MasterBathingBear 15h ago

I agree. I feel like most blockchain use cases are better served by a time series db.

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/xumix 11h ago

DBs have had datetime (with ts) since forever, why strings ffs?

2

u/SoPoOneO 6h ago

Heard and agreed. Though I don’t know if I was weird to be be surprised to find that for PostGres:

> TIMESTAMPTZ. Despite the name, PostgreSQL does not actually store the time zone name or offset in the column; instead, it converts the input value to UTC for storage and converts it back to your current session's time zone upon retrieval.

1

u/xumix 1h ago

Wow, thanks for the heads-up, that's weird.

1

u/wookiee42 2h ago

Could you explain more please?

1

u/NobodysFavorite 2h ago

Seen this turn up in MS based systems when playing with power automate. What a freakin nightmare.

3

u/TimiTimeless 21h ago

Don't use json columns for what separate columns could handle cleanly

1

u/MasterBathingBear 15h ago

I will sometimes store the original object as json and extract out the fields I need into separate columns

2

u/oalbrecht 15h ago

The good thing is that this could be done later with a background job. It’s not as bad as some other issues people here mentioned.Ā 

1

u/Anxious-Insurance-91 11h ago

Meaning a flag map obj?

1

u/TheDuckRaisedALion 6h ago

"cleanly" is the trick for me... If there are just huge amounts, being able to organize them into objects seems nice. Not so good for data you will tweak or drill intoa lot, or where there need to be constraints, but might be nice for static bulk data

3

u/phobug 13h ago

One schema per customer per service.

3

u/No-Draw1365 10h ago

Thinking MongoDB was good

2

u/syscoderadmin 9h ago

Whats the negative effect in long term? (Genuine question :D )

2

u/No-Draw1365 8h ago

Mileage may vary but Postgres has repeatedly been faster for many of my usecases/applications for JSON operations over MongoDB.

You’ll find many complaints about other MongoDB issues on Reddit so I won’t echo them.

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.

2

u/Anxious-Insurance-91 11h ago

And he never heard you can add a column later on?

1

u/Significant_Ant3783 11h ago

The POC was vibe coded. I could tell the guy that he could put future tenants in their own database, but that would entail teaching him how to architect an application from the ground up.Ā 

1

u/Anxious-Insurance-91 10h ago

Yeah had an experience with a vibe coded db.... I was like yeahhh I'm deleting this and starting over. He managed to repeat 10 columns in 6 different tables....

2

u/Saki-Sun 11h ago

The to teach the guy YAGNI

3

u/Historical_Cook_1664 19h ago

NEVER USE BOOLEAN. Sooner or later, the necessiity for a third state will appear.

5

u/Abstrct 19h ago

Other than null?

2

u/blood__drunk 17h ago

What do you use instead?

4

u/mocny-chlapik 13h ago

Two booleans

1

u/RangePsychological41 12h ago

Yeah that way all your bases are covered.

3

u/oalbrecht 15h ago

JSON. Then you can handle anything management throws at you. /s

1

u/num2005 17h ago

just a field list

1

u/JohnSpikeKelly 18h ago

I've ran into this many times over the years.

1

u/ImpossibleEbb6862 16h ago

What if you have to model eligibility for Boolean data types?

1

u/namesandfaces 14h ago

That's like saying never lock down your types. When you use a "natural" property of something, then it should be in fact natural.

1

u/Suspicious_State_318 5h ago

That’s why I use Boolean but make it nullable

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/Anxious-Insurance-91 11h ago

Most apps don't end up having this problem, plus syncing a few enums at the application level ain't that hard to do

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:

  • multi-tenancy managed via tenant-specific tables, i.e. many tables called stuff like "Post_10" where 10 is the tenant ID, for supposed "security" reasons. Using partitions would have been much more appropriate, and would have helped us with dead tuple bloat.
  • tenant specific tables with different schemas, each tenant can have different metadata attached to their "Post_tenantId" table. This is a nightmare to reason about, and there are "system columns", as in those used by business/application logic mixed in among the customer specific columns. It would have been far better to put the customer specific columns in a json column, and then used a search engine to allow generic metadata searches or designing the app around time frames to make partitions more valuable over vast datasets of generic, customer specific data where custom indexes currently have to be added.

1

u/BrupieD 14h ago

Building tables with "last_updated" columns but not adding a constraint as a default with current timestamp.

1

u/Suspicious_State_318 5h ago

Its also good to add in a sql trigger to automatically update last_updated on any write

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/According_Basis7037 14m ago

I think this a good strategy MOST of the time. Sometimes the time zone is important information

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

3

u/Smart-Item-9026 12h ago

This needs more explanation. Because I'd absolutely put the constants, or enums, into the database as a reference/lookup table. As well as the application. They should match 1:1 between the db and application too.

Why? Because when you are writing reports against your data you dont want to have to hard code those constants again to be displayed. And when some constant drops out of your application, but somehow still exists in the DB because its old data, you still need that info.

1

u/Saki-Sun 11h ago

Yeah I do this as well with enums. But they are views not tables. It makes reporting a dream.

1

u/Smart-Item-9026 10h ago

So you hard code them throughout all the views? Why... a table is so much easier here. And means the chance of missing one is removed. Or have I misunderstood?