Django model design to analyse user activities on external websites

Question

I am working on an app that analyses the activities of users on some external sites. The contents of the target sites fall into three types:

• Forum post: A user can either start a thread or reply to an existing one, the reply will get an unique url;

• Blog entry: Any user can post. The blog entry gets an unique url, comments do not.

• News article: Not user posted, from a number of news sources. Comments do not have an unique url.

The focus of my app is on user activities, below are some possible questions:

• How often does user A post (with respect to types, daily, weekly, etc.)?
• What topics are the user mostly interested in? (for this, all threads will be tagged based on their original category and scanned keyword from title/content).
• Is user A tend to reply user B's posts more often then to user C's? (obviously this particular question suits more on forum posts and blog entries).

I plan to consolidate a Thread model to hold all three types of contents, do not address the diffrences in them, for example, theat the news source just as a regular user. The Thread model will have roughly 10 millions of records.

Below are the models that I came up in very rudimentary form. I have few questions that need your help:

1. Is the logic clear in the models?
2. Regarding the first_seen and last_seen fields in Nick model: if I can look up the Activity model inexpensively in runtime, I would rather not to have them, since the two fields need to be computed each time I insert a new record. What are my better options here?
3. There will be about 10 sites included, should I have another model just for sites?
4. The url field in Activity model exists only for a forum type, meaning quite a few millions of records (Blogs and News) will not have a value there, is this a good practice?

Thank you all very much! I appreciate any input!

# should i index some fields?
class Thread(models.Model):
    site = models.CharField(max_length=40)
    thread_type = models.CharField(max_length=20)
    category = models.CharField(max_length=20)
    title = models.CharField(max_length=200)
    pub_date = models.DateTimeField()
    url = models.URLField()
    hits = models.IntegerField()
    votes = models.IntegerField()

    def __str__(self):
        return self.title


class Nick(models.Model):
    site = models.CharField(max_length=40)
    username = models.CharField(max_length=40)
    first_seen = models.DateTimeField(null=True, blank=True)
    last_seen = models.DateTimeField(null=True, blank=True)

    class Meta:
        unique_together = ("site", "username")

    def __str__(self):
        return self.username


class Activity(models.Model):
    usernames = models.ForeignKey(Nick)
    threads = models.ForeignKey(Thread)
    post_date = models.DateTimeField(default=datetime.now, blank=True)
    is_original = models.BooleanField()
    url = models.URLField()     # in case a forum reply and not original

    def __str__(self):
        return Nick.username    # needs to be more informative

Update: The users of all sites are almost from the same population, they may register on multiple sites, may or may not use the same nickname. One of my research goal (in a later phase) is to explore the correlations between users of different sites.

Answer 1

1. Is the logic clear in the models?

Somewhat. Your narrative didn't touch on the Nick table, which I think many people would name a User table. You didn't make it clear if user alice on the site example1.com is different from alice on example2.com - that's a namespace issue. Your table implies they would be distinct, that there is a compound primary key on (site, username). That would be good DB modeling, but weird from a UX perspective, if you put yourself in Alice's shoes. I would personally lean toward designing this with a unified username namespace across all sites, and similarly a unified thread_id namespace. That way you're always free to shard it out later (or partition it out) by site, but you're never forced to.

The Thread table appears to me to be broken. That is, no PK is defined, and I don't even see an obvious one. You have thread_type, but no thread_id. Maybe pub_date has enough entropy for uniqueness? I'm willing to believe that if we're looking at posts from a single user, but site wide? No, that is difficult to believe. I recommend adding some unique identifier. Remember that Primary Key dictates physical layout on disk, and the details of which rows are near which other rows will affect query performance.

2. Regarding the first_seen and last_seen fields in Nick model: if I can look up the Activity model inexpensively in runtime, I would rather not to have them, since the two fields need to be computed each time I insert a new record. What are my better options here?

I'm not happy with the Activity model. Substantively, you haven't described a Primary Key. I propose compound PK (username, post_date), assuming the timestamps are high enough resolution, otherwise the more complex (username, post_date, thread_id). You can assign IDs how you like, in ranges or even as GUIDs, but the post_date will matter for query performance. Trivially, rename to singular "username" and singular "thread_id" (or "thread" if you really want). Probing Nick when computing str() seems silly, as you already have exactly the right username. I notice you chose to leave site out of this table.

Updating the last_seen and (hardly ever) the first_seen columns is not such a big burden. And suppose that last_seen is not part of the UX, not displayed on screen. Then you could choose not to update it in timely fashion. Or if it is displayed, you could let transient memcached track recent updates. However you do it, at some later time, like 5 minutes or hourly or daily, you could do a reporting run where you read max(post_date) from Activity and update Nick. Or keep it out of Nick and instead update some offline reporting table. I'm just saying you have lots of flexibility in how you handle this detail. Nothing wrong with denormalizing in the name of query performance. Just wait till you can benchmark two competing queries so you understand what you're trading off.

3. There will be about 10 sites included, should I have another model just for sites?

Not based on what you've told us so far. A GROUP BY (or DISTINCT) would soon extract the current set of sites from one of your existing tables. You've not yet invented any requirements that require mutable site attributes be stored in the database.

4. The url field in Activity model exists only for a forum type, meaning quite a few millions of records (Blogs and News) will not have a value there, is this a good practice?

Depends on your query details, but sure, looks fine the way it is. Many urls will be NULL or will be the empty string, depending on whether you choose to make it a nullable column. Either way, it's not like you're losing much.

For rows without an URL, I bet you could avoid writing the row in the first place. I bet a carefully structured thread_id could encode all of (is_original, low_timing_resolution_post_date, unique_thread_number) within 64 or even 32 bits.