Please start any new threads on our new
site at https://forums.sqlteam.com. We've got lots of great SQL Server
experts to answer whatever question you can come up with.
| Author |
Topic |
|
Dumps
Starting Member
10 Posts |
 Posted - 2010-02-02 : 05:58:07
|
| Hi,I have a requirement to create a system that does the following;For each table:- Allows users to enter data into a temporary table via a form. This table will not have constraints on it, so will allow nulls etc. - Once data entry is complete, the user will commit the data to a permanent storage table that will enforce the necessary constraints (not null, key constraints etc) and reject the committed record if it fails to meet the constraints.So far easy enough.However, if a user subsequently needs to edit the 'committed record'- The record is considered in a 'temporary' state (and maybe moved to the temporary table?)- User edits the record including a reason why the edit was required- Once the edit is completed, the user re-commits the record as aboveThe problem is the moving back from the committed storage. I'm not sure what the best way to do this is, but I can think of two options;1. Delete the record from committed and copy to temporary. When recommitted the record would have a 'reason for amendment' description attached2. Flag the committed record as in a 'temporary' state without physically moving it and add the 'reason for amendment' description, then 'commit' it and removing the temp state flag.The first is problemmatic because deleting the record will break referential integrity, so I would need a procedure that automatically ran the process for all dependent records. Then I would need a way of moving these back once the main record change is committed (unless of course these were also then edited, in which case a 'reason for amendment' would also be required).The second is problemmatic because the user would not be able to make the required edits. For example, they may wish to make a field null and the constraints on the 'committed' storage would not allow this.I can only see the first option as workable, but this would be a fairly complex process to implement. I would need to make the stored procs reusable so this process can be applied to any tables requiring this functionality.Does anyone have any examples or suggestions as the best way to proceed, or have any other ways I could manage this sort of functionality?Thanks |
|
|
Lumbago
Norsk Yak Master
3271 Posts |
Posted - 2010-02-02 : 07:16:57
|
| Do you really need the temporary table? I haven't thought this through completely but my initial instinct tells me that it would be better to just add a new column in the production table that says if this record is being edited or not, and maybe also by who. It would be fairly easy to filter out these records when doing selects...and since the data entered needs to conform to the constraints/foreign keys/whatever anyway I see no reason why allowing nulls and such is necessary. Maybe I'm not seeing the full picture...- LumbagoIf the facts don't fit the theory, change the facts. Albert Einstein |
 |
|
|
Dumps
Starting Member
10 Posts |
Posted - 2010-02-02 : 08:04:58
|
| The reason why I chose this design is because we do not always receive all 'required' data. To explain a little - we receive data that is initially recorded on paper - for instance, a list of lab results for a patient. Whilst all results are required to be completed, the fact of the matter is that sometimes there are omissions. I initially built a db using the usual constraints etc, but if there is an omission for one result then none of the supplied lab results can be recorded due to conflict with the null constraint for the missing one. The lab results list is standard, so each set of results are recorded in this format - patient_fk, lab_result1, lab_result2, lab_result3 ... lab_resultN. In this instance, it might be possible to change this table to a long thin one, but in other instances, this is not possible (ie. a record requires a start and end date and one of them is omitted).Your solution seems possible at first sight, but it is feasible that, for example, a record with an end date erroneously recorded may have to have this set to back to null which would violate the null constraint. In this case, I'd still need to keep the 'correct' columns, but allow null for the end date. Basically, I need a system that can accept 'incomplete' data which is identified as such (hence the temporary table), whilst allowing complete data to be stored and conform to the usual constraints that are applied to tables (ie. key, null, check, unique etc constraints). I suppose an altenative would be to have one 'anything goes' table, and run a process that checks each record for validity. I don't really want to go down this route though as I'd ideally like to take advantage of SQLs built-in constraint mechanism. |
|
|
|
Lumbago
Norsk Yak Master
3271 Posts |
Posted - 2010-02-02 : 08:14:09
|
I think stuff like this is going to be a pain almost no matter what you do. What do you do if someone wants to edit the already committed row, and then closes their application/web browser before committing again? I'm not sure if I know of any smart solutions that will help solve your problem...hopefully some of the really clever people her do  - LumbagoIf the facts don't fit the theory, change the facts. Albert Einstein |
|
|
|
|
|
|
|
|
Maintaining temporary and committed records