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

 

[lespaul]

what's the difference in the data stored between a Basin and a subBasin? Or do you have two identical tables with different names?

Leslie

Anything worth doing is a lot more difficult than it's worth - Unknown Induhvidual

Essential reading for database developers:
The Fundamentals of Relational Database Design
Understanding SQL Joins

 

[wvandenberg]

A basin is a geographic area drained by a single major stream. The basin can be divided into sub basins or smaller streams that drain smaller areas and these areas can further be divided into SubSubBasins.

The table format for the Basin tables are:
tblBasins
Basin_ID Basin_Name
01 Maritime Provinces Drainage Area
02 St. Lawrence Drainage Area
03 Northern Québec and Labrador Drainage Area

tblSubBasins
SubBasin_ID SubBasin_Name
A Athabasca
B Mackenzie
C North Saskatchewan

tblSubSubBasin
SubSubBasin_ID SubSubBasin_Name
A Pine
B Hummingbird
C Whitefish

So, if a sample was collected in the Athabasca basin, it would not have a Basin and SubBasin assigned to it but not a SubSubBasin. If a sample was collected from the Whitefish SubSubBasin then it would have a Basin, SubBasin and SubSubBasin assigned to it.

 

[wvandenberg]

Oops, my last post should read:

"So, if a sample was collected in the Athabasca basin, it WOULD have a Basin and SubBasin assigned to it but not a SubSubBasin." Sorry.

 

[lespaul]

"So, if a sample was collected in the Athabasca basin, it WOULD have a Basin and SubBasin assigned to it but not a SubSubBasin."

And is this something that the database needs to be aware of? How do you KNOW that rule?

Are the IDs that you have assigned in the example above "meaningful"...does A stand for something in some other context?

Have you read the Fundamentals document linked in my signature? It should help you identify the data that you're trying to collect and organize it according to the rules of databases.


Leslie

Anything worth doing is a lot more difficult than it's worth - Unknown Induhvidual

Essential reading for database developers:
The Fundamentals of Relational Database Design
Understanding SQL Joins

 

[MajP]

Personally I would do this in one single table if Basins, Sub Basins, and SubSubBasins all have the same type of information. This is called a self referencing table.

