Creating a category system in a MySQL database.

[ocon]

I have a database full of business listings; different hotels, bars, and restaurants, each with an "id" and contact information. I want to be able to organize these listings into categories, but I don't know how to go about structuring my database tables.

I do know that I don't want to modify my existing table that stores the business listings, instead I want to create a new table or tables to store these categories, linking them with the business through their "id".

On my homepage I will show all the listings. But I want user's to be able to click a link in a list of top tier categories, such as "Restaurants", to be taken to a page where they will then see only the listings in that category; restaurants listings.

On this new restaurants page I want to show a list of subcategories, such as "Asian", "American", "Fast Food", and "Diner". I want to repeat this so user's can click on "Asian" and only see the Asian restaurants with Asian subcategories, such as "Japanese", "Chinese", and "Vietnamese". If the user clicks on Chinese, they'll have further subcategories for "Anhui", "Cantonese, and "Fujian". There could be many levels in a category, whereas others may only have a few.

Some businesses will be associated with multiple categories, such as a restaurant that also has a bar.

How I see it, I need to create a table of different categories with a name, its category id (separate from the business id), and the category id of its parent? Maybe then create another table that associates a listing with the category id of the deepest level in a category, with multiple rows for the business if it has different category branches?

Am I on the right track? I guess I don't know how I would do something like a query to show all the restaurants?

[caribguy]

Straight out of the mySQL Manual, see the example.

[dev.mysql.com...]

[ocon]

Thank you, I got how you can use an INNER JOIN command to link the listings to a category, but I don't know how to create a multi-category depth.

Unless, are you advocating to create table with a column for "level 1", "level 2", "level 3", etc...? Like how the example uses has a column for style and color. In that example I could easily join the tables and show all the polos. I could also easily show all the blue polos, and create a new column for each deeper level in the category. This is how I would normally do it, but it seems very wasteful, redundant, and limited in that I could only have a maximum number of subcategories based on the number of columns in the table.

[g1smd]

Do make sure that category names appear in the page URL only when listing multiple items within a category. On the click to the detailed listing for a single outlet, make sure the requested URL does not contain category information. You can therefore avoid duplicate content issues for all businesses that are listed in multiple cateogies.

[ocon]

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?