Last post Sep 28, 2017 06:09 AM by Cathy Zou
Sep 27, 2017 04:37 AM|asplearning|LINK
Hello everyone, I have this table:
So here is some of my sample data in the ItemTable:
form_id item_name item_value active
1 Digging 1 1
1 Flying 3 1
1 Watching TV 5 1
1 Sleeping 100 1
I have another table call ItemSelectedTable that is going to store item_value, so I know which item was selected by the users. The table has two columns (user_id, item_value). Here is sample data:
My problem now is that the admin can delete and add the items any time they want. So they may remove Digging or watching TV if they want to. This is not a problem if the item value does not exist in the selected value table. However, how to solve it if the
values have already been inserted to the ItemSelectedTable? I actually could create primary key to stop the admin from delete the item if it existed in the other table. But this doesn't meet my client requirement. The same thing, if the user selected Digging
today and a couple days later, the admin changes Digging to Traveling, I still want to show Digging to reflect what it was that the user selected.
Any suggestion on how I could do this?
Sep 27, 2017 07:52 AM|PatriceSc|LINK
And so which behavior do you want when an admin delete an item? For example you could mark the main item as being "deleted" but keep him. The app would prevent to use it for new selections but older selections would still have this (or just cascade delete
this item if this is what you want).
For changing the label a competent admin should just never do that. OInce created an item is THIS item. The label could be enhanced but it should never be changed to ssomething entirely different. The alternate would be to copy the label or keep versions
if you really need to keep the label even if it just slightly changed.
Sep 27, 2017 10:15 AM|asplearning|LINK
Thanks. About deleting item, I can't figure out. On one hand, I want to keep the database as clean as possible so i want to remove it from the database. On the other hand if I deleted it from DB, the previous selection will be lost. One solution I could
think of is to check if item existed in the itemselected table. If it's not, then delete it. If it existed, mark it as deleted and insert a new record. But it seems a lot of overhead work and the number of not-so-useful records will build up very quickly for
the item table.
About updating, how could you keep different versions of the label in the database?
Sep 28, 2017 06:09 AM|Cathy Zou|LINK
I would recommend that for each table you want to track history, you have a second table with the identical format - plus a date column.
Whenever an edit is made, you insert the old record to the history table along with the date/time.
This has the benefit of keeping your 'real' tables as small as possible, but gives you a complete history of all the changes that are made.
However, it will come down to how you want to use this data. If its just for auditing purposes, this method is simple and has little downside except the extra disk space and little or no impact on your main system.