Table size vs amount
Hello,
I do not have experience in DB design and it would be one terrible time/usage trade for me, to learn MySQL design considerations from zero for my first DB project since university and probably the last one in my life.
So I ask you just to propose optimal MySQL DB design to fill it from a web page for unlimited time (in best case my entire life).
Don’t think about use of the data, actual data will be different, but the structure is exact the same.
There will be a regular web forum from simple template with user registration. Each user register up to 10 weather stations and adds every day the same data, like temperature, pressure and so on (may be in the future we will require other values, like radiation) and we have different weather station types, eg one show Celsius and another Fahrenheit, I will define available values for each model.
Each user earns points by adding today’s values to his weather stations. How many points the user will get, depends on the values and model. For example a temperature from simple thermometer cost 5 points and temperature from weather balloon 10000 high cost 50 points.
At the end, I will run simple analytics about earned points, for example top 5 users with the most points this month. Another example could be a cake diagram for this week, if all earned points are 100% and how much points in percentage added each user.
My idea was to define a record for each weather station model, then create a main table for all users with unique number (id), his current points for today and registered records. Just after data submit, I will calculate current points and on midnight save this value in a separate table, with all users and collected points on that date.
Like I said, it is just an example to explain the task, collected weather data is only required to calculate user points and won’t be saved.
Is it an appropriate design for long lasting application? By this design I would create for the next year new table and summarize collected point per user for each week, each month and each year in 3rd table for long time statistics.
Or may be it would be better to create a separate table for each weather station model and then fill it every day with user id and earned points by this model? Or should each user get his own table? Or should I create a record with all possible data and just fill one table with date, user and record? Or may be one table with a separate column for auch possible value (or should I create such a table for every day and previous one save in Archiv?)
There are a lot of possibilities, what is your suggestion for my use case?
Thanks