5

The only thing closest to what I'm developing is Facebook's activities log for users. So I figured if I could ask about how FB might possibly implement such a feature at the database layer, then it might give me some insight on how to solve my own similar problem.

How would one design a schema in a MySQL database to hold all the activities of a specific user. In FB's case, you have many activities that a user might be able to do, like something, comment on something, add a friend, use an app, etc. All these activities have their own database schema, I'm sure, so I'm assuming that the activities log schema would need to reference these other data somehow.

So what I'm asking is for ideas. The only ideas I can come up with is to create an activities table and multiple "connecting tables", tables that reference the activities table and the activity data (e.g. comments on something, likes something, etc.). However, this schema would require a new table for every new kind of activity and new business logic to handle these new tables. Maybe one can't get around this, but I'm interested in a schema that would require very little updating to the schema and business logic when new kinds of activities are added to the system.

Just to be clear, this question isn't intended to sound like I'm just simply trying to build another Facebook clone. I only use FB as a reference to something similar to what I'm trying to do. To be more abstract about my question: I'm trying to aggregate various kinds of data in my database into a single schema that could be queried, and anytime there is a new kind of data, there should little if any schema changes, otherwise what's the point? FB has two instances of this kind of aggregation, activity logs and notifications (that I can think of).

Any help is appreciated.

1
  • I'm not familiar with MySQL, but in SQL Server, I might implement this as a single activity table with sparse columns for the attributes.
    – Jon Seigel
    Aug 13, 2012 at 2:58

2 Answers 2

3

Relational DBMS is not ideally suited to this type of application. RDBMS is great for tracking and enforcing referential integrity for relations between two entities, THING A and THING B. What you want to do is to track relationships between THING A and every other thing in your database (or lots of other things, at least).

Your options, each of which include some form of compromise, include:

  • Sparse Foreign Key Columns: Create an activity table that has mutually exclusive foreign key columns that point to the various kinds of activity that you want to track. This is a fully 3NF way of doing things, but it still requires new columns to be added for new kinds of activities and it results in sparse columns, which people try to avoid as a rule.

  • Forrest of Tables: Create individual activity tables each of which track a particular type of activity. You can then create a query or view that uses UNION ALL to bring the various kinds of activity together into one place for querying. This is also very by the book with respect to normal forms, but you end up with a database cluttered with activity tables and you have to create a new table and alter a view whenever you add a new type of activity.

  • Data Driven Joins: Use a single activity table that captures the type of the activity and the instance of the activity in one place. You would need to have more sophisticated logic built for retrieval of the activity details, since this approach foregoes formal foreign keys. There is no declarative referential integrity and the approach smells a lot like EAV, which makes many people's skin crawl. On the other hand, you wind up with a single table, no sparse columns, and the addition of a new activity type requires no schema changes, only a tweak to one piece of code - the one that reads type of activity and knows what to do to go and retrieve the details of that activity.

You need to look at where your sensitivities are going to lie. How many types of activity will you ultimately be tracking? How often are new types of activity going to be added to the mix? Are you prepared to endure the scorn of colleagues when they see you've implemented a solution with sparse columns (or worse) EAV? When you answer these questions you'll be able to pick the approach which makes the most palatable compromises for your application.

3
  • For the Forest of Tables idea, what if you had a single activities table that relates an activityId to the users table's userId. Then you had other tables that relates other data to the activities table. This way you can use a JOIN instead of a UNION ALL. (I'm not sure if JOINs are faster)
    – Sam
    Aug 13, 2012 at 19:23
  • Also, I'm not sure I understand th Data Driven Joins description. Are you saying just store the "type" as a VARCHAR or similar, then have the application layer (business logic) handle the querying of the related data? If I want to ad a new type of data, then I'll need to add it only at the application level, along with the additional business logic for aggregating this data?
    – Sam
    Aug 13, 2012 at 19:26
  • @Sam - That's right, in the data driven approach you have a column that indicates which activity table to join (plus another to indicate which record in that table). You could use anything, varchar, char, int - it doesn't matter. The idea is that you have one piece of application logic that knows how to interpret the "activity type" and "activity instance" columns and pull back the right details for presentation according to your needs. That way you have just the one business logic change to make when a new activity type is created.
    – Joel Brown
    Aug 13, 2012 at 21:04
1

Here is a fourth option to add to Joel Brown's list. +1

  • Detail Tables + Log Table: Design detail tables for each type of activity that reflect the nature of the activity. Column names, types, and sizes would be appropriate for the data they contain. Constraints and indexes as well as retention, archiving, and backup policy can all be designed appropriately. One sequence could be used to provide keys for all tables. This key along with a table identifier could be used in a log table.

    A log table would be faster than a UNION ALL from multiple tables and much simpler to add new activities to. When a new event is added to a detail table, the log table could be populated with the detail sequence, detail table identifier, user key, and summary of the event that could be used for display purposes.

In the interface, if the user wants more information about the activity than the log entry can provide, the entry could be joined with the appropriate table. Some activities could forgo a details table if the log entry is sufficient. When new activities are added, the detail table, log entry, and log display could each be created and added in any order. The definition of the log table would not have to change, nor would the definition of other detail tables.

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge that you have read and understand our privacy policy and code of conduct.

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