If it were me I would set up the tables like this

tblCities
id | city_name

tblCategory
id | cat_name | parent_id 

tblListing
id | listing_name | city_id

tblListingHasCat
id | listing_id | cat_id

Listings store which city they belong to on the listing table because it is one to one.

Categories and subcategories all live in the same table and use parent_id to define the hierarchy. Anything with a parent_id of 0 is a top level category.

tblCategory
1 | Root Cat | 0
2 | Sub Cat | 1
3 | Sub Cat 2 | 1
4 | Another Root | 0
5 | Another Sub | 4

Then because many listings can have many categories (many to many) you store those relations in tblListingHasCat, you can decide if you want to do inherited categories or not... eg if a listing is associated to Sub Cat 2 (id:3) does that mean they automatically are associated to the parent Root Cat (id:1) if yes then you need only store the relationship to Sub Cat 2 and it will inherit Root cat... or you can code it so you have to explicitly associate each listing to each category.

Does that make sense?

Hi IanSirkit,

Thank you so much, that is very very helpful! I have a couple of questions regarding this though:

1. What do you mean by the parent id of 0 ? What does that relate to?
2. Does ListingHasCat only store the relationship between the listing/category?

Thanks again!

What about setting it up through a Pivot table, would that be part of the solution?

I got it working pretty close to what I want now, thanks to IanSir and the good Jeffrey Ways videos..

I got one issue now, and that is regarding the subcategories. I don't understand this part:

IanSirkit said:

Then because many listings can have many categories (many to many) you store those relations in tblListingHasCat, you can decide if you want to do inherited categories or not... eg if a listing is associated to Sub Cat 2 (id:3) does that mean they automatically are associated to the parent Root Cat (id:1) if yes then you need only store the relationship to Sub Cat 2 and it will inherit Root cat... or you can code it so you have to explicitly associate each listing to each category.

To me it seems like a better solution to have a dedicated table for my subcategories, as im now running into the problem of all my listings belonging to different subcategories in the table, but not belonging to a category?

Like I said above, it's now working pretty decently.

I went with a setup like the following:

categories
 - id
 - name

subcategories
- id 
- name
- category_id

cities
- id
- name

listings
- id
- name
- city_id
- subcategory_id

This sort of works, but im running into some issues with the routing and showing my view with the right data according to whats being input into the url string.

Ideally it should work like this:

www.appname/listings/city_id/main_category_name/subcategory_name

But so far I have only managed to make it work so the query is working on the city_id and the subcategory_id (not name).

My function:

public function showBySubCat($city_id, $category_name, $subcategory_id) {

 		$city = City::find($city_id);

 		$subcategories = Subcategory::where('category_id', '=', $subcategory_id)->get();

 		$listings = Listing::where('city_id', '=', $city_id)
 			->where('subcategory_id', '=', $subcategory_id)
 		->get();

		return View::make('cities.cities-view', compact('categories', 'listings', 'subcategories', 'city'));
}

And my route:

Route::get('/annoncer/{city_id}/{category_name}/{subcategory_name}', [
  'as'   => 'searchbycat',
  'uses' => 'ListingsController@showBySubCat']
  );

Annoncer means listings in danish btw :)

Any insights here?

How do i write a query that takes all 3 parameters into account?