Another question about database modelling


thanks for reading! :slight_smile: Ich will describe my problem with the help of an example, I think, that's the quickiest way to present my thoughts.

Let's assume, you want to save locations. Locations can be of different types: country, federal state, town or even a shop or a phone cell. These ways of modelling this came to my mind:

1. Create a table for each type of location, identify the common properties and extract these to seperate tables, so that you could end up in something like this:

Tables: 'countries', 'federal_states', 'towns' and as an example for a table holding common properties 'demographic_information' which will be joined with each of the mentioned tables.

2. Identify location categories, whose members have the same properties and add a kind of type column referring to a location type table, so:

Tables: 'cat1_locations' with a foreign key to a 'cat1_location_types' table.

3. I could imagine to modell this polymorphic, but I've got no clear idea right now, maybe you have heard more about this and are willing to tell me more about this, I would really appreciate this.

Let's come to my question: Which way would you modell this? What other possibilities are there to modell this, maybe a kind of modelling pattern? Can you name me criterias on which my decision should be based?

Thank you really much!

Best wishes, ms

Hi, IMHO the best way is Nested Set, you can find a lot of tutorials online. This approach can represent different administrative organization in the same time.