How do you store business activities in a SQL database?

Question

The goal is to store activities such as inserting, updating, and deleting business records.

One solution I'm considering is to use one table per record to be tracked. Here is a simplified example:

CREATE TABLE ActivityTypes
(
    TypeId              int IDENTITY(1,1)       NOT NULL,
    TypeName            nvarchar(50)            NOT NULL,

    CONSTRAINT PK_ActivityTypes          PRIMARY KEY (TypeId),
    CONSTRAINT UK_ActivityTypes          UNIQUE (TypeName)
)

INSERT INTO ActivityTypes (TypeName) VALUES ('WidgetRotated');
INSERT INTO ActivityTypes (TypeName) VALUES ('WidgetFlipped');
INSERT INTO ActivityTypes (TypeName) VALUES ('DingBatPushed');
INSERT INTO ActivityTypes (TypeName) VALUES ('ButtonAddedToDingBat');

CREATE TABLE Activities
(
    ActivityId          int IDENTITY(1,1)       NOT NULL,
    TypeId              int                     NOT NULL,
    AccountId           int                     NOT NULL,
    TimeStamp           datetime                NOT NULL,

    CONSTRAINT PK_Activities                      PRIMARY KEY (ActivityId),
    CONSTRAINT FK_Activities_ActivityTypes        FOREIGN KEY (TypeId)
                                                  REFERENCES ActivityTypes (TypeId),
    CONSTRAINT FK_Activities_Accounts             FOREIGN KEY (AccountId)
                                                  REFERENCES Accounts (AccountId)
)

CREATE TABLE WidgetActivities
(
    ActivityId          int                     NOT NULL,
    WidgetId            int                     NOT NULL,

    CONSTRAINT PK_WidgetActivities                  PRIMARY KEY (ActivityId),
    CONSTRAINT FK_WidgetActivities_Activities       FOREIGN KEY (ActivityId)
                                                    REFERENCES Activities (ActivityId),
    CONSTRAINT FK_WidgetActivities_Widgets          FOREIGN KEY (WidgetId)
                                                    REFERENCES Widgets (WidgetId)
)

CREATE TABLE DingBatActivities
(
    ActivityId          int                     NOT NULL,
    DingBatId           int                     NOT NULL,
    ButtonId            int,

    CONSTRAINT PK_DingBatActivities                  PRIMARY KEY (ActivityId),
    CONSTRAINT FK_DingBatActivities_Activities       FOREIGN KEY (ActivityId)
                                                     REFERENCES Activities (ActivityId),
    CONSTRAINT FK_DingBatActivities_DingBats         FOREIGN KEY (DingBatId)
                                                     REFERENCES DingBats (DingBatId)
    CONSTRAINT FK_DingBatActivities_Buttons          FOREIGN KEY (ButtonId)
                                                     REFERENCES Buttons (ButtonId)
)

This solution seems good for fetching all activities given a widget or dingbat record id, however it doesn't seem so good for fetching all activities and then trying to determine to which record they refer.

That is, in this example, all the account names and timestamps are stored in a separate table, so it's easy to create reports focused on users and focused on time intervals without the need to know what the activity is in particular.

However, if you did want to report on the activities by type in particular, this solution would require determining to which type of activity the general activity table refers.

I could put all my activity types in one table, however the ID's would not be able to be constrained by a foreign key, instead the table name might be used as an id, which would lead me to use dynamic queries.

Note in the example that a DingBatActivity has an optional button Id. If the button name were to have been edited after being added to the dingbat, the activity would be able to refer to the button and know its name, so if a report listed all activities by dingbat and by button by name, the button name change would automatically be reflected in the activity description.

Looking for some other ideas and how those ideas compromise between programming effort, data integrity, performance, and reporting flexibility.

Answer 1

The way that I usually architect a solution to this problem is similar to inheritance in objects. If you have "activities" that are taking place on certain entities and you want to track those activities then the entities involved almost certainly have something in common. There's your base table. From there you can create subtables off of the base table to track things specific to that subtype. For example, you might have:

CREATE TABLE Objects   -- Bad table name, should be more specific
(
     object_id     INT          NOT NULL,
     name          VARCHAR(20)  NOT NULL,
     CONSTRAINT PK_Application_Objects PRIMARY KEY CLUSTERED (application_id)
)

CREATE TABLE Widgets
(
     object_id     INT           NOT NULL,
     height        DECIMAL(5, 2) NOT NULL,
     width         DECIMAL(5, 2) NOT NULL,
     CONSTRAINT PK_Widgets PRIMARY KEY CLUSTERED (object_id),
     CONSTRAINT FK_Widgets_Objects
     FOREIGN KEY (object_id) REFERENCES Objects (object_id)
)

CREATE TABLE Dingbats
(
     object_id     INT           NOT NULL,
     label         VARCHAR(50)   NOT NULL,
     CONSTRAINT PK_Dingbats PRIMARY KEY CLUSTERED (object_id),
     CONSTRAINT FK_Dingbats_Objects
     FOREIGN KEY (object_id) REFERENCES Objects (object_id)
)

