JimmyCrackedCorn
Posts: 583
|
| Posted: 04/16/2007, 6:04 PM |
|
On many of our web systems the owner has exclusive control of the admin so they are responsible for their data integrity. However, more and more of our customers also allow their end-users to access and even update their own data. Therein lies the problem.
For example, let's assume a basic eCommerce system where I let end-users set up and manage their accounts. They enter their info and are free to change it any time they wish.
However, what if they place an order today and then decide they wish to disappear from the database so they go in and change all of their account data to bogus info the next day. Now, when we run reports of past activity we have bogus account info linked to past orders.
One suggestion our team had was to let them create their account but not update it but that idea was rejected as not customer-friendly enough.
Another idea we had was to "pretend" to update the customer's records when they change info but actually create a new record with the changed info and mark the previous record as archived. This way the old info stays linked to previous orders and the new info is used for all new orders.
As an enhancement to the above idea we could even place an activity indicator in each record so if there has been no order activity since the last create/update action, just go ahead and update the record but if there has been order activity then do the "create new/archive old" technique discussed above.
I'd like to hear other thoughts on this problem and whether the approach we are considering seems sound or if there is a better way.
And, any ideas as to how we could implement this using CCS? My guess is we would need to override the default update code and replace it with code to do the create new/archive old functionality.
_________________
Walter Kempees...you are dearly missed. |
 |
 |
