2

I'm wondering about the idiomatic, or at least a workable, way to make a table that represents subsets of rows of another table. This is motivated by efficiently storing and looking up these subsets.

For example, a medical setting: you have a Patients table, and an Allergies table. There's a many-to-many relationship between patients and allergies -- so the usual approach for a relational database schema would be to make a bridge table, of two columns: a patient key and an allergy key.

Here's an ER diagram, where the bridge links to patients and allergies:

But that solution is space-inefficient, and doesn't support looking up the set of patients with a given collection of allergies, because every patient allergic to A, B, and C gets their own three rows in the bridge table.

I want a table that represents subsets of the allergies. So, for example, we'd have one representation of "allergic to A, B, and C" in the bridge/subset table:

AllergySubset
SubsetKey | AllergyId
foo       | A
foo       | B
foo       | C
etc etc   | other ids

Then, every patient allergic to the combination A, B, and C would link to "foo". The schema reverses the link between patients and the bridge/subset table:

My question is: how to do lookups for subsets?

Given a patient allergic to B, C, and E, if there's already an AllergySubsetKey in the subset table, how can I determine that?

Or, equivalently: given allergies B, C, and E, I can find all patients allergic to that combination if I know the subset key. How do I find that key?

What's the idiomatic way to design this schema?

2
  • Does the subset always have exactly three Allergy references? Mar 5, 2023 at 15:10
  • Nope. It could have one or more Allergy references.
    – Dan Drake
    Mar 5, 2023 at 16:53

1 Answer 1

3

You can use relational division to do such lookups.

For example, to find a subset key for a particular list of items, begin by placing them into a table variable or temp table:

DECLARE @toFind TABLE (AllergyId varchar(20) PRIMARY KEY)
INSERT @toFind VALUES ('B'), ('C'), ('D');

SELECT
  s.AllergySubsetKey
FROM PatientAllergySubset s
LEFT JOIN @toFind f ON f.Allergy = s.AllergyId
GROUP BY
  s.AllergySubsetKey
HAVING COUNT(*) >= (SELECT COUNT(*) FROM @toFind)
    -- all toFind values are matched
   AND COUNT(*) = COUNT(f.Allergy);
    -- all subset values are matched

To find subset keys which contain all those allergies and any other allergies, simply remove the final condition of the HAVING.

2
  • Thanks! I'm not experienced with SQL or schema design,but that's exactly what I wanted: it uses joins, HAVING, GROUP BY, and other standard operations to do the lookup, and it's clear how to modify that for slightly different flavors of searches. Thanks!
    – Dan Drake
    Mar 6, 2023 at 13:01
  • Note by the way that this query could theoretically return multiple results if there are multiple subsets that are exactly the same (or in the case you remove the final filter then if one is a superset of another) Mar 6, 2023 at 13:25

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

Not the answer you're looking for? Browse other questions tagged or ask your own question.