tblBasins
ID
strName
strType (Not really needed but, "Basin", "SubBasin", "SSB"
higherID

ID strName higherID
01 Maritime Provinces Drainage Area
02 St. Lawrence Drainage Area
03 Northern Québec and Labrador Drainage Area
04 Athabasca 01
05 Mackenzie 01
06 North Saskatchewan 02
07 Pine 04
08 Hummingbird 06
09 Whitefish 07

Now you can relate the table to itself by adding the table three times to a query.

With this kind of design I like to use a Tree View to show the hierarchy, which allows you to expand and collapse the nodes. It requires a bit of coding.

 

[wvandenberg]

Ooooh! I like your suggestion MajP. I will set up my table as you suggested.

Thanks!

 

[wvandenberg]

OK, so my table looks like this:

tblHydrologicUnits
Unit_ID ->AutoNumber
Unit_Name ->Text
Unit_Type ->Value List="Basin";"Watershed";"River"
("Basin" would always be the parent and would have a NULL Parent_Unit_ID, "Watershed" would be child1 and "River" could be either child1 or child2)
Parent_Unit_ID ->Number


I have a few questions.

1. Should the field "Unit_Type" be a foreign key to a separate table (tblUnitTypes) rather than a value list?

2. How would I go about setting up the query that would relate the table to itself and is this query useful for anything other than populating the TreeView control(which I do intend to use)?

3. I would like to include the information in this new table in another table I have called tblSites. Would I use the lowest "Unit_ID" as the foreign key in tblSites?

Leslie, I read through your link to "The Fundamentals of Relational Database Design" and am trying to normalize my tables. I understand the concept and can follow the examples in the article but am not sure I've applied the rules to my own tables correctly. Would it be OK to post another thread that outlines my "normalized" tables and their structure in order to solicit advice?

Thanks,
Wendy

 

[lespaul]

Wendy,

Glad you read the article! Feel free to post your table structure and we'll help you fine tune it. One of the reasons that I was asking the questions I was, was to help determine if you COULD keep all the data in a single table and have it be self referencing. I'm glad that that solution worked for you.

As far as your other questions:
1. I would make it another table, but that's just my preference. If you KNOW that you'll NEVER have to add anything, then a list would be fine.

2. The query would be:

SELECT A.UnitID, A.UnitName, A.UnitType, A.ParentID, B.UnitName as ParentName, B.UnitType As ParentType FROM tblHydrologicUnits A
INNER JOIN tblHydrologicUnits B ON A.ParentID = B.UnitID

3. What does the table tblSites do? A list of places to collect samples or where the samples were collected? What do you need to know about the HydrologicUnits in relation to the Site?

Leslie

Anything worth doing is a lot more difficult than it's worth - Unknown Induhvidual

Essential reading for database developers:
The Fundamentals of Relational Database Design
Understanding SQL Joins

 

[wvandenberg]

Leslie,

When I post my table structure, what should I include? Would table name and table fields be sufficient or should I include all the relationships as well?

1. Yes, I can see the client wanting to add more Unit_Types so I will use a separate lookup table.

2. Worked like a charm, thanks.

3. Yes, tblSites contains information about the location at which a sample is collected, many samples are collected from each site. tblSites contains the following fields

Site_ID (pk, AutoNumber)
Northing
Easting
UTM_Zone
GAL_Site_Name
AENV_Site_Name
RAMP_Site_Name
Industry_Site_Name
Other_Site_Name
Site_Name_Description
Hydrologic_Unit_ID ??????

As for the HydrologicUnits, I will be designing a form for users to select what analytical data they would like to view/export. I would like to design the form in such a way that the user will first select one or more Basins, then one or more Watersheds (in selected Basin(s)), then one or more Rivers (in selected Basin(s), Watershed(s)). From there, the user would select one or more sites (in selected Basin(s), Watershed(s), River(s)). Does that make sense? If you need more clarification, please let me know.

Wendy

 

[lespaul]

Just listing the tables and the fields and indicating relationships is the best way (PK for primary keys; FK for foreign keys and note the table and field that's the other end of the relationship).

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?

Leslie

 

[wvandenberg]

OK, I'm working on the list of tables etc.

For the Sites table, I know I've broken one of the rules for 1NF: No repeating groups. However, I was not sure how to deal with the situation. The situation is that I have legacy data and some sites have one or more site names. Going forward, ALL new sites will be required to have a GAL_Site_Name. Some sites are used by agencies other than my company and therefore there may be one or more site name (other than GAL_Site_Name). We would like to be able to "match up" data we import from other agecies to an existing GAL_Site_Name (if there is one).

Wendy

 

[wvandenberg]

Leslie,

Just wondering if I should start a new thread with my table structure. There are 17 tables.

Wendy

 

[lespaul]

That would be best...also try to describe what it is the system "does"...

Leslie

 

[wvandenberg]

MajP,

I created the self-referencing table as you suggested but I have no idea how to program the TreeView control. Any hints?

Wendy

 

[MajP]

This is pretty VBA intensive, and not for the feint at heart. I do not know how good you are at VBA. The other thing is uses a "recursive function"; a function that calls itself. This can be a very difficult concept to get your hands around. If you are proficient here is some example code. I would also use the advance search feature and look at other posts on this site dealing with tree views. Also goggle the web for more examples.

In this example the data comes from a query called:
qryNodeSort

The foriengn key in my self referencing query is
txtParentUIC in your case "Parent_Unit_ID"

My primary key is
txtUICcode

my treeview control is called
Xtree

The other fields are used to add text and keys to the nodes.

Private Sub Form_Load()
  Dim objTree As TreeView
  Dim RStasks As Recordset
  Set objTree = Me!xTree.Object
  Set RStasks = CurrentDb.OpenRecordset("qryNodeSort", dbOpenDynaset)
  ' My highest node in the hierrarchy has a 'W' in this field
  ' You need to tell the code what signifies the highest node
  ' Since it/they are the highest level it/they do not have a parent
  Call subAddBranch(RStasks, "W")
End Sub

Private Sub subAddBranch(myRS As Recordset, theCurrentID As String)
  Dim strCriteria As String
  Dim bk As String
  Dim blnSelected As Boolean
  Dim currentTaskNumber As String
  Dim currentHigherTask As String
  Dim currentTaskDescription As String
  Dim nodeCurrent As Node
  Dim nodeParent As Node
  Dim objTree As TreeView
  
  Set objTree = Me!xTree.Object
  ' My foriegn key field is called 'txtParentUic'
  ' My highest node in the hierrarchy has a 'W' in this field
  ' You need to tell the code what signifies the highest node
   currentTaskNumber = theCurrentID
   strCriteria = "txtParentUIC = '" & currentTaskNumber & "'"
   If Not currentTaskNumber = "W" Then
     Set nodeParent = objTree.Nodes("A" & currentTaskNumber)
   End If
   myRS.FindFirst (strCriteria)
  
  Do Until myRS.NoMatch
   currentTaskNumber = myRS![txtUICcode]
   currentTaskDescription = myRS![txtOrganizationShortName] & "          MULT. " & myRS![numMultiple] & "      # SELECTED:  " & myRS![calcNumberSelected]
   currentHigherTask = myRS![txtParentUIC]
  If currentHigherTask = "W" Then
     Set nodeCurrent = objTree.Nodes.Add(, , "A" & currentTaskNumber, currentTaskDescription)
      nodeCurrent.Tag = currentHigherTask
    Else
     Set nodeCurrent = objTree.Nodes.Add(nodeParent, tvwChild, "A" & currentTaskNumber, currentTaskDescription)
     nodeCurrent.Tag = currentHigherTask
    End If
    bk = myRS.Bookmark
    Call subAddBranch(myRS, currentTaskNumber)
    myRS.Bookmark = bk
    myRS.FindNext (strCriteria)
  Loop
End Sub

subAddBranch calls itself and works down each branch until it gets to the end.

 

[wvandenberg]

Thanks MajP for the code, I'm just trying to get it all set up. I'm wondering if the SQL that that Leslie provided will work for my "qryNodeSort"?

 

[wvandenberg]

I've implemented the sample code from the Microsoft website (it's very similar to the example MajP gave me). However, I'm having a small problem. The union query I'm using to populate the tree is based on two tables and the pkey for each table is and AutoNumber. Therefore, I have duplicate values in the NodeID field. This is giving me runtime error 35602 'Key is not unique in collection'. I read in another post that I can use a counter within the sub to ensure a unique key but the example was not recursive. Could anyone offer a suggestion as to how I can fix this problem with my recursive procedure below?

The first root node is showing up but the child of the first root node has the same Node ID so that's where the error is occurring.

Here is the code:

'=================Load Event for the Form=======================
'Initiates the routine to fill the TreeView control
'===============================================================

Private Sub Form_Load()
Const strTableQueryName = "qryxTreeView"
Dim db As Database, rst As Recordset
Set db = CurrentDb
Set rst = db.OpenRecordset(strTableQueryName, dbOpenDynaset, _
dbReadOnly)
AddBranch _
rst:=rst, _
strPointerField:="ParentID", _
strIDField:="NodeID", _
strTextField:="Node"
End Sub

'================= AddBranch Sub Procedure =========================
' Recursive Procedure to add branches to TreeView Control
'Requires:
' ActiveX Control: TreeView Control
' Name: xTree
'Parameters:
' rst: Self-referencing Recordset containing the data
' strPointerField: Name of field pointing to parent's primary key
' strIDField: Name of parent's primary key field
' strTextField: Name of field containing text to be displayed
'===================================================================
Sub AddBranch(rst As Recordset, strPointerField As String, _
strIDField As String, strTextField As String, _
Optional varReportToID As Variant)
On Error GoTo errAddBranch
Dim nodCurrent As Node, objTree As TreeView
Dim strCriteria As String, strText As String, strKey As String, varTag As Variant
Dim nodParent As Node, bk As String
Set objTree = Me!xTree.Object
If IsMissing(varReportToID) Then ' Root Branch.
strCriteria = strPointerField & " Is Null"
Debug.Print strCriteria
Else ' Search for records pointing to parent.
strCriteria = BuildCriteria(strPointerField, _
rst.Fields(strPointerField).Type, _
"=" & varReportToID)
Set nodParent = objTree.Nodes("a" & varReportToID)
End If
' Find the first emp to report to the boss node.
rst.FindFirst strCriteria
Do Until rst.NoMatch
' Create a string with LastName.
strKey = "a" & rst(strIDField)
Debug.Print strKey
strText = rst(strTextField)
Debug.Print strText
varTag = rst(strPointerField)
Debug.Print varTag
If Not IsMissing(varReportToID) Then 'add new node to the parent
Set nodCurrent = objTree.Nodes.Add(nodParent, _
tvwChild, strKey, strText)
nodCurrent.Tag = varTag
Else ' Add new node to the root.
Set nodCurrent = objTree.Nodes.Add(, , strKey, _
strText)
nodCurrent.Tag = varTag
End If
' Save your place in the recordset so we can pass by ref for
' speed.
bk = rst.Bookmark
' Add employees who report to this node.
AddBranch rst, strPointerField, strIDField, strTextField, _
rst(strIDField)
rst.Bookmark = bk ' Return to last place and continue search.
rst.FindNext strCriteria ' Find next employee.
Loop
exitAddBranch:
Exit Sub
'--------------------------Error Trapping --------------------------
errAddBranch:
MsgBox "Can't add child: " & Err.Description, vbCritical, _
"AddBranch Error:"
Resume exitAddBranch
End Sub

Here is the SQL for the union query that populates the tree:

SELECT WSNodeID AS NodeID,WSNode AS Node, WSParentID AS ParentID, Image
FROM qselWatersheds

UNION SELECT WCNodeID AS NodeID,WCNode AS Node,WCParentID AS ParentID, Image
FROM qselWaterCourses;

Thanks,
Wendy

 

[MajP]

I am not certain about your data structure because I can not envision how you get a hierarchical structure with like primary IDs. I always want to save a unique record ID as the nodes key so that I can associate a record to a node. The key is very weird and I do not know the answer. The key has to be a non numeric string. If you make the key "123" or even cstr(123) it throws an error. So that is the only reason you see the "a" concatenated to the ID.
strKey = "a" & rst(strIDField)

You can simply put a counter in, but that defeats the ability to associate the node to a unique record. If the node is associated I can double click on the node and bring up the record. I can also move nodes around in the tree and associate them to different parents.

So my solution would not be to add a counter but a field in each table to say if it is "WC" or "WS". Lets call that field something like "strNodeType". So if I have two IDs
123 from watershed and 123 from watercourse I would make a key WC123 and a key WS123.
strKey = rst(strNodeType) & rst(strIDField)

I am not good at sql, but I think you can add WS or WC by just doing something like:

SELECT WSNodeID AS NodeID,"WS" as strNodeType...
FROM qselWatersheds

Now all your keys start with WS or WC which is good because you also then know which table they come from. You then will be able to do something like this when you click on a node:

Private Sub Xtree_NodeClick(ByVal selectedNode As Object)
On Error GoTo Err_NodeClick
    Dim stDocName As String
    Dim stLinkCriteria As String
    Dim WSorWC as string
    Dim selectedSite As String
    selectedSite = Right(selectedNode.Key, Len(selectedNode.Key) - 2)
    WSorWC = left(selectedNode.key,2)
    if WSorWC = "WS" then
      stDocName = "frmWSsites"
    else
      stDocName = "frmWCsites"
    end if
    stLinkCriteria = "ID = " & selectedSite 
    DoCmd.OpenForm stDocName, , , stLinkCriteria
Exit_NodeClick:
    Exit Sub
Err_NodeClick:
    MsgBox Err.Description
    Resume Exit_NodeClick
End Sub

One more thing. This line of code:
If IsMissing(varReportToID) Then ' Root Branch.

means that your very highest node will not have any value in the parentID field. Which looks something like the data I should originally:

ID   strName                                      higherID
01   Maritime Provinces Drainage Area
02   St. Lawrence Drainage Area
03   Northern Québec and Labrador Drainage Area
04   Athabasca                                     01
05   Mackenzie                                     01
06   North Saskatchewan                            02
07   Pine                                          04
08   Hummingbird                                   06
09   Whitefish                                     07

These three have no parents they are top nodes.
01 Maritime Provinces Drainage Area
02 St. Lawrence Drainage Area
03 Northern Québec and Labrador Drainage Area

Your tree then looks like this

-Maritime Provinces Drainage Area
Athabasca
Pine
Whitefish
Mackenzie
-St. Lawrence Drainage Area
North Saskatchewan
Hummingbird
-Northern Québec and Labrador Drainage Area

 

[MajP]

Wendy,
If you get the tree working, I have a lot of code to add "bells and whistles" if you are interested.
-add different icons per level or type of node
-collapse and expand tree
-reorder data by drag and drop
-add and delete nodes and associated records