I have a dance school listing section on my site and have the following tables:
countries table::::::::::::::::::::
country_id
country_name
regions table::::::::::::::::::::::
region_id
country_id
region_name
schools table::::::::::::::::::::::
school_id
school_name
..
..
..
region_id
school_id
Im changing my site and want to allow people to add their schools to more than one region. I was going to use something like this:
SELECT this, that, the, other FROM schools WHERE region_name IN (".$_GET['regions'].")
but people on here are saying that using a comma seperated option was bad database design and that you should use a seperate table.
So would it be a better idea to have a seperate table that contains the school id and the region it belongs in:
school_something:
school_id
region_id
with school name "The Dance Place" (school_id = 1) in london (id=1) and hertfordshire (id=2) like:
school_id | region_id
1 | 1
1 | 2
and should i compact the countries and regions to one table with a parent id like:
id | parent id | name
1 | 0 | England
2 | 1 | London
3 | 0 | United States
4 | 3 | Florida
This could allow me to really catogorize the countries, states/counties and even make it deeper and add cities!?
What do you think?
Thanks for your help in advance!
Danny
