sql server - SQL - Audit log table design - Which would you prefer? -


for project company i'm working on setting up, in need of auditing (storing logs) various of tasks (changes made tables in system) users perform. currently, there 3 different types of tasks. may grow in future.

my suggestion following schema table's , relationships (example):

table auditlog ------------------------------ id | pk description created

and every task:

table exampletaskauditlog ------------------------------ exampletaskid | fk pk auditlogid | fk pk

and:

table anotherexampletaskauditlog ------------------------------ anotherexampletaskid | fk pk auditlogid | fk pk

basically, every kind of task need audit, have new table holding relationship.

what developer suggested, following:

table auditlog ------------------------------ id (pk) description created exampletaskid | nullable anotherexampletaskid | nullable type | (an integer id indicates whether "example task" or "another example task").

basically, if create log "exampletask", set exampletaskid-field identity of example task , type corresponding exampletask-enum value.

he suggested above table because arguing integrity(which think good!) , performance. because there fk constraints, , 1 need join table in order relevant logs (yes, rmdbs - mssql). also, since there 2 tables every log, there need 2 inserts(integrity lookups etc). sure, correct. can't see issue. not performance since it's minimal. also, logs going stored not going more 5-10k in total first year. in couple of years, tables might contain 30-40k rows, @ max.

what opinion on above? also, 1 of solutions above prefer, , why?

i'm not sure understand - if exampletaskid , anotherexampletaskid same data type, why not have 1 table following columns?

  • id
  • description
  • created
  • taskid
  • tasktype

that aside, auditlog table definitely should have tasktype field, otherwise becomes relatively difficult work out type of change record in log represents.

also, avoid (where possible) denormalizing tables (i.e. having columns null given task type) unless absolutely necessary (for performance reasons example). instead i'd recommend using tables , joins task specific columns:

table exampletaskauditlog ------------------------------ auditid (pk) taskspecificfield anothertaskspecificfield

Comments

Post a Comment

Popular posts from this blog

apache - Add omitted ? to URLs -

i working on website cms reads page name of query string, e.g. http://exmaple.com/?pagename . trying create rewrite rule rewrite ? . so, user types in /pagename , taken /?pagename try this: rewriteengine on rewritecond %{request_filename} !-d rewritecond %{request_filename} !-f rewriterule ^/?(.*) index.php?$1 [l]
Read more

redirect - bbPress Forum - rewrite to wwww.mysite prohibits login -

i using bbpress installation without wordpress integration. the redirect of rewriteengine @ forum site works: rewriteengine on rewritecond %{http_host} ^forum.mysite.com$ rewriterule (.*)$ http://www.forum.mysite.com/$1 [r=301,l] the problem login pppress. bbpress login not recognize: http://www.forum.mysite/bb-login.php . it redirects to: http://forum.mysite.com/bb-login.php . i tried permalink settings none , name based, same issue. does uses redirect in bbpress or how can eliminate "double" content , redirect permanently www.? i redirected www.forum.mysite.com forum.mysite.com , achieved eliminate "double" content.
Read more

php - How can I stop spam on my custom forum/blog? -

so have custom built forum & blog system of late has been dealing lot of spam. if wordpress use akismet, if different common platform i'm sure i'd find plugin. there kind of static class can download this? using php. i'd still go akismet, if it. uses outside of wordpress, may have pay fee it, depending on use -- check terms , conditions -- it's option , easy implement in php using api. use api key wordpress. com account access. basically, grab whichever php client library takes fancy -- use alex potsides' php5 library -- plug in key, , it's handful of lines of code. here's bare bones of validation straight 1 of live sites: ... if ($akismet) { $akismet->setcommentauthor($name); $akismet->setcommentauthoremail($session->userinfo["email"]); $akismet->setcommentauthorurl(""); $akismet->setcommentcontent($sentence);...
Read more