How to get database records number variations over time

Hello everyone,

My Users can choose Interests and potentially other entities can choose interests too (such as Sessions/Speakers, ...).

I've 3 tables : users, interests (id, name), interestables (interest_id, interestable_id, interestable_type, timestamps)

Basically what I want is to know the interest "popularity" (= number of users that has chosen a given interest) at a given moment (daily or hourly).

At the moment when a User chooses an interest, I create a record in the interestables table that has the id of the interest, the id of the user and the timestamps. And when he is not interested anymore, I delete the same record. If a User chooses an interest and delete it after a while, I've no way of knowing it was even selected by this user at that moment because the record doesn't exist anymore.

If I add Soft Delete on the interestables I feel it'll add some unnecessary complexity to the code and if a user checks, unchecks then check again an interest I've still lost the fact it was even unchecked at a given moment.

So how you guys do it?

rwdevguy

9 years ago

Best Answer

Level 11

You could add another table to the mix purely for the sake of reporting. It would mean that you would end up with some duplicated data but they serve a different purpose.

You could go about it a couple of different ways:

Leave the schema for you're interestables the same and pretty much duplicate that table but add a flag to say whether it was selected or deselected. This way you wouldn't have to change your applications current behaviour.

OR (If you really don't want to have as much duplication)

Use SoftDeletes and create a one-2-many between interestables and (the recording table). You would either still use a flag or just calculate the changes on the fly (assuming you'd only be tracking selects and deselects).

Either way, you can then just use Eloquent's event system to automatically record the changes.

Hope this helps!

depsimon

9 years ago

Level 16

Thanks for your reply @rosswilson252

I think this solution might work (the first one) but I have some concerns.

Is that not a problem to duplicate (basically making a new INSERT request) every time there's a change? I mean if 10 users are choosing their interests at the same moment and play with "check"/"uncheck". For that solution I'm thinking of using queues, though is it better to have 100 queues doing 1 INSERT query or doing the INSERT query while doing the operation?

If I go for this solution, I'd probably create a generic "activities" table to store all kind of activities. Like checking an interest, logging in, logging out, etc.. That table would soon store thousands and thousands of records and that scares me.

rwdevguy

9 years ago

Level 11

The additional insert shouldn't be a problem at all.

As well, you can just create a job to record the changes and then call the job from the event if you want to go down that route. Just remember that it might not be that beneficial at this point as (if you're using DB for your jobs) you're going to be performing an insert anyway. That being said you shouldn't notice any performance issues and your DB should be able to easily handle this -- if it were to struggle you would probably notice it in other areas of your application well before.

If you're going to create generic activities tracker https://laracasts.com/lessons/build-an-activity-feed-in-laravel might be a good place to start (if you haven't already seen it). There are few places you might want to expand on it but it's definitely a good starting point.

depsimon

9 years ago

Level 16

Thanks. As you say I think it won't be a problem for now.

I've already seen it and I'm using a cool package that makes it easy to play with activities : spatie/laravel-activitylog

Please or to participate in this conversation.