I develop a website where there is a notion of city group. Where a group is defined by a city parent and cities as childs. So I need to store a relation between a parent city and child cities (1:n relation).
I was wondering was is the best schema to define for that. For instance we would have : Paris (parent) --> Neuilly (child), Vincennes (child), St Mandé (child), etc...
Maybe something like:
--> cities
- id (int PK)
- name (varchar)
- parent_id (int default 0 )
Then values like:
- 1,Paris, 0
- 2, Neuilly, 1
- 3, Vincennes, 1
- 4, St Mandé, 1
To build your list:
select * from cities as c0 join cities as c1 on c1.parent_id = c0.id;
You will use self join for this
table :=> cities
fields :=>
- id (int PK)
- name (varchar)
- parent_city_id (int default 0 )
And therefore self join will be used where Join will be based on id of c1(cities Alias table1) joining c2(cities Alias table2,treating as Second table).
