ocon - 5:16 am on Aug 6, 2011 (gmt 0)

Thank you, I didn't have plans to use the categories as part of the url path for the listings, but I can see how much of a mess that would be.

How it stands now for tables is this:

listings (existing)
listing id, name, address, phone number, etc...

category_names (create)
category id, parent id, name

categories (create)
listing id, category id

If I want to list the "Widget Oriental" business in the Fujian category, I would insert into the categories table the matching listing id (for Widget Oriental) along with the correct category id (for Fujian).

But in order to output a list of something like all the Asian restaurants it would either require some intensive recursive lookups, or an entry in the categories table at every category level that listing is in; one row associating it with the restaurant id, one row associating it with the Asian id, one row associating it with the Chinese id, along with the row associating it with the Fujian id.

I think I'm leaning towards the later idea, one row for every single level category the business is in, but it seems like it would be hard to maintain, and very bulky.

Am I on the right track?