1

I would like to ask if how do you deal with foreign keys?
Do you cascade delete, or just mark it as deleted but its there?
Here is my sample:

Users(table) 1 ------ * Transactions(Table that has userId)
                             1
                             |
                             |
                             *
Items(table) 1 ------ * TransactionItems(Table That has ItemId)

(this scenario is for sales transactions )

Do if I delete a user that is being used in a transaction all transactions that have that user id will be deleted and that is not ok of course..

The simple answer might be to not allow the user of the application to delete a user record that is being referenced. So this means that you must not allow cascade delete right?

So, if I use cascade delete on the relationship between Transactions and TransactionItems would that be okay? Since its not being referenced.

PJSimon
  • 343
  • 1
  • 18
John Patrick Po
  • 169
  • 1
  • 9

1 Answers1

0

I would add a bit or bool field to the Users and Items tables called "Active." Set it TRUE for all records. When a User or Item needs to be "deleted," set the bit to FALSE. Change all your queries in your application to filter the Users and Items tables WHERE Active = 'TRUE' so that the application only sees "non-deleted" Users or Items.

This will preserve the userId for things such as historical reports (you can join the Transactions table to the Users table and still match on all usersId's in the Transactions table), but allows for a logical "deletion" from the perspective of your application.

The same applies for itemId in the TransactionItems table; your join to the Items table will still match on all itemId's.

For the relationship of Transactions to TransactionItems, since no two Transactions records can relate to the same TransactionItems record, you can set the relationship to Cascade Delete, so that when a record from the Transacations table is deleted, all related TransactionsItems records are deleted, as well. (Any Items related to those deleted TransactionItems would still remain.)

PJSimon
  • 343
  • 1
  • 18
  • 1
    hi thanks for your answer. i had that in mind before.. so the application user will press the delete button but its just being flaged deleted in the db and doesnt show up in queries.. thanks.. but how about the relationship between transactions and transaction items must i put a cascade delete on their relationship? – John Patrick Po Jul 12 '13 at 18:10
  • I didn't understand that Transactions and TransactionItems are other tables... so in that case, you would set Cascade Delete on the relation between those tables. I added to my original answer. – PJSimon Jul 12 '13 at 19:06
  • How do you handle logging or audit trail tables? reference? or actual data? or any indicator column for the action? – John Patrick Po Jul 13 '13 at 13:58
  • Some good discussions here that might answer your questions: http://stackoverflow.com/questions/1071513/how-would-i-use-an-audit-trail-to-display-which-fields-have-ever-been-edited – PJSimon Jul 18 '13 at 06:36