I'm pretty new when it comes to database development and the project I need to install now. I'm not sure if my question makes sense, but I have two pieces of data, for example, daily weather and another list of cities.
Daily (or more often) daily weather will be updated with new weather for each region, but also when adding new cities, the database of cities will also increase (not as fast as we collect daily weather, but it can grow as we add more cities and even countries) .. Each table has other tables to enrich it, for example, cities have other tables with statistics on the city (population, current mayor, etc.).
The question is how the data is getting bigger and bigger, is it better to have the weather in one table and cities in another table or to have one database for these two and keep everything aside. The reason I ask is due to two reasons. As the size grows, is it easier to move one database to another physical location (i.e., to another server), or can I do this using tables? In addition, someone told me that for future performance you need to index your indexes as little as possible and not too large, or it will be slower, I was thinking about splitting data between databases as a way to do this. And finally, in the future, I might want to release an API that gives people access to one and not to the other (i.e. only to weather, but not to city statistics or vice versa), or to provide api access to both. I am more concerned with the scale, as if everything is going well according to the nature of this system, the data will always grow.
Sorry for this long question. I'm a little confused about which approach to take. Finally, if I go on the wrong road ... is it easy to recover in mysql? I create one database, but later I understand that I need two or vice versa.
source share