Mastering Development

Storing changes on MySQL Database properly

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.

Leave a Reply

Your email address will not be published. Required fields are marked *