Referential Integrity - Re-using table

Question

This is a simplified example of a design issue that I am facing: I have 3 tables: Car, Ship and Bicycle. I need to add an "activity logging" table that records user actions such as deletion and user comments etc. I was thinking of creating just one table. Rather than 3 tables. The problem is with ensuring referential integrity. Should I create 3 separate columns that link to these tables? Should there be one column and I use it when needed? What is the general recommendation in such case? or should I just create 3 separate tables?

Answer 1

I work with a database that has this solution that I outline below. I don't like it because the base table has an entry for each type and it ends up being a huge table that is slow to query.

--base table

CREATE TABLE APPLICATION_LOGGING
(
  ID                  NUMBER(9)                 NOT NULL,  --primary key
  CURRENT_USER_ID     NUMBER(9),
  EVENT_ID            NUMBER(9)                 NOT NULL,
  ENTRY_DATE          DATE                      NOT NULL,
  MESSAGE             VARCHAR2(200 CHAR)        NOT NULL,
  MESSAGE_PARAMETERS  VARCHAR2(2000 CHAR)       NOT NULL
)

--table for "Car" logging

CREATE TABLE CAR_APPLICATION_LOGGING
(
  ID       NUMBER(9)                            NOT NULL, --same as applogging ID
  CAR_ID  NUMBER(9)                            NOT NULL
)

--another child table

CREATE TABLE SHIP_APPLICATION_LOGGING
(
  ID          NUMBER(9)                         NOT NULL,
  SHIP_ID  NUMBER(9)                         NOT NULL
)

to get all the ship logs you would query for

select * from ship_application_logging ship, application_logging app
where ship.ID = app.ID

this is a clean design but if each type of thing generates ten or twenty log entries per thing you don't have to do too much to have a huge table for application_logging with a million entries. Users complain it's slow to see the activity log.

The real question is:

• how many of your users need to see the activity log?
• how often do they view it?
• will you be doing additional logging for financial or security purposes?
• do you need to log every activity a user does or just critical ones?

As @Steb says "it all depends". Your application, your users, number of transactions....

Answer 2

Arguments can be made either way depending on whether you want to handle populating these tables using triggers or the application(s).

I think this depends on what exactly you want to store in these logging tables, if you're logging anything specific to your 3 "data" tables then you'd be better off with 3 logging tables (1 logging table per "data" table which will be easier to populate using triggers), otherwise you can store everything in 1 table (with 1 field to reference each data table) which will make reporting easier.

As regards referential integrity, if you're actually deleting the original data record (rather than just marking it as deleted) then your Foreign Key will have nothing to link to anyway.

This is one of these situations where I de-normalize to gain centralized reporting and application logic (though triggers could be used for this, depending what you're storing).