[thelist] recursive database design discussion

Jacques Capesius jacques_capesius at cnt.com
Wed Oct 9 13:33:01 CDT 2002


Hi folks,

I'm designing the database architecture for a web content management system,
and one the puzzles I'm trying to figure out is mapping the organizational
hierarchy to a database system. There are two ways I was thinking about
doing this. Here's the first, and original way I was planning on going about
this. We'd have four tables:

1: T_ORGANIZATION (t_organizationID (pkey), (organization_name)

2: T_DEPARTMENT (t_departmentID (pkey), t_organizationID (fkey),
department_name)

3: T_GROUP (t_groupID (pkey), t_departmentID (fkey), t_organizationID
(fkey), group_name)

4: T_USER (t_userID (pkey), t_groupID (fkey), t_departmentID (fkey),
t_organizationID (fkey), user_name, password)

This model allows for a variety of relationships, like:
a) an organization can have many departments can have many groups can have
many users
b) an organization can have many departments can have many users.
c) an organization can have many users.

However, in discussing this with a colleague, we got to thinking that with
the T_GROUP entity, the T_DEPARTMENT and T_ORGANIZATION entities were kinda
redundant. We could just expand the T_GROUP entity and make a foreign key
that references itself, thus having parent/child relationships among the
groups themselves. The data model thus becomes:

1: T_GROUP (t_groupID (pkey), parent_groupID (fkey), group_name)

2: T_USER (t_userID (pkey), t_groupID (fkey), user_name, password)

Where the parent_groupID would point to the group that is higher in the
organizational hierarchy than the group in question. This eliminates two
tables, and simplifies the T_GROUP and T_USER tables immensely, which I
figure is better database design.

There's a drawback that I see though, and I'm writing to get you database
gurus' thoughts on this. Programming webpages to layout content and
navigation based on this model will require a lot more logic, most of it
recursive, to figure out the organizational hierarchy and determine, among
other things, who has access to what page.

My question is, what are your thoughts on these comments, and which model,
in your opinions, is the more sensible one.

thanks, fellas :)

-jacques :)



More information about the thelist mailing list