[thelist] Nested Queries
David.Cantrell at Gunter.AF.mil
David.Cantrell at Gunter.AF.mil
Wed Aug 21 09:48:00 CDT 2002
>I have an email application that allows people to send emails to people by
>selecting the group that they want. Each group can be made up of other
>groups.
What you are looking for is a way to associate 0..n e-mail addresses with a
specified group, and also associate 0..n groups with a specified group,
right?
This is a recursive parent-child structure. Remember there are only three
numbers a programmer cares about: Zero, One, and Infinity. ;)
If this is correct I would look at a multi-table structure like this:
tEmails
-------
email (PK)
... whatever else you want to maintain on the email address ...
tGroup
------
group_id (PK)
group_name (or whatever)
... whever else you want on a group ...
tGroupEmails
------------
group_id (FK)
email (FK)
child_group_id (FK)
Not that tGroupEmails doesn't have a primary key -- it is a joining table
because the entity diagram I just drew up shows a many-to-many relationship
that tGroup has with itself. Since relational DBs can't do m-m relationships
you have to work around it.
Then your data would look like this:
tEmails: email | ...
-----------------------------------------
foo at foo.com | ...
bar at bar.com | ...
fnord at fnord.com | ...
test at test.com | ...
fake at fake.com | ...
prez at biz.com | ...
... | ...
tGroup: group_id | group_name
------------------------------------------------
1 | designers
2 | developers
3 | testers
4 | managers
tGroupEmails: group_id | email | child_group_id
----------------------------------------------------------------
1 | foo at foo.com | Null
1 | bar at bar.com | Null
1 | fake at fake.com | Null
2 | fnord at fnord.com |
Null
2 | foo at foo.com | Null
3 | Null | 1
3 | Null | 2
3 | prez at biz.com | Null
4 | prez at biz.com | Null
Where group_id 1 has three e-mail addresses and no child groups, group_id 2
has two email addresses and no child groups, group_id 3 has two child groups
as well as listing the president of the company, and group_id 4 has only one
member, the company president.
HTH,
-dave
More information about the thelist
mailing list