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.

It looks like your goal is to keep a log of the changes that have happened. If this is correct, why not simply set up your database to keep the logs for you? All major databases that I'm aware of can be set up to log what happens. Taking a step back, what are you trying to accomplish? What information are you trying to hold in your database? I'm very confused by what you've written.
–
David OneillDec 19 '09 at 1:22

I hadn't considered using the SQL logs for what I think qualifies as domain information. Is it common practice to mix SQL log results with domain results in a webpage view or report? Sorry about the general example, I modified my example to elaborate my problem.
–
Ed IDec 19 '09 at 1:33

5 Answers
5

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:

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.

That's a good half-way approach I. I'm realizing one table per activity requires way too many tables and joins, even on a simple database. But I have some activities that will be based on the state of a given record in the present, so I will definetely need more than one activity table.
–
Ed IDec 19 '09 at 22:02

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?"

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.

Thanks for understading my design goal, David. Using one table would be simple, however you need to store the exact description of the activity. For example "Product color was changed to red". If I had a table for each type of activity, such as as ProductColorChange table, I would be able to query on those fields and generate the same description from the results, or even change a template that would affect the display of all such descriptions. I'm just worried if I get started down this path the number of tables and JOIN queries will get out of hand, but I think I'm going to give it a try.
–
Ed IDec 19 '09 at 2:11

You certainly don't want a separate table for each type of activity, unless there is fundamentally different information you are tracking for different types of activities. I've edited my answer a bit. One other note: please consider accepting an answer if it answers your question. It's how this site works.
–
David OneillDec 19 '09 at 3:13

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.

This sounds like an approach that can be used in combination with one table per activity type. I need to weigh the added complexity of using triggers against the value of automatically having the activities stored. Very useful link you posted.
–
Ed IDec 19 '09 at 2:47