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?