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?

  • In my opinion it depends how you want to store a changelog if required. The straigt forward approach would be using 3 Tables with the same stucture + additional fields changetime/changeuser/comment etc. filled initial from triggers. – bummi Nov 18 '12 at 19:08

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

  ID                  NUMBER(9)                 NOT NULL,  --primary key
  EVENT_ID            NUMBER(9)                 NOT NULL,
  ENTRY_DATE          DATE                      NOT NULL,
  MESSAGE             VARCHAR2(200 CHAR)        NOT NULL,

--table for "Car" logging

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

--another child table

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

| improve this answer | |

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

| improve this answer | |

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.