Foreign keys in log tables ... good or bad?

Question

this one is a design question.

Let's say i have a user table {UserKey, UserName} and i am logging some user Activity. so in the log table {UserKey, Activity}, i have a UserKey column. Now is it a good idea for the UserKey in the log table to be a Foriegn key to the user table?

as far as i can see,

Pros(ForiegnKey): No dangling records.

Cons(ForiegnKey): hard Deletes are not possible unless i remove the logs also, which is obviosly bad.

what is your suggestion? what else i am missing?

Answer 1

Cons(ForiegnKey): hard Deletes are not possible unless i remove the logs also, which is obviosly bad.

First of all this is not "obviously" bad. This is simply one way for dealing with dangling records. BTW, you don't have to do it from the client code - you can automate it via ON DELETE CASCADE referential action and let the DBMS do it for you.

The other is by having a NULL-able FK (and possibly using ON DELETE SET NULL).

---

You simply can't keep the records that are associated to a user that no longer exists in the database. Dangling records may keep the user key, but that key no longer has meaning and can even be reused by a new user (not likely if you use auto-incrementing ID, but still possible).

But you could "retire" the user (e.g. by setting a flag in the user table) and still keep all her records, and potentially have a background process that cleans-up retired users that are too old to matter anymore.

In any case, FK is the way for preventing dangling records, and I would be extremely reluctant of abandoning it. With proper indexing, there should be no performance problem and if you think there is, please measure and confirm it's actually there before doing anything else...