0

I have a database that is not well designed. To design a new database, I came up with the idea of having 4 copies of each schema (A1, Backup_A1, History_A1, Test_A1). So if a row is added/updated in any of the tables in A1, the same will replicate on Backup_A1 and Test_A1 as shown in the image. While the History_A1 will have the previously updated row inside it with the expired_column = True and timestamp _ts. This makes it much more confusing in many cases because in production, a table can be altered to add more columns, for example, if add the new column in A1, how the change should replicate in the other three schemas tables. I am not sure how the triggers can handle such task when needed to add new rows or alter tables to add new columns.

Please if you can explain it with example, that will be great.

enter image description here

DumbLoawai
  • 11
  • 4
  • So all I can do is write triggers that can add and copy rows into other schemas and I have to manually alter each schema.table column if i want to alter the main schema.table. Am i right ? or if you have any other suggestion, Please share :) – DumbLoawai Jun 23 '20 at 06:31
  • Reading again, it looks like you are not talking about schema changes at all (`ALTER` statements), but data modifications. Is that so? If yes, you might want to [edit] your question to make that clearer. – Laurenz Albe Jun 23 '20 at 06:36

0 Answers0