Re: Relations Question

I believe that you will need five relations, three 'referntial' ones and two 'transactional'

Call them

State
Teritorial unit type
Teritorial Unit Type Hierarchy
Teritorial Unit
Teritorial Unit Hierarchy

State ID		State
1		Alaska

Teritorial unit type	Will have records :
Id	Type		State Owner ID
1	State		1
2	District		1
3	County		1
4	City 		1
5	Division		1
6	Region		1

Teritorial Unit Type Hierarchy	Will have records :
Parent Type ID	Child Type ID	State Owner ID
1		2		1
2		3		1
2		4		1
3		4		1
4		5		1
5		6		1
6		NULL		1

Then you add your data

Teritorial Unit

ID		Name		Type ID
18990001	Whatever	1

Teritorial Unit Hierachy
Parent Unit ID	Child Unit ID
18990001	18990991

Hope this makes sense. This model will allow you that different states have different type hierarchy.

Type hierarchy will limit the teritorial units i.e. you would not be able to insert state as being direct parent of a county unless your type hierarchy allows it.

HTH Kind regards

Kresimir Fabijanic

On Thu, 06 Jul 2000 15:03:58 -0400, Chris Gidman <christopher.j.gidman_at_no.spam.pnu.com> wrote:

>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
Received on Sat Aug 05 2000 - 00:00:00 CEST