Relationship design between four tables 1

[wvandenberg]

I am trying to figure out what the best design would be for relationships between four of my tables. I am designing a database that will hold information about water quality samples collected from various locations or sites. The four tables in question are tblSites, tblBasins, tblSubBasins and tblSubSubBasins. Right now, I’ve designed it so that the basin tables are “lookup” tables for three corresponding fields in tblSites (they’re each on the one side of a one-to-many with tblSites). I designed it like this because all sites will belong to a basin but may or may not belong to a SubBasin or SubSubBasin. If a site can be assigned a SubBasin, then it will also have a Basin and if a site has a SubSubBasin then it will also have a SubBasin and a Basin.

Ultimately, I would like to be able to “drill down” in a form (that I have not yet designed) to find sites in a particular Basin, SubBasin or SubSubBasin.

If anyone could help me with a relationship design that would suit my requirements, I would greatly appreciate any advice.

Thanks,
Wendy

 

[wvandenberg]

OK, I added the field "Type" to my queries and assigned the values "WS" and "WC" to all the records in the appropriate query. This modification results in a union query that now looks like this:

[tt]
NodeID  Node                 ParentID  Type  Image
1       Athabasca Basin                WS    1
1       Athabasca River      1         WC    3
2       Battle Basin                   WS    1
2       Clearwater River     20        WC    3
3       Beaver Basin                   WS    1
3       Hangingstone River   20        WC    3
4       Bow Basin                      WS    1
4       Manatokan Creek      19        WC    3
19      Manatokan Watershed  3         WS    2
20      Clearwater Watershed 1         WS    2

[/tt]

I also changed strKey to include the new information:
strKey = rst(strNodeType) & rst(strIDField)

I have been working for way too long and just can't get the treeview to load. Arrgh! I've modified the code in so many spots trying to overcome the errors but then a new error comes up.

Are there any glaring errors in my posted code based on the table structure? Can someone do some quick sand dirty recursive code based on my union query?

Sorry to be such a pest.

Thanks,
Wendy

 

[wvandenberg]

MajP,

Yahooooo!! I got the tree loading properly! Thanks! I will be adding "bells and whistles" you mentioned. Should I start a new thread?

Wendy

 

[MajP]

3 Beaver Basin WS 1
3 Hangingstone River 20 WC 3
19 Manatokan Watershed 3 WS 2

Who is the parent of record 19? Is it Beaver Basin or Hangingstone river? What are the rules? Once I understand how to determine which one is the parent, I can fix the code. I think you might need an additional field such as parent Type.

To simplify this a lot, put a zero in for the Parent IDs for all the records at the highest level (this assumes that you do not have a NodeID equal to 0). With the data fixed the highest level has a ParentID of 0 and a Type of WS. Having a null ParentID really complicates matters.

The following populates most records, but it is not correct because I do not understand how to determine if record 19 belongs to BB or HR.

Private Sub Form_Load()
  Dim objTree As TreeView
  Dim rsSites As Recordset
  Set objTree = Me!xTree.Object
  Set rsSites = CurrentDb.OpenRecordset("qrySites", dbOpenDynaset)
  Call subAddBranch(rsSites, "WS0")
End Sub

Private Sub subAddBranch(myRS As Recordset, theCurrentID As String)
  Dim strCriteria As String
  Dim bk As String
  Dim currentSiteNumber As Integer
  Dim strType As String
  Dim currentHigherSite As String
  Dim currentSiteDescription As String
  Dim nodeCurrent As Node
  Dim nodeParent As Node
  Dim objTree As TreeView
  Dim strKey As String
  
  Set objTree = Me!xTree.Object
  currentSiteNumber = Mid(theCurrentID, 3)
  strType = Left(theCurrentID, 2)
  'This is where I need to understand your relationships
  strCriteria = "ParentID = " & currentSiteNumber & " AND Type = '" & strType & "'"
  If Not currentSiteNumber = "0" Then
     Set nodeParent = objTree.Nodes(strType & currentSiteNumber)
   End If
   myRS.FindFirst (strCriteria)
   Debug.Print strCriteria
  Do Until myRS.NoMatch
   currentSiteNumber = myRS!NodeID
   currentSiteDescription = myRS!Node
   currentHigherSite = myRS!ParentID
   strKey = myRS!Type & currentSiteNumber
  If currentHigherSite = "0" Then
     Set nodeCurrent = objTree.Nodes.Add(, , strKey, currentSiteDescription)
    Else
     Set nodeCurrent = objTree.Nodes.Add(nodeParent, tvwChild, strKey, currentSiteDescription)
  End If
    bk = myRS.Bookmark
    'This is the other place I need to understand your    
    'relationships
    Call subAddBranch(myRS, myRS!Type & currentSiteNumber)
    myRS.Bookmark = bk
    myRS.FindNext (strCriteria)
  Loop
End Sub

 

[wvandenberg]

lespaul, regarding your comment:

For the Site table, will every site have a GAL_Site_Name AND an AENV_Site_Name AND a RAMP_Site_Name or does each site only have ONE site name?

I just wanted clarification on how to deal with this table. Not every site will have all three site names entered. A site will ALWAYS be required to have a GAL_Site_Name. If it happens that same location is used by another agency, then AENV_Site_Name and/or RAMP_Site_Name would be filled in too. I know this design is not "normalized" but how else could I set it up?