Relationship Quandry

[michiko]

No - I havn't mistaken this for womencentral ..

Here's my deal. I have to create a database with multiple levels of relationships that resemble a tree structure.
Level1 - tree1 -

Branch1 Branch2 Branch3

Branch1a Branch1b Branch1c


The problem is that branch 1 and 2 of tree1 could get molded into another tree at some point in time. All the subbranch information, branch1a etc, would have to maintain that it was at one point attached to tree1 but now show that its in tree2. Any idea of how to do this,

I'm thinking primarly from a primary key perspective?

Thanks,
Michiko

 

[mymou]

Hi michiko

Not sure if im getting the picture right - but do you really need separate tables for these 'layers'. This appears to look like an old hierarchical db.

Can't you just use two fields to categories the items in a separate table (along with a date)? This means that all the information can be stored in the same table - with a foreign key to a 'reference' table.

The table would look something like.
[BranchID],[BranchNo], [BranchAB], [DateFrom].

This sould give a very clear branch history.

Im sure that something like this should work.


Stew


"Even a stopped clock tells the right time twice a day."