Whats the best way to design this database scenario?

Question

I want to setup 2 MySQL databases which differ in schema in that, one is normalized and the other is flat for quicker reads. The information being stored in both DBs is the same, but the representation is obviously different owing to the different design approaches.

I need to find a robust solution to sync information in real time from my normalized version to my flatter version.

EDIT: The sync only needs to be from the normalized DB to the flat DB. The flattened DB would not write to any of the tables that have been flattened out from the normalized one.

Answer 1

The approach is sound from a read standpoint, but not from a write one. The problem being that it is easier to denormalize from normalized tables than to do the reverse. If you're willing to take a speed hit on writes, you can write both sets of tables simultaneously, and read only the denormalized tables. This has the added benefit of providing always up-to-date information on reads.

If writing in this way is not possible, you will need to put a "dirty flag" in with some of your normalized tables, so that you can grab the fresh records and update the denormalize the tables with them. You will then need a set of stored procedures that periodically updates the denormalized tables from the normalized ones. For ideas on how to do that, look here: http://www.xaprb.com/blog/2007/08/29/how-to-notify-event-listeners-in-mysql/

Answer 2

The approach you are taking makes a lot of sense, if your de-normalized is intended to be used for read-only use and is NOT real time.

There number of solutions based on log shipping and triggers that allow near real-time performance - not sure it's available for mysql. These solutions always come with a catch though - there is no free lunch.

At this point in the RDBMS game, there is no way to get both. Normally one develops fast /insert OLTP DB and delayed - not real time OLAP database, which is populated once a day or so. This usually satisfies 99% of all reporting requirements.