Versioning Database Persisted Objects, How would you? [closed]

by

Think carefully about the requirements for revisions. Once your code-base has pervasive history tracking built into the operational system it will get very complex. Insurance underwriting systems are particularly bad for this, with schemas often running in excess of 1000 tables. Queries also tend to be quite complex and this can lead to performance issues.

If the historical state is really only required for reporting, consider implementing a ‘current state’ transactional system with a data warehouse structure hanging off the back for tracking history. Slowly Changing Dimensions are a much simpler structure for tracking historical state than trying to embed an ad-hoc history tracking mechanism directly into your operational system.

Also, Changed Data Capture is simpler for a ‘current state’ system with changes being done to the records in place – the primary keys of the records don’t change so you don’t have to match records holding different versions of the same entity together. An effective CDC mechanism will make an incremental warehouse load process fairly lightweight and possible to run quite frequently. If you don’t need up-to-the minute tracking of historical state (almost, but not quite, and oxymoron) this can be an effective solution with a much simpler code base than a full history tracking mechanism built directly into the application.

More Related Contents:

  1. Database – Data Versioning [closed]
  2. Database development mistakes made by application developers [closed]
  3. Database, Table and Column Naming Conventions? [closed]
  4. What’s wrong with foreign keys?
  5. Surrogate vs. natural/business keys [closed]
  6. What are database normal forms and can you give examples? [closed]
  7. What is the best design for a database table that can be owned by two different resources, and therefore needs two different foreign keys? [closed]
  8. How to version control a record in a database [closed]
  9. NoSql vs Relational database
  10. Multiple schemas versus enormous tables [closed]
  11. First name, middle name, last name. Why not Full Name?
  12. Relational table naming convention [closed]
  13. What are the design criteria for primary keys?
  14. Techniques for database inheritance?
  15. What is the most efficient way to store tags in a database?
  16. Microservices with shared database? using multiple ORM’s? [closed]
  17. Supertype-subtype database design
  18. In terms of databases, is “Normalize for correctness, denormalize for performance” a right mantra?
  19. How should international geographical addresses be stored in a relational database?
  20. Still Confused About Identifying vs. Non-Identifying Relationships
  21. Which is a good design for a database table that can be owned by two different resources, and therefore needs two different foreign keys? [closed]
  22. What’s wrong with nullable columns in composite primary keys?
  23. Ideas on database design for capturing audit trails [closed]
  24. Is it better to use multiple databases with one schema each, or one database with multiple schemas? [closed]
  25. Is it a good idea to use an integer column for storing US ZIP codes in a database?
  26. Best design for a changelog / auditing database table? [closed]
  27. What are some best practices and “rules of thumb” for creating database indexes?
  28. When we need to use 1-to-1 relationship in database design?
  29. Need help in developing DB logic
  30. Databse architecture (single db vs client specific db) for Building Enterprise Web (RIA) application on cloud

Leave a Comment