Notice that a submarket may be a part of more than one market.
The question is this:
Should I use a seperate table to keep the relations so that I can preserve the many-to-many relational nature or should I force a many-to-one (market-to-submarket) relation so that if two markets both have "Residential" submarkets, they are treated as seperate and distinct submarkets (i.e. Water-->Residential and Wastewater-->Residential where the two instances of Residential are different and distinct
In the second case, I would add a "parent ID" field to the market table and in the first case, I would use a seperate table altogether which had two columns <parent market ID>,<child market ID> (I'm only using descriptive names here).
Ultimately, the data will be used to construct an OLAP cube...I'm not sure if this affects which design pattern to use.
Suggestions, comments, and/or corrections would be much appreciated. Thanks!