I'm having a hard time figuring out how to model a database with a Many-to-Many relationship between a concept of Markets & regions of the world.

The issue I'm running into is that a Market is just a region for doing business. It could be North America, and include multiple cities, provinces, and countries, or it could be the city of Ottawa, Ontario, Canada. Meaning, a Market can contain many regions, or regions can contain many Markets.

I've seen some structures for this concept with a separation between a Global Market which would be ones that include more than one country, and a Regional Market which would be only within one country. But this is treating them as two different entities when, by nature, they're the same.

Is there a standard model for structuring data that could efficiently convey the parent in the relationship? To easily be able to say The North American Market contains these countries, which would enable deriving provinces & cities, but also say The USA contains these Markets or Texas contains these Markets.

Browse other questions tagged or ask your own question.