I’d like to share a simple pattern that has worked well for the past decade. For many good reasons (security, availability, performance, etc.) it is not usually a good idea to connect a website directly to operational systems, unless it is providing an e-commerce function. It’s quite a common design to have a web-optimised database refreshed overnight from operational data, as shown:
One of the disadvantages of this design is finding a solution to updating the Web database without causing downtime or inconsistencies: typically you could either drop and re-create the whole database (which can lead to unavailability) or perform a delta update (which can be very complex to implement).
My preferred solution is to use a pair of databases and then ‘flip-flop’ between them: whilst the Website is using database A database B can safely be dropped and re-created:
Once the database is fully refreshed a flag is set which means subsequent sessions are switched to the most recently refreshed database and the next refresh will drop and re-create database A: