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.

  • I don't understand question. The repeating columns that you speak of are important for every table. They show when the data was changed. That is why they are repeating. Of course you can remove them, but in that case you will not be able to tell when the "USA" country is updated to "Usa" for exmaple. There is also good practice (in my huble opinion) to have columns that show who changed that data. – Bogdan Bogdanov Apr 24 '15 at 9:20
  • What problem is caused by having these columns in these tables? – Michael Green Apr 24 '15 at 10:21
  • There is not a big issue with those columns but i just wanted to normalize it. – Govinda Rajbhar Apr 24 '15 at 10:23

You can achive this by creating History table. In that table you can store all changes from those tables. You have to use tiggers to populate data in History table. Take into account that if the history table stores all changes you need engine to populate it. Another approach is to have History table for each table that you want to observe. In your case Countries_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.

  • 1
    No problem, you can accept my answer if you think that it answers the question. For implementing my answer you have to make addtional reserach - especially if you want only one History table. In that case you must store each filed on the row in separate row in that table and make transforamtions (because of data types). – Bogdan Bogdanov Apr 24 '15 at 9:42

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.