mamboBROWN
Posts: 1713
|
| Posted: 04/16/2007, 9:19 PM |
|
JimmyCrackedCorn
How does that saying go Quote :THERE'S MORE THAN ONE WAY TO SKIN A CAT . There are many ways that you could approach this but I must say I like the last suggestion with the enhancement.
IMPLEMENTATION IDEAS
One way you could implement this is to have a separate update user table that will store user updates. If the updates meet the necessary requirements the old record can be archived ( a flag turned on or off) and the new record is added to the user table with the reference id from the old record. The new record now becomes the primary record for the user.
Another way you could implement this is that before the users approved changes takes place you can copy the old record to an archived table (saving the old record -DON'T FORGET the ID- in an archived table and replacing it with the new changes).
A third method is to copy the user data and to make it appear (to the user) that they are changing the record when actually they are changing a copy of the data which stores the reference id of the old record (in a hidden field) with it and deactivates the old user record.
These came of the top of my head. I actually forgot to ask how many users do you expect to handle with the application?? That may also determine with method is the best to use. Hopefully this will help you some.
|
|
|
|
JimmyCrackedCorn
Posts: 583
|
| Posted: 04/17/2007, 3:20 AM |
|
Thanks mamboBROWN for your thoughtful response!!
I would estimate we will have between 5000-10000 users. I think I understand your point...if every user changes their account info every month the number of records would go up quickly!
Following up on your comments...
I think it may be better to keep the archived record in the same table and retain its original ID. That way, any reports or order recalls will still reference the info that was correct at that point in time. But any new transactions would be done using the new record's ID. Does that make sense?
I was thinking it would go something like this...
User Joe Smith sets up an account and the database assigns him ID 561. Every time he purchases something an order item is placed in the orders table with the product ID, date and his ID, 561.
Now, Joe decides to change all of his information so we set an archive field to true in his record with ID 561 and we create a new record with Joe's changed info (and whatever info he did not change is copied from the old record). Now his ID is 1055.
When I retrieve any orders he placed with his original info, 561 will be in that order and it will get his info from that record (we could also display an alert that this is using an archived record). But when he places new orders or wants to review his account info we would always pull up the 1055 record.
Does this sound feasible? Am I missing anything that will bite me later on? :)
_________________
Walter Kempees...you are dearly missed. |
|
|
|
wcboyd
Posts: 46
|
| Posted: 04/24/2007, 5:46 AM |
|
JimmyCrackedCorn,
Here a couple ideas:
Theme & Variation on Previous thought:
I think an effective date or a "record deactivated date" might work better than a flag. If you go with the record deactivated date you could set the field to be NULL until the record is updated. Then you would create the new record with the updated information, leave the record deactivated date NULL and update the old record with with the date. That way you get the time/date when they updated their record plus you can include in your select statement "where record deactivated date = NULL" and get a single unique instance.
Of course, like it has been pointed out you now have to manage the database for deactivated records and deal with performance issues that were not there before.
So my suggestion would be to create an audit table. The audit table could be just a copy of the customer table with the additional key of the record deactivated date to make each one unique. Then you could place a button on the form to "View Previous Updates" and sort either ascending or descending. This also has the advantage of keeping your customer table neat and tidy. Plus, depending the database you could easily implement this in a trigger and not have to worry about writing the code to deal with this. CCS would be none the wiser which makes your code easier to maintain. Additionaly you can more easily manage the audit table since it will most likely not be as heavily used as the customer table.
_________________
Thanks,
Craig
"Stress is the body's way of saying you have not worked enough unpaid overtime." ~ Scott Adams |
|
|
|
JimmyCrackedCorn
Posts: 583
|
| Posted: 04/24/2007, 3:33 PM |
|
Thanks Craig..I appreciate your insights on this.
Quote wcboyd:I think an effective date or a "record deactivated date" might work better than a flag. If you go with the record deactivated date you could set the field to be NULL until the record is updated. Then you would create the new record with the updated information, leave the record deactivated date NULL and update the old record with with the date. That way you get the time/date when they updated their record plus you can include in your select statement "where record deactivated date = NULL" and get a single unique instance.
Of course, like it has been pointed out you now have to manage the database for deactivated records and deal with performance issues that were not there before.
Sounds like the same approach, just using date instead of a flag field. In either case we could check for NULL to get a unique instance. But having the deactivated date might be useful.
Quote :So my suggestion would be to create an audit table. The audit table could be just a copy of the customer table with the additional key of the record deactivated date to make each one unique. Then you could place a button on the form to "View Previous Updates" and sort either ascending or descending. This also has the advantage of keeping your customer table neat and tidy. Plus, depending the database you could easily implement this in a trigger and not have to worry about writing the code to deal with this. CCS would be none the wiser which makes your code easier to maintain. Additionaly you can more easily manage the audit table since it will most likely not be as heavily used as the customer table.
Not sure I follow you here. If I create a separate audit table that means I would need to change code to detect whether or not it is working on an active or deactivated record and then go to a different table in each case. Is that what you meant?
In my example above, where the deactivated record stays in the same table, the deactivated record retains its original ID in the database so all old order retrievals, for example, still work just fine since they are using that ID and looking for the record in the same table.
_________________
Walter Kempees...you are dearly missed. |
|
|
|
wcboyd
Posts: 46
|
| Posted: 04/25/2007, 5:33 AM |
|
JimmyCrackedCorn,
Yes, you are right. The first option discussed is the same thing, but with a date field or timestamp.
Quote :If I create a separate audit table that means I would need to change code to detect whether or not it is working on an active or deactivated record and then go to a different table in each case. Is that what you meant?
No, that is not what I meant. Here is how the scenario plays out:
1) User updates some field about themselves
2) The Update trigger on the Customer table fires and inserts a record into the Customer Audit table with the old values.
3) Now the Customer table has the updated information and still has only a single record per customer. The Customer Audit table contains the history of the changes.
If I am a customer that has never updated my data then I will have no records in the Customer Audit table. If, on the other hand, I have made six updates to my customer record then there will be six records in the Customer Audit table for me and still only one record, with the last set of updates, in the Customer table.
I will post a diagram of what I mean later this afternoon. I can't upload images to my website from work otherwise I would do it now.
As an alternative I could post the sample DDL. Would that be helpful in the meantime?
_________________
Thanks,
Craig
"Stress is the body's way of saying you have not worked enough unpaid overtime." ~ Scott Adams |
|
|
|
JimmyCrackedCorn
Posts: 583
|
| Posted: 04/25/2007, 11:37 AM |
|
Quote wcboyd:No, that is not what I meant. Here is how the scenario plays out:
1) User updates some field about themselves
2) The Update trigger on the Customer table fires and inserts a record into the Customer Audit table with the old values.
3) Now the Customer table has the updated information and still has only a single record per customer. The Customer Audit table contains the history of the changes.
If I am a customer that has never updated my data then I will have no records in the Customer Audit table. If, on the other hand, I have made six updates to my customer record then there will be six records in the Customer Audit table for me and still only one record, with the last set of updates, in the Customer table.
If I understand you, it sounds like there are two tables involved, Customer and Customer Audit. When the customer has only a single record in the system, any orders he places would have his ID (from Customer record) inserted into a record in the order table as a foreign key. Then when referencing his order we would get his detailed info from the Customers table using that foreign key.
But, after he changes his info record and the record is "moved" to the Audit table (presumably now with a different ID) the link to the old info record (in his past orders) is broken. On the other hand, if the old info record stays in the same table, the ID never changes and all old orders reference the correct ID.
I cannot seem to get my head around how it works with a separate audit table! I appreciate the benefits of keeping the Customer table clean but once you move records to another table it seems to me it breaks existing relationships. Maybe I'm missing something key here! :)
Thanks for your help.
_________________
Walter Kempees...you are dearly missed. |
|
|
|
wcboyd
Posts: 46
|
| Posted: 04/25/2007, 12:51 PM |
|
JimmyCrackedCorn,
Does this help?
The table outlined in red indicates a trigger is present.
_________________
Thanks,
Craig
"Stress is the body's way of saying you have not worked enough unpaid overtime." ~ Scott Adams |
|
|
|
JimmyCrackedCorn
Posts: 583
|
| Posted: 04/25/2007, 5:07 PM |
|
Quote wcboyd:Does this help?
not sure! :)
I'm not intimately familiar with triggers but I assume what you are showing is that a trigger (stored procedure) occurs whenever someone updates their record in the Customers table. That trigger causes the old info to be written to a new record in the Audit table and the updated info to be written into the existing record in the Customers table.
I see how this is desirable as behavior that can happen "behind the scenes" from CCS. But what I don't see is how I make use of the Audit table from my order history functions.
Currently when an order is placed, a new record is inserted into the Orders table. This record includes ID from the Products table, ID from the Customers table, date/time stamp and a few other things.
As long as my customer's info does not change, I can print his order by getting the order details from the Orders table, his info from the Customers table by using the CustomerID foreign key and the product info from the Products table by using the ProductID foreign key.
But because I actually store the CustomerID in the Orders table, if I let them change this record, the order history will always show the newest customer info regardless of whether that is the info that was used when that order was placed. And that is not correct!
For example, CustomerX is located in California and in January he orders 1000 widgets from us and has them shipped to his California facility. One month later he calls me and asks for a copy of his order and I pull it up and print it...no problem. It correctly shows his Califormia address as the shipping location.
Two months later his company moves to New York and he logs onto our web site and updates his account with the new info.
At the end of the year we are reviewing our orders and when we review CustomerX's January order it shows that we shipped 1000 widgets to his New York facility. The system is incorrectly using his current account info with an old order that was placed with his old account info.
So, my "solution" was whenever a customer updates their account (if they have had any transactions since their last update) leave the current record in the Customers table with its original ID and just mark it as inactive. That way, in my example above, calling up his January order at the end of the year will show the correct shipping address while any subsequent orders he placed (after moving to New York) will show New York as the shipping address.
In order to make this work with an Audit table as you describe, wouldn't my trigger code or other code actually have to,
1) determine the ID of the new Audit table record (containing the old info)?
2) find every order where the old CustomerID is used?
3) replace that CustomerID with the Audit table ID?
4) detect whether to get order info from the Customer table or the Audit table?
If these assumptions are correct it sure seems a lot easier to simply leave the old records in the Customer table as I described above.
Your thoughts?
_________________
Walter Kempees...you are dearly missed. |
|
|
|
wcboyd
Posts: 46
|
| Posted: 04/25/2007, 7:54 PM |
|
Ah. So the problem really isn't so much auditing changes to the customer record as it is tracking shipping addresses. I see that now. Thank you for patiently explaining your dilemma.
How about this?
With this method you don't loose any address history and if the customer wants to "update" one of his addresses he has to deactivate the old one and key in a new one plus, he has to tell you the type of address it is. Your order history is intact and your customers can have multiple "Ship To:" addresses active or inactive. BTW, the Deactivation Date should be NULL until the customer wants to deactivate it, thus it is your address active/inactive "flag". Also note, triggers are not required for this design.
_________________
Thanks,
Craig
"Stress is the body's way of saying you have not worked enough unpaid overtime." ~ Scott Adams |
|
|
|
JimmyCrackedCorn
Posts: 583
|
| Posted: 04/25/2007, 9:12 PM |
|
Quote wcboyd:Ah. So the problem really isn't so much auditing changes to the customer record as it is tracking shipping addresses.
Well, shipping was only one example. Basically we feel everything the end-user can change should be protected in some way. Our customers are very nervous about end-users now having the power to screw with data they've never had access to in the past!
Quote :How about this?With this method you don't loose any address history and if the customer wants to "update" one of his addresses he has to deactivate the old one and key in a new one plus, he has to tell you the type of address it is. Your order history is intact and your customers can have multiple "Ship To:" addresses active or inactive. BTW, the Deactivation Date should be NULL until the customer wants to deactivate it, thus it is your address active/inactive "flag". Also note, triggers are not required for this design.
Interesting but it still seems more complicated than simply retaining all changed records. By retaining them you don't have to do anything but deactivate them; they stay available for any use in reports where they are already linked in. When working with new transactions you just add a condition that you only use the active customer record. The old ones are only used for historical purposes.
If this starts causing the table to use too much space you could always set up a process to purge/archive them after a certain period of time.
I really cannot see any problems with the method I'm describing so we will probably try and implement it using CCS. Now that's a whole nother challenge! I could hand code this fairly easily but I'd like to get under the hood of CCS and change its operation to do this for me. I'll post my results back here.
Thanks for your inputs.
_________________
Walter Kempees...you are dearly missed. |
|
|
|
wcboyd
Posts: 46
|
| Posted: 04/26/2007, 5:09 AM |
|
JimmyCrackedCorn,
I wish you the best of luck.
Here is a little light reading when you get some free time:
http://en.wikipedia.org/wiki/Database_normalization 
_________________
Thanks,
Craig
"Stress is the body's way of saying you have not worked enough unpaid overtime." ~ Scott Adams |
|
|
|
Wkempees
|
| Posted: 04/26/2007, 6:28 AM |
|
That "light"reading is indeed the way to the light.
Although not standard practice, nor the default way to do it,
basically it boils down to:
The customer can edit its details as much as he likes.
When an order is entered the latest client info is used and stored in
the order!, when order is status:final it cannot be altered.
When an invoice is generated the customer details (invoice address
shipping address atc) is stored in the invoice!
(Along with shipping fees, VAT fees etc etc, these can no longer be
linked to real data.
This will on the one hand guarantee data integrity on the other you will
always be able to reproduce an order/invoice as it was at the time of
creation.
Walter
|
|
|
|
JimmyCrackedCorn
Posts: 583
|
| Posted: 04/26/2007, 4:54 PM |
|
Quote wcboyd:
thanks for the link...I've been there a few times before.
almost seems that a bigger challenge than normalizing is knowing when to denormalize!
_________________
Walter Kempees...you are dearly missed. |
|
|
|
JimmyCrackedCorn
Posts: 583
|
| Posted: 04/26/2007, 5:04 PM |
|
Quote Wkempees:the way to the light.
but sometimes the light is a train! :)
Quote :The customer can edit its details as much as he likes.
When an order is entered the latest client info is used and stored in
the order!, when order is status:final it cannot be altered.
When an invoice is generated the customer details (invoice address
shipping address atc) is stored in the invoice!
(Along with shipping fees, VAT fees etc etc, these can no longer be
linked to real data.
This will on the one hand guarantee data integrity on the other you will
always be able to reproduce an order/invoice as it was at the time of
creation.
thanks Walter. it makes sense the way you describe. but I still want to have more of a complete history mechanism. like maybe this customer submits a request for quote or he submits a complaint or he sends us a glowing review! in any of these cases we would like to have his contact info at that point in time permanently stored.
of course, the way you describe would also work for this...just always copy their current customer info into each transaction table. but it seems cleaner to have all customer info (even with dupes) in a single customer table rather than pasting it into every transaction table.
I'll post some feedback as to how well this goes and whether we can get it integrated well using CCS.
Thanks again for the discussion.
_________________
Walter Kempees...you are dearly missed. |
|
|
|
|
