Hi,

I need to store a hierarchy in my database (a tree)
I have 3 types of data: Cases, Groups and Users. I can have any
combination of them.

Check the following link ('Groups', 'Cases', 'Users' and 'Tree' will be
tables in my database):
http://www.lemforever.com/temp/tree.jpg

What do you guys think? Do you think is a good solution? I still want to
have a relational database
and I don't want to violate any integrity rules.

Thanks a lot.

I just realized that I could have used my previous post for asking that.
Sorry about that.

If you have any feedback please use either one.

Thanks

i have send a zip on lemfore***@lemforever.com . go through it if  u
like the same then u have easy solution to ur this problem

On Thu, 05 Jan 2006 14:38:04 -0500, Star wrote:

Show quoteHide quoteHi Star,

Depends on how this data has to be used.
If the example shown in the picture is representative of all possible
relationships, an alternative design would be:

DECLARE TABLE Groups
          (GroupID int NOT NULL,
           ParentGroup int DEFAULT NULL,
        -- other columns,
           PRIMARY KEY (GroupID),
           FOREIGN KEY (ParentGroup) REFERENCES Groups(GroupID)
          )

DECLARE TABLE Users
          (UserID int NOT NULL,
           ParentGroup int NOT NULL,
        -- other columns,
           PRIMARY KEY (UserID),
           FOREIGN KEY (ParentGroup) REFERENCES Groups(GroupID)
          )

DECLARE TABLE Cases
          (CaseID int NOT NULL,
           ParentGroup int DEFAULT NULL,
           ParentUser int DEFAULT NULL,
        -- other columns,
           PRIMARY KEY (CaseID),
           FOREIGN KEY (ParentGroup) REFERENCES Groups(GroupID),
           FOREIGN KEY (ParentUser) REFERENCES Users(UserID),
           CHECK ((ParentGroup IS NULL AND ParentUser IS NOT NULL)
               OR (ParentGroup IS NOT NULL AND ParentUser IS NULL))
          )

Joing two tables but avoid cartesian product
Dynamic SQL Column Total Help
Datetime constraint: is there a better way to do this?
Add a field
simple insert of strings