SQL Server multiple many-to-many join query

Question

I currently have a one main table with multiple other tables associated with it via many-to-many joins (with join tables). The application using this database needs to have search functionality that will print out multiple rows matching specific criteria, including all the values in the join tables. The values from the join tables also need to be links that will enable a search for all other rows that match that value. I am trying to figure out how to do this without taxing the database.

Here is an example of the table structure

**Metrics (Main Table)** 
MetricID (pk) 
Metric

**Domains (ValueList Table)** 
DomainID (pk) 
Domain

**MetricsDomains (Join Table)** 
MetricsDomainsID (pk) 
MetricID (fk) 
DomainID (fk)

**MetricTypes (ValueList Table)** 
MetricTypeID (pk) 
MetricType

**MetricsMetricTypes (Join Table)** 
MetricMetricTypesID (pk) 
MetricID (fk) 
MetricTypeID (fk)

**Studies (ValueList Table)** 
StudyID (pk) 
Study

**MetricsStudies (Join Table)** 
MetricsStudiesID (pk) 
MetricID (fk) 
StudyID (fk)

When someone searches for a Metric by various criteria, they should get output in table format that looks something like this:

Metric1 | Description | Study1, Study2, Study3 | MetricType1, MetricType2 | Domain1, Domain2
Metric2 | Description | Study5, Study2, Study4 | MetricType2, MetricType3 | Domain5, Domain9

The Metric will be a link to the full description of the Metric. However, in addition, the Studies (ie., Study 1, Study 2, Study 3, etc.) and MetricTypes (MetricType1, Metric2, etc.) and Domains (Domain1, Domain 2, etc.) should also be links, that when clicked on, will perform a new search for all other metrics that contain that study, or type, or domain. This leads me to believe I will also need the primary key of the study, type, or domain, in addition to the text, in order to place in the href.

At any rate, considering one search could possibly return 20+ metrics, what I need to figure out is a good way to write an optimized query to return the results of the multiple many-to-many joins. I know that joining all of these tables in one query will generally result in a Cartesian product of all the joins, but I am not sure if there is another way to go about it. I have also read about a way I could return the many-to-many results as a comma-separated list in a field using a method like this:

SELECT m.MetricID, Description, 
    STUFF((
    SELECT ', ' + s.Study
    FROM Studies s, Metrics_Studies ms 
    WHERE s.StudyID = ms.StudyID AND ms.MetricID = m.MetricID
    ORDER BY s.Study
    FOR XML PATH('')
    ),1,1,'') as Study, 
FROM Metrics m 
WHERE Metric_PK = 13

However, I am not sure of the performance impact of this method, or whether it will really get me what I am looking for since I think I may need the primary keys of the Studies as well.

Any help would be appreciated.

Thanks!

Answer 1

I'd recommend doing it first with your multi-joins - only then will you know whether the perofrmance is good enough. As always you have to beware of premature optimisation. Once you have your query running correctly against your normalised model, you can check the query plan, etc. This may highlight that you need to flatten a few of your joins, if that's the case you probably will have to store the data in two different formats, one for reporting / one for searching, etc. But 1st thing is to see if performance is actually acceptable out of the box. Hopefully that helps.