May 18, 2009 at 6:21 am
Hi,
I've design some database tables for storing client data in an every changing environments. The tables are as follows
1. Forms: FormId (key,auto), FormName, CreationSessionId, etc
2. Fields: FieldId(key,auto), FieldName, DataType, Label, etc
3. FormSections: FormId, SectionId(key), Title, etc
4. FormFields: FormFieldId(key,auto),SectionId, FieldId, etc
5. FormInstances: FormInstanceId(key,auto), FormId, OwnerId, etc
6. FormInstanceData: FormInstanceId(key), FormFieldId(key),value, etc
7. FormInstanceDataHistory: FormInstanceId, FormFieldId,oldValue, newValue
It works fine. Haven't tested it for performance though. What I seek to achieve is how I can add some column(s) or even restructure the data so that I can get the exact form data at any given time.
Every change set will increase the revision by 1 just like subversion and I would like to retrieve the form data passing in the revision so as to be able to generate the appropriate reports.
Eg.
To get the form data, the query "select * from formInstancedata when forminstanceid=@x" will work.
I would like to use something like
Select FormFieldId,newValue as Value from FormInstanceDataHistory where revision=@r
The thing is this will return all those with revision equal to the given number. If it was revision<=@r, it might end up returned multiple instances of the same field entry whereas only the most recent one equal to or below the revision is expected. I do not want to save the entire form data only the bulk changes that the user makes. Any help as to how to structure the database tables and or the queries needed? All suggestions are welcomed. Thank you.
May 19, 2009 at 7:59 am
koosubscriptions (5/18/2009)...so that I can get the exact form data at any given time.
This is what Oracle calls flashback query -a feature packed with Ora10g
Hard to accomplish such a thing manually but it can be done.
The easy part would be storing the data... the secret would be to keep previous image of all changed rows including version and timestamp columns
The hard part would be retrieving the data... the person writing queries would have to be very aware of the overall architecture otherwise it could be a nightmare.
_____________________________________
Pablo (Paul) Berzukov
Author of Understanding Database Administration available at Amazon and other bookstores.
Disclaimer: Advice is provided to the best of my knowledge but no implicit or explicit warranties are provided. Since the advisor explicitly encourages testing any and all suggestions on a test non-production environment advisor should not held liable or responsible for any actions taken based on the given advice.May 21, 2009 at 12:43 am
Hi,
Thanks for the response. Where can I get an example of how such queries are written?
May 22, 2009 at 3:53 pm
Something I did with maintaining revisions of important data was on every update to push the entire record to a backup table with exactly the same structure. With UserID and ModifiedOn fields we can maintain a full audit trail of who did what when.
I guess the best way of acheiving this is with triggers? But being a lazy app developer I just did this in the application's data layer rather than researching how to acheive it with database engine 😛 (no, i know. it isn't big or clever.)
May 23, 2009 at 4:14 am
Hi,
I though of doing that on the server but my sql is not that powerful. I guess for the time being I'll try out what you said. Probably save the entire form data as xml. Thanks.
Viewing 5 posts - 1 through 4 (of 4 total)
You must be logged in to reply to this topic. Login to reply