New answers tagged database-recommendation
Generally speaking when you want to fix a big-ball-of-mud architecture it is best to focus on one small aspect that you can fix relatively quickly with maximum payoff. What that thing is will vary depending on the system. Once you identify that thing then implement it and look for an opportunity to fix something else. Over time your big-ball-of-mud will get ...
One answer I have given for a similar situation is here: Best way to design a database and table to keep records of changes? The third and fourth bullet points address your request for single query access to current / historical data and preservation of foreign keys. Sometimes it's a help just to know that what you want is possible.
Trying to do FKs will just frustrate you - you don't actually need it. If you handle versioning by using an InsertDateTime concept, then you're basically describing a Type2 dimension as used by many data warehouses. There is quite a lot of material out there about tuning systems that sit over the top of data warehouses, but from a T-SQL perspective, ...
Top 50 recent answers are included