I have more than 20 tables in my database. There are some columns which occur in almost all of the tables. In that case what should I do? Do I put all those columns in every table or is there a better way to solve this problem?
Country Table Example
CountryID, CountryName, AddedBy, AddedTime, LastUpdatedBy, LastUpdateDTime, DeletedBy, DeletedTime, DeleteFlag
As we can see in the table structure above there are 9 columns. Except for CountryID and CountrynName all occur in most tables. I don't want to keep above repeating columns in all tables. So if there is any solution then please let me know.
Best Answer
You can achive this by creating
Historytable. In that table you can store all changes from those tables. You have to usetiggersto populate data inHistorytable. Take into account that if the history table stores all changes you need engine to populate it. Another approach is to haveHistorytable for each table that you want to observe. In your caseCountries_History. You have to decide when you make design what amount of such info to keep. Of course you can just remove all that columns if you don't care about the info they store.