I am designing schema. Any idea is welcome!
Right now I have tableA, tableA_cache, tableB, tableC, tableD. If any row insert to tableB tableC or tableD, the trigger will insert data to tableA.
For data integrity, periodically I have to delete all the rows in tableA then re-populate data from tableB, tableC…etc.
Normally, user query tableA_cache so that even if tableA has no data, user can query tableA_cache. However, during re-population, tableA will be keep writing. At the same time, if user insert a row in tableB (or C, D), the trigger should insert data to tableA. Since tableA is locked, the response time for insert will be extremely slow.
Is there a better way to handle this case? or a direction I can look into. (There is no guarantee that in specific timing no user will use the system.)
Best Answer
Get rid of all the complex design.
Plan A: Get rid of A and A_cache and the trigger and the rebuild; run queries against B and C. Perhaps you need better indexes? Or "Summary tables"?
Plan B: (More directly in line with your design.) Get rid of A_cache. Do this, which will avoid A ever being unreachable:
RENAME TABLE A TO A_old, A_new TO A;-- atomic and instantaneousDROP TABLE A_old;