I am implementing an account "ability" type thing - a users account is given the abilty to do one or many things in the system, like (simple examples) cancel orders or pay invoices. I have a table like this:
ac_id ability active 1 CANCELORDER Y 1 PAYINVOICE Y 1 EDITOREDR Y 2 PAYINVOICE Y 2 PAYWAGES Y 2 CANCELORDER Y
ac_id is a link to the account table.
When a user account is first created with some abilities it is easy to insert the abilities into the table with an insert statement.
When a user account's ability list is changed updating seems to be tricky. Here's what I think needs to be done:
list DB ABC ABC GHI DEF JKL JKL MNO MNO
If list contains the new list of abilities for an account and DB contains the current list for that account we need to
- remove DEF from the DB list because it is not in the new list.
- add GHI into the DB list.
The first is straightforward SQL like:
update userabilitytest set active = 'N' where account_id = 1 and active = 'Y' and ability not in (''ABC', 'GHI', 'JKL', 'MNO' )
(which sets the active flag to 'N' rather than actually deleting).
However, I can't think of a way of doing the 2nd part. I have it working by figuring out whether the ability is already in the DB for that user in my application code, which does insert for each not-yet-in-the-db ability.
It would be nice to push this into SQL and run on the server.