Re: Relations Question

If you don't care what data is stored about each level of resolution, or if the data is of the same type, you can do this with two tables.

One table stores the list of possible resolutions. i.e. State, Region, etc.

The other stores a reference to the lookup table indicating what resolution the record is at, and a reference to its parent (in the same table).

Lookup Table ->
Resolution Name ID
--------------- ---------

State		 1
District	 2
County		 3
City		 4
Division         5
Region           6

Node Table ->
Name         Population  Parent ID   Resolution ID  ID
------------ ----------  ----------  -----------    ---------
Texas	     1,000	 0	     1		    1
Longhorn     500         1           2              2
Alamo        500         1           2              3
Dry Valley   250	 3           3              4
Longview     100         4           4              5

Get the idea?

Of course, this leads to recursive queries to get the full tree structure, but hey, you can't have everything. You could even do this with one table if you don't want the lookup table. Someone else will have to address performance. Sorry.

To get all the cities in Texas:

SELECT Node.Name
FROM Node,Lookup
WHERE
Node.ResolutionID = Lookup.ID
AND Lookup.ResolutionName = 'City'
AND Node.ParentID In
  (SELECT Node.ID FROM Node
   WHERE Node.Name = 'Texas')

That's the way I see it.

Chris.

Roger Webb wrote:

> My problem is in having an unknown number of Master-Detail type
> relationships between one master record and a detail record. Heres my
> example.
>

<snip example>

> Other states are even more different..
> I think one was State, Region, Division, District, County, City....
>
> What kinda structure would yall use to make quering things like The Cities
> in a State... or other such items perform well?
>
> Thanks,
>
> - Roger
 

-- 
+---------------------------=*=---------------------------+
| Views expressed by Mr. Gidman are not necessarily those |
* of Pharmacia.  To reply to Mr. Gidman directly,  remove *
| the  first  five  characters  of  the  return   domain. |
+---------------------------=*=---------------------------+