If this is your first visit, be sure to
check out the FAQ by clicking the
link above. You may have to register
before you can post: click the register link above to proceed. To start viewing messages,
select the forum that you want to visit from the selection below.

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!