Referential Integrity – Re-using table

database-designreferential-integrity

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?

Best Answer

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

Related Question