What I'm trying to do is create a database that stores geographic locations. It could be a restaurant, shop, city, zipcode, state, province or country. For each geographic location, I want to be able to provide some type of description and even rating or review. I would like to model this in a database, but I don't want to have to explicitly state exactly how many layers of relationships there are. For example, in the US we have states, but in other countries there aren't any. So I don't want to make a country table that links to a state table that links to a city table because if the country has no states, then how am I supposed to get to the city table.

What I was thinking was creating one table where each geographic location will have a foreign key relationship with another record in the same table which would be the parent for that location (for example, US would be the parent for Colorado). So by doing this, you can keep adding relationships and drilling down further and further without limitations imposed by the database table structure. It seems like this isn't a very good idea though, but I don't really know why. Does anyone have any suggestions about tackling a problem like this?

I think this is better suited to Database Concepts and Design. Most posters there frequent here too, but there are some that don't use SQL Server. Let me know if you are happy for a move.

pootle flump ur codings are working excelent.

ah, but it is

see Categories and Subcategories for sample queries

Oh, sorry. I usually just post here because most of my issues are SQL Server related, so I didn't even think about checking for a more relevant discussion area. I'd definitely be happy with a move. Thanks.