I have a flat table
(id, city, county, state),
(1,'Beckingen', 'Merzig-Wadern', 'Saarland')
A city belongs to a county. A county belongs to a state.
First my attempt was to divide the data into 3 tables cities, counties, and states and to build up the associations via linking tables (cities_counties). Now I want to create a 'locations' table out of it, where county is parent of city and state is parent of county.
locations: (id, name, parent_id, type) (type is 'city','county', or 'state')
What would be best practice to create the adjacent list from the flat list?
I'm working in a LAMP environment, just in case a php script would fit.