[thelist] Nested Queries

[David.Cantrell at Gunter.AF.mil]

>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