2

this is a question around best practice that I'm currently dealing with. I have a database (SQL Server 2008 R2) with a heap of tables (around 40) with data, now the need has risen to have logging of which Changes are made not from a technical standpoint but for instance if a row gets updated some users needs to have a "tracking view" in the application in which they can see which fields were changed and when.

So to my question, should I add a new table and store all this or is it better to add a new database to handle all history stuff in? What are the pros and cons?

  • 4
    If the history tables refer to entities in your "main" tables they most definitely should be in the same database. Presumably foreign keys will be involved and this is meta-data about our "real" data. – JNK Jul 9 '14 at 12:53
  • Foreign keys aren't a given for auditing, particularly if the app is coded so that the base rows are hard-deleted instead of soft-deleted. In any event, there are so many different designs for this type of thing, I'm not sure we can reasonably answer the question. – Jon Seigel Jul 9 '14 at 13:31
2

I would definitely err on the side of keeping the tables in the same database. It makes it easier to maintain referential integrity (unless you hard delete records in which case foreign keys won't work for you anyway) and create consistent backups (simply backup one database and you know it is in a consistent state, instead of taking two+ backups that my be slightly out of sync).

If you concern is wanting to spread the data between different storage pools (for instance keeping history and audit data on something big & cheap but potentially slow, while keeping active data on something faster but smaller & more expensive), or just keeping the different data on different spindle-sets to reduce potential IO contention, then you can achieve this within a single database by distributing your tables between files/filegroups instead of just having one filegroup containing everything. See http://technet.microsoft.com/en-us/library/ms189126(v=sql.105).aspx (amongst other reference locations in MS's sites) for more details on this.

-1

I would do this in the same database because the data belongs together. To create a trigger for this use, it is not practical to insert records to another database.

Refer to the following answer on how to design the auditing table. https://stackoverflow.com/questions/7975317/audit-tables-each-field-for-table-or-one-table

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.