Now for your activities:

CREATE TABLE Object_Activities
(
     activity_id     INT          NOT NULL,
     object_id       INT          NOT NULL,
     activity_type   INT          NOT NULL,
     activity_time   DATETIME     NOT NULL,
     account_id      INT          NOT NULL,
     CONSTRAINT PK_Object_Activities PRIMARY KEY CLUSTERED (activity_id),
     CONSTRAINT FK_Object_Activities_Objects
     FOREIGN KEY (object_id) REFERENCES Objects (object_id),
     CONSTRAINT FK_Object_Activities_Activity_Types
     FOREIGN KEY (activity_type) REFERENCES Activity_Types (activity_type),
)

CREATE TABLE Dingbat_Activities
(
     activity_id     INT     NOT NULL,
     button_id       INT     NOT NULL,
     CONSTRAINT PK_Dingbat_Activities PRIMARY KEY CLUSTERED (activity_id),
     CONSTRAINT FK_Dingbat_Activities_Object_Activities
     FOREIGN KEY (activity_id) REFERENCES Object_Activities (activity_id),
     CONSTRAINT FK_Dingbat_Activities_Buttons
     FOREIGN KEY (button_id) REFERENCES Object_Activities (button_id),
)

You can add a type code to the base activity if you want to for the type of object which it is affecting or you can just determine that by looking for existence in a subtable.

Here's the big caveat though: Make sure that the objects/activities really do have something in common which relates them and requires you to go down this path. You don't want to store disjointed, unrelated data in the same table. For example, you could use this method to create a table that holds both bank account transactions and celestial events, but that wouldn't be a good idea. At the base level they need to have something in common.

Also, I assumed that all of your activities were related to an account, which is why it's in the base table. Anything in common to ALL activities goes in the base table. Things relevant to only a subtype go in those tables. You could even go multiple levels deep, but don't get carried away. The same goes for the objects (again, bad name here, but I'm not sure what you're actually dealing with). If all of your objects have a color then you can put it in the Objects table. If not, then it would go into sub tables.

Answer 2

New answer, based on an different interpretation of the question.

Are you just trying to keep a list of what has happened? If you just need a ordered list of past events, you just need 1 table for it:

action_list
  action_list_id (pk)
  action_desc (varchar)

event_log:
  event_log_id (pk)
  event_time (timestamp)
  action_list_id (fk)
  new_action_added (fk)
  action_details_or_description (varchar)

In this, the action_list would be something like:

1   'WidgetRotated'
2   'WidgetFlipped'
3   'DingBatPushed'
4   'AddNewAction'
5   'DeleteExistingAction'

The event_log would be a list of what activities happened, and when. One of your actions would be "add new action" and would require the 'new_action_added' column to be filled in on the event table anytime the action taken is "add new action".

You can create actions for update, remove, add, etc.

EDIT: I added the action_details_or_description column to event. In this way, you can give further information about an action. For example, if you have a "product changes color" action, the description could be "Red" for the new color.

More broadly, you'll want to think through and map out all the different types of actions you'll be taking ahead of time, so you can set up your table(s) in a way that can accurately contain the data you want to put into them.

Answer 3

The last time I needed a database transaction logger I used an Instead Of trigger in the database so that it would instead of just updating the record, the database would insert a new record into the log table. This technique meant that I needed an additional table to hold the log for each table in my database and the log table had an additional column with a time stamp. Using this technique you can even store the pre and post update state of the record if you want to.

Answer 4

I'm going to go out on a limb and take a few wild guesses about what you're really trying to accomplish.

You say you're trying to track 'store activities' I'm going to assume you have the following activities: Purchase new item Sell item Write off item Hire employee Pay employee Fire employee Update employee record

Ok, for these activities, you need a few different tables: one for inventory, one for departments, and one for employees

The inventory table could have the following information:

inventory:
  item_id (pk)
  description (varchar)
  number_in_stock (number)
  cost_wholesale (number)
  retail_price (number)
  dept_id (fk)

department:
  dept_id (pk)
  description (varchar)

employee
  emp_id (pk)
  first_name (varchar)
  last_name (varchar)
  salary (number)
  hire_date (date)
  fire_date (date)

So, when you buy new items, you will either update the number_in_stock in inventory table, or create a new row if it is an item you've never had before. When you sell an item, you decriment the number_in_stock for that item (also for when you write off an item).

When you hire a new employee, you add a record from them to the employees table. When you pay them, you grab their salary from the salary column. When you fire them, you fill in that column for their record (and stop paying them).

---

In all of this, the doing is not done by the database. SQL should be used for keeping track of information. It's fine to write procedures for doing these updates (a new invoice procedure that updates all the items from an invoice record). But you don't need a table to do stuff. In fact, a table can't do anything.

When designing a database, the question you need to ask is not "what do I need to do?" it is "What information do I need to keep track of?"

Answer 5

How about the SQL logs?