0

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

Improve this question
3
  • Why do you want to store aggregated values if you can calculate them on the fly from the city stats alone?
    – mustaccio
    Dec 30, 2020 at 17:28
  • Primarily for visualisation purposes, i agree with your sentiment though, it's not necessary
    – studentofscience
    Dec 30, 2020 at 17:52
  • This is not the way to build a database. Lookup "Database Normal Forms"
    – Charlieface
    Dec 30, 2020 at 23:04

1 Answer 1

Reset to default
0

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

Improve this answer

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge that you have read and understand our privacy policy and code of conduct.

Not the answer you're looking for? Browse other questions tagged or ask your own question.