Categories
Database Development

A noob question about DB structure and storing changing data (like page views and updates) over time

I want to make a project for a friend… I am new to creating databases’ structures and learning on my own (any resources welcome). I have little experience with WordPress databases and I know how they store articles and stuff, but this is something new I have here…

For simplification purposes I will compare my goals to products in an e-shop. I have data that can be summarized like this:

  • ID of the product that never changes

  • People create descriptions in my database (imagine like products on eBay) that they can edit, I guess most will just add it and never touch again. However, if they change it, I would like to save the edits too… something like history in the edit menus of today’s apps, so I can go back in case it is needed. Bear in mind, they can change different parts of the ad on different times: E.g. they add something to the description today, reduce the price tomorrow and change the headline in a week. I would like to save all of the changes and the days it happened, so I can go back (in my case this is a history of what didn’t work).

Then I have additional data I would like to monitor:

  • I want to save in the database views of the products over time – I am thinking about updating it once a day. So I can create a table of the development for the CFO.

  • Further I have information about used ad budget (whole number, usually in range 0-20 – can only go up, maybe in the future I will need to save the date it increased). This is an additional number to the product price.

The product stays on the page for a maximum amount of 30 days (so max 30 updates to the page views daily number). One of the goals is also for me to be informed when someone deletes the product prematurely (the ID disappears from the e-shop), so I can contact them and make sure it was intentional. I am thinking about storing “last present on the page” to the database (this would just update to today’s date, if it is present at today’s update). After checking all IDs, I can run a program and let it spit out all of the IDs that were removed before the 30th day, so I can make the calls.

The current plan is to start the update of the data manually / when required.

How would you structure this database? What is the basic idea behind storing the page views?

What else should I bear in mind?

Thanks to all in advance!

Leave a Reply

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