Another question about database modelling

Hello,

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.

Danilo