I face a conceptual question for which I don't find an efficient solution.
Imagine below dataset that indicates at which side of the road people drive around the world. The structure of the data is a bit strange because the level of detail in the different rows is different. Nevertheless, I want to use such a dataset to get information out for every city.
My question is how to deal with this kind of datasets.
1) should I put the data for every level of detail in seperate tables ?
(this would make my queries much more complex)
2) should I convert the data to a dataset per city every time I receive it?
(this would generate a huge dataset)
3) should I use the table as shown below ?
(this also requires more complex programming for my queries)
Continent Country Province city SideOfRoad
Americas all all all -right-
Europe Belgium all all -right-
Europe United Kingdom all all -left-
Europe France Bretagne all -left-
Europe France Ile de France all -right-
Europe Spain Catalonia Barcelona -left-
What is your opinion ? Is there documentation about this kind of issues ?
Thanks already for any feedback.
If all of the rows will contain the 4 columns you've shown, though the last n values will be blank or "all", one table should suffice. If there is additional data, there might be a need for more than one.
1. possibly you could create a view (or more than 1) that would still allow a simple query.
2. if youhave the same amoune of data it will take about the same space whether in one table or multiple.
3. why would this be more complex? write multiple queries that are requirement driven rather than completely generic.