c# - SQL Structure Stores, Countries, Districts and Other Important Criteria -

i sorry if bit broad i've been working on structure couple of days , can't seem figure out cleanest , efficient way this. share tables i've created think it's not close how proper diagram should be.

let me describe problem bit: have stores, countries, districts, categories. each store belong different countries/districts enabling store branch manipulation. of course store belong multiple categories too, example store x under both food , beverages , night clubs. country have multiple districts , stores, , store have many countries , districts.

i writing application using c# , don't have problems creating data-layer objects , classes. need proper mssql structure manipulate , filter down data based on given criteria.

the important criteria be: going through countries first step, locating stores within country global view, it's important sort stores based on districts and/or categories within country.

please let me know if need me share have now, since i'm on stack overflow asking question can guess i'm doing wrong way.

anyway, if shed light on issue , explain how things should done highly appreciate it.

thanks in advance.

whenever have many-to-many relationships (e.g. 1 district may contain many stores; 1 store may contained many districts), going need use cross-reference tables between entities.

i'm assuming particular district may contained 1 single country. here how model out schenario:

countries(country_id [pk], name, ...) districts(district_id [pk], country_id [fk], name, ...) districts_has_stores(district_id [pk], store_id [pk]) stores(store_id [pk], name, ...) categories_has_stores(category_id [pk], store_id [pk]) categories(category_id [pk], name, ...) 

in er:

districts_has_stores , categories_has_stores cross-reference tables representing many-to-many relationships between entities.

based off of model, can retrieve stores within particular country, , order stores district name using following sql:

select c.* districts inner join districts_has_stores b on a.district_id = b.district_id inner join stores c on b.store_id = c.store_id a.country_id = <country_id here> order a.name 

retrieving count of stores in each country:

select a.country_id, count(*) store_count districts inner join districts_has_stores b on a.district_id = b.district_id group a.country_id 

edit: per comment answer, here's example of how can retrieve stores have category_id of 1:

select b.* categories_has_stores inner join stores b on a.store_id = b.store_id a.category_id = 1 

retrieving stores in particular category_id (1) , filtering result include stores within either districts 4 or 5.

select distinct b.* categories_has_stores inner join stores b on a.store_id = b.store_id inner join districts_has_stores c on b.store_id = c.store_id a.store_id = 1 , c.district_id in (4,5)