How should I setup a table in a database to keep the history of records? -

-

i'm trying setup sql server database asp.net mvc site both store latest information history of changes data.

the information site comes xml files uploaded user. site parses through xml , writes contained information sites database. elements in successive uploads may represent same thing, data may have changed. yet said before want keep track of every version.

the table bellow shows 1 way thinking of approaching this. create duplicate records each item in each upload. new items match in previous uploads assigned same id, each item assigned unique upload id.

upload 1: erik , sara added upload 2: erik renamed eric, bill added upload 3: sarah grew 2" taller, eric removed.   [persons table] personid     name    height    uploadid 1            erik    71        1 1            eric    71        2 2            sarah   70        1 2            sarah   70        2 2            sarah   72        3 3            bill    76        2 3            bill    76        3   [uploads table] uploadid    uploadtime 1           3/09/2011 2           4/01/2011 3           4/11/2011

however, doesn't seem optimal solution me because of how information ends being duplicated in database. there better way approach changes saved each upload?

i think problem is persons table no longer contains information persons. contains information on updloads. i'm going recommend won't decrease size of database; make little easier understand , work with.

persons personid, name, height 1            eric    71 2            sarah   72   3            bill    76  upload uploadid, uploadtime 1           3/09/2011 2           4/01/2011 3           4/11/2011  persons_edit personid, uploadid,      changesql,                                    changedescription 1          1         "insert persons(name, height) values('erik', 71)" "erik added" 1          2         "update persons set name='eric' name='erik'"    "changed erik's name" ....      ...              ......                                            ....

i don't think can beyond make tables simpler or database smaller. can see, persons_edit table going largest table. database you're using might provide mechanisms automatically (some sort of transaction recording or something) i've never used i'll leave other people on stackoverflow make suggestions if exist. if persons_edit table gets large, can @ deleting entries on week/month/year old. decision on when you.

some other reasons making change, in first table, had use personid , uploadid primary key persons table. so, recent version of person within application, have had select person id, , order uploadid , select 1 largest upload id every time transaction on 1 person.

another benefit don't have bunch of fancy sql edit history. select * persons_edit table.


Comments

Post a Comment

Popular posts from this blog

php - What is the difference between $_SERVER['PATH_INFO'] and $_SERVER['ORIG_PATH_INFO']? -

-
what’s difference between $_server['path_info'] , $_server['orig_path_info'] ? how use them? when run print_r($_server) , path_info , orig_path_info not present in array. why not? how can enable them? i have read php manual on them, still don’t understand them. the path_info variable present if invoke php script this: http://www.example.com/phpinfo.php/hello_there it's /hello_there part after .php script. if don't invoke url that, there won't $_server["path_info"] environment variable. the porig_ prefix uncommon. path_info standard cgi-environment variable, , should never prefixed. did read that? (there issues around php3/php4 if invoked php interpreter via cgi-bin/ - hardly has such setups today.) for reference: http://www.ietf.org/rfc/rfc3875
Read more

fortran - Function return type mismatch -

-
i'm attempting recode old c++ program in fortran make use of lapack (i'm aware c++ have lapack++, i'm having lot of trouble installing it, gave up). i didn't have problems compilation, when had variables declared real . when started coding section of program required lapack, found parameters passed dsyev need double precision . tried change double precision (including changing hard coded numbers double precision counterparts, i.e. 0.0 -> 0.0d0) when try compile, following error of functions , subroutines i've written: error: return type mismatch of function <function> @ (1) (real(4)/real(8)) i'm not sure coming from, in program has been changed double precision. for example, i've declared following: double precision :: alpha(3),d(3),zeta1,zeta2 double precision :: a1(3),a2(3),d1(3),d2(3) double precision :: pi pi = 3.14159265359d0 alpha = (/0.109818d0, 0.405771d0, 2.22766d0/) d = (/0.444635d0, 0.535328d0, 0.154329d0 /) 10 i=1,3 ...
Read more

queue - mq_receive: message too long -

-
i implementing communication between 2 processes using queue. problem when call function mq_receive, error: message long. i have done following: struct mq_attr attr; long size = attr.mq_msgsize; .... // initializing queue "/gateway" int rc = mq_receive(gateway, buffer, size, &prio); if print size value, size=1, while when print same size program (got same mechanism), not long integer ( -1217186280 )... how can solve error?....so while size = 1, believe it's right "message long" why 1? p.s. have tried put : int rc = mq_receive(gateway, buffer, sizeof(buffer), &prio); but no result. it seems need read docs more carefully. when call mq_receive should pass size of destination buffer. this size must greater mq_msgsize attribute of queue . in addition, seems have error in queue attributes initialisation makes proper mq_receive call impossible. here standard message queue session: fill mq_attr struct ( doc ): struct mq_a...
Read more