I’ve currently got three tables to track changes on specific entries but it seems like I am ending up with a ton of entries and I am not sure if that is the best possible way.
My first table holds the basic information and the second and third one the extra entries I grab every 8 hours.
ID | creation_date | removal_date | article_url | status which are basically the most stable entries. Status and removal_date are the only ones that will change in case we disable/remove an entry.
The second table holds everything else:
ID | main_id | last_update | title | description | views | rating | comments
The second table creates a new entry every 8 hours as long as something changes. Then based on the entries added here, I show average views/rating/comments changes on a daily/weekly/monthly basis.
The third table holds the tags:
ID | main_id | tag_id | date_added | date_removed
I thought instead of having a status to add an empty date_removed so in case the tags get updated/removed/etc update that part. The tags are saved in a separate table and just grab the id and store the connection between the two here.
I’d just like to know if there is a better / more proper way to store the above data.