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

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?

Upvotes: 3

Answers (5)

Branko Dimitrijevic

Reputation: 52137

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...

Upvotes: 4

Costin Rizan

Reputation: 3

There are multiple solutions to your problem. You can 1. logically delete the user and keep the data intact or you can 2. log the user key only in the Log table.

The first solution is great if you have a small/medium number of users. But it has the performance downside: if the application grows and the number of users gets bigger, then in the Users table you will still have users that are not active and those records will slow down the database response (for example, during login operation).

The second solution is good, regardless of the number of users your application will have over the time. But you will lose the data of the users you deleted. Thus, you will not know the details of a deleted user which made some Activity 5 years ago.

There is a third solution: 3. using table 'UserArchive' This one combines the advantage of the first solution, and avoids the problem with the second solution. You can build a new table called UserArchive that will contain the data for all the users which existed in your application. Linking the Log table with the UserArchive table gives you the data for evey user that made some Activity even though the user does not exist anymore in the Users table.

When the user registers, you can add the needed details in the Users table and some important (or all) details in the UserArchive table. (notice the data redundancy, it's up to you if this is good or bad for your database) When a user wants to delete the account, you are free to make the hard delete in the Users table as the important (or all) details are stored in the UserArchive.

Advice: the Users table should not be connected with the UserArchive, as it is not neecssary from a logical point of view.

It's up to you which solution is best for your application.

Upvotes: 0

Luis LL

Reputation: 2993

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

Physical delete to Users but not logs? I think that such concept requires rethinking and prioritization.

Before deleting I would consider hide the user.

Otherwise what's the point of keeping the log if you can't tell who did what.

If instead of deleting users you archive them, FK is not really a FK for check but just a suggestion.

Upvotes: 2

Arghya C

Reputation: 10078

I would suggest no-FK (1) Though we can create a FK relation, it doesn't have to be a relation really. Means, its not very meaningful to say, an user has many userevents...event has a separate purpose. (2) We can allow anonymous/guest users, they should record events too!

Upvotes: 1

Joni

Reputation: 111339

As you yourself have seen, there are pros and cons to both approaches. Personally, I prefer not to put foreign keys in log tables so the only time you write to the table is when you append new records, and the storage for the log table can be optimized for appends. As a benefit you can maintain log records about deleted items with their original IDs.

Upvotes: 1

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

Answers (5)

Related Questions