<style type="text/css">
<!--
@page { size: 8.5in 11in; margin: 0.79in }
P { margin-bottom: 0.08in }
-->
</style>
I need to create a data model for an application that I am writing, but I am extremely unskilled in this area, and could use a little guidance. I have searched google for answers previously, but I don't know enough to make an effective search.
The problem is that I have a set of items each of which
has multiple dependencies. The problem is the system needs an
expandable number of both items and dependencies. I tried to
follow the normalization guides producing three tables:
Item:
Dependency:
DependencyGroup
Field
Type
Field Type
Field
Type
ItemID
int
DepID int
GrpDepID int
GrpDepID
int
DepID int
So dependancies are broken into groups, each item has a group.
Now the problem occurs because given a list of dependencies, I want to return all items which have a dependency group that contain a subset of the given dependencies. So if I have a dependency set [1,2,3] then an item with a group [1,2] would match, but and item with a group [1,4] would not.
It seems that I want to run a query that uses information in the rows to generate my results. I could return all the results that contain any of the dependencies and then using my software to reduce the result set to what is proper.
That seemed to me to remove part of the usefulness of the database, so I made a query. Its complex, so I do not think it is the most efficient solution.
SELECT ItemID FROM Item WHERE GrpDepID NOT IN (SELECT DISTINCT GrpDepID FROM DependancyGroup WHERE DepID NOT IN (1,2,3))
I appreciate advice. Is this the best way to
organize the data? Is there a better way to store or retrieve
it with respect to this query?
Thank you for your help.