I'm having trouble structuring the data in an investment database. The
basics are:

* Investors invest in Deals
* Investors can be either Individuals or Entities
* Entities may or may not contain individual Members
* Entity Members can be either Individuals or Entities
* An Investor in one Deal may be a Member of an Entity that is an
Investor in another (or even the same) Deal.
* There is no limit to the depth of this "tree"

My issue is how to structure the data to support these relationships
while enabling flexibility in the depth of display for Investors - i.e.
show just the highest-level Investor or drill down N levels in the "tree".

I'm a novice with Access, so I apologize if I'm not explaining the
problem clearly. Any advice would be greatly appreciated.

-----BEGIN PGP SIGNED MESSAGE-----
Hash: SHA1

You've described it well. It sounds like an adjacency list model or
nested set model would be the way to set up your, relevant, tables.
This is also called a Bill of Materials (BOM) model. The most famous
book on adjacency list & nested sets is Joe Celko's. See his book _Joe
Celko's SQL For Smarties: Advanced SQL Programming_. There are also
many discussions of all three models in SQL forums and this newsgroup.
Try reading the Google group archives in these newsgroups:

comp.databases.ms-access
microsoft.public.sqlserver.programming

To understand these models it would be a good idea to read any
programming books that include discussions of data structures: linked
lists, trees, especially.

--
MGFoster:::mgf00 <at> earthlink <decimal-point> net
Oakland, CA (USA)

-----BEGIN PGP SIGNATURE-----
Version: PGP for Personal Privacy 5.0
Charset: noconv

iQA/AwUBQd9MHoechKqOuFEgEQLWDQCeJKdb+wICgcL1+h0IMVszka 1m04UAniFK
FAO0QB2bV8/KCnreknuJEBpg
=mj35
-----END PGP SIGNATURE-----


Elliot wrote:

Actually Joe has a book out specifically on this:

http://www.amazon.com/exec/obidos/AS...356133-3944959


"MGFoster" <me (AT) privacy (DOT) com> wrote