DB Design: how to enforce integrity

Question

Assume I want to log certain things in an activity log in my database. In some cases, these activities are associated with a user (e.g. a user created something). In some cases, these activities are automatically done by the application (e.g. the system updated some record for some reason). I want to be able to keep track of who did what in the system. Keeping track of what users do seems simple:

TABLE: user
- user_id (PK)

TABLE: activity_log
- activity_log_id (PK)
- user_id (FK)

The above design allows me to associate each activity to a particular user (and, thus, enforce referential integrity). However, what if it's the system that took action? It doesn't have a user_id I can insert in the activity_log.

Should I:

• Just insert, say, 0 as activity_log.user_id to represent the system even if 0 does not exist in user table? InnoDB won't let me do that, right?
• Create a user specifically to represent the system. The problem is that each account owner in my application may require it's own "system user" so that I can later distinguish between one account's system user over another account's system user. Maybe this might get ugly. Not sure.

Suggestions?

Answer 1

One of the problems of enforcing referential integrity in logging and auditing applications is that you can't delete a user. (Or whatever thing you're auditing.) You can make a good argument for both cases,

• live with that restriction, or
• drop that referential constraint.

Answer 2

You could allow your activity_log.user_id to contain NULLs, then a NULL would indicate that no user performed the action and "no user" means "the system did it".

If you don't want to use NULL to indicate that "the system did it", then you could add a user to your users table and use that user as a placeholder for the "the system did it".