How do I store World/event data in a treelike structure SQL/SQLite3 I would like to create a database which contains tables of the following form, where each record in a table, is a table in and of itself. Ideally at any one time I would be able to query any table of the following format. e.g I would be able to lookup/export Paris table and see names of persons and other data contained in that table.
I've currently not found a solution for this use case and any help would be much appreciated
Continent | Occurrences in continent|
Europe. ....
...
Country | Occurrences in country
France ...
...
City | Occurrences in city
Paris. ...
...
Name of persons in City | number of occurrences | City | Country | Continent | Unrelated data
...
I'm not too familiar with building databases and I think one issue I have is that currently, the only unique identifier would be names of persons for each city. Naturally, the number of occurrences in each continent would be the sum of the occurrences for each country in that respective continent and so on. I would like the db to work such that an increase in number of occurrences in the lowest table would be reflected in all tables.
So far I've created the relevant tables at each node but I don't know how to relate them to one another
I have seen a reporting database that had join tables that assigned a city to an administrative district (county), the administrative districts are assigned to another, bigger district (state), which is assigned to a country. The nesting action is done through the queries, instead of trying to have Russian Nesting Dolls of tables inside of tables.
Example:
CityID | CityName | DistrictID
1        New York   5
DistrictID | DistrictName  |  Province\State
5            New York County  7
ProvinceID   |ProvinceName|  CountryID
7             New York        127
CountryID | CountryName
127            USA