The Bill of Materials structure is the standard solution when you have one table that has a many-to-many relationship, not with another table, but with itself .

Everyone who has used a relational database has seen two tables with a one-to-many relationship. Also well known is the case where two tables are given a many-to-many relationship by placing a third table, sometimes called a "junction", in between, as shown here, where one row in first_ent might match several in second_ent , and vice versa:

Even the case of a single table having a one-to-many relationship with itself, as in a standard hierarchy, is common enough. In the diagram below, several rows in the hierarchy table may match their parent_id to the hierarchy_id of another row. (See also Sandeep Gali's article on Generating a Tree structure (Asynchronously) for a discussion of using this type of structure.)

Because tables with a many-to-many relationship to themselves are less common, many developers have not seen them. The classic example is a mechanical assembly that is composed of several sub-assemblies but is itself part of several larger units.

None of the usual database structures can model such relationships, but the Bill of Materials structure does it very well. To see how the structure works, consider again the many-to-many diagram shown above. To find all the children of a given parent we could use an SQL statement such as:

SELECT * 
FROM first_ent a           /* the specified row from first_ent is the parent */
JOIN junction1 j  ON j.first_ent_id  = a.ent_id
JOIN second_ent b ON j.second_ent_id = b.ent_id  
                           /* rows from second_ent are the children */
WHERE a.ent_id = @parameter

Note: I only use the "SELECT *" syntax here to keep the example simple. Explicitly listing the fields is more efficient and helps make the query self-documenting.

Notice that the tables first_ent and second_ent are identical except for their name. If we merged them into one table the diagram would be:

and the preceding SQL would be re-written as:

SELECT * 
FROM ent a
JOIN junction j  ON j.first_ent_id     = a.ent_id
JOIN ent b       ON j.second_ent_id    = b.ent_id
WHERE a.ent_id = @parameter

We should point out a few things about the junction table. It has two foreign keys, first_ent_id and second_ent_id , both of which are related to ent_id , the primary key in the ent table. The junction table also has an identity column, junction_id . I found that without it, if the junction table had two identical rows, I had trouble deleting the rows. The SQL for setting up these tables is in the download material.

Enough theory, everyone is really interested in applications. How might you actually use this structure?

Relationships between people are as complex as they come, so let's consider some typical relationships between people:

(Feel free to add relationships of your own; in-laws, business partners, favorite-uncle-of, not-speaking-to; in a real app you would probably provide a dropdown list if only to enforce standardization.)

The first thing we need is a screen for setting up these relationships:

As will be seen later, it doesn't matter whether Beatrice or Harry is entered as "First Person". The screen, as seen from the code shown below, is quite simple. It is just an ordinary data entry form that stores a row in the database, so I provide only a few comments.

<%@ LANGUAGE="VBScript"%>
<% 
Option Explicit 
Response.Buffer=True
%>
<!-- #include file=adovbs.inc -->

I use Option Explicit as a standard practice so that all variables must be declared. The include file provides standard constants to be used throughout the program; those of you who are more efficiency conscious may want to create your own include file, with only those constants you regularly use.

<%
  Dim strFeedBack, Conn, Cmd, rsNames, strTitle
  Set Conn   = Server.CreateObject("ADODB.Connection")

  Conn.ConnectionString = "PROVIDER=SQLOLEDB" & _
                          ";SERVER=dohrsgateway;UID=sa;PWD=;DATABASE=Testbed"
  Conn.Open
  
  Set Cmd = Server.CreateObject("ADODB.Command")
  Cmd.ActiveConnection = Conn
  
  'Recordset to populate the screen
  Set rsNames = Server.CreateObject("ADODB.RecordSet")
  rsNames.Open "proc_retrieve_names", Conn, adOpenStatic, adLockReadOnly, _
               adCmdStoredProc 
%>

I generally create most of my ADO objects at the start of my code. It would be more efficient to wait until they are actually needed but the code is easier to maintain this way. Note that in the recordset I use a static cursor and a read-only lock. I'm not worried about the data changing while the recordset is open and we won't be updating the list of names ourselves.

Next we come to a set of subroutines which will be used farther down. I generally put my subroutines between the <HEAD>…</HEAD> tags – it's just a matter of preference:

<HTML>
<HEAD>

<SCRIPT runat="Server" language="VBScript">

'==============================================================================
' SHOW Data Section
'==============================================================================
'********************************************************************
'Purpose:  Generate a SELECT box from a recordset
'Input: rsOptions   Recordset to provide the OPTIONS.
'       strName     Name to be given the SELECT box.
'       strValue    Name of the recordset field to serve as the OPTION.value
'       strDisplay  Name of the recordset field to serve as the OPTION.text
'       intSize     Number of items to display in a scrolling list
'       strMultiple Indicates whether multiple selections are permitted
'********************************************************************

Sub SelectBox(rsOptions, strName, strValue, strDisplay, intSize, strMultiple)
  Dim strLine
  strLine = "<SELECT name=" & strName & " id=" & strName & "size=" _
            & intSize & " " & strMultiple & ">"
  strLine = strLine & "<OPTION value=''></OPTION>" & vbCRLF 

  Do Until rsOptions.EOF
    strLine = strLine & "<OPTION value=" & Trim(rsOptions.Fields(strValue)) _
              & ">" 
    strLine = strLine & rsOptions.Fields(strDisplay) & "</OPTION>" & vbCRLF 
    rsOptions.MoveNext
  Loop

  strLine = strLine & "</SELECT>"
  Response.Write strLine
  'Return to the top of the recordset to be ready to use it again.
  rsOptions.MoveFirst
  
End Sub
'==============================================================================' UPDATE Data Section
'==============================================================================
Function EditForm()
  Dim strErr 
  strErr = ""
  IF Len(Request("txtFirstRel"))  = 0 OR _
     Len(Request("txtSecondRel")) = 0 OR _
     Len(Request("selFirst"))     = 0 OR _
     Len(Request("selSecond"))    = 0   THEN
    strErr = strErr & "All fields are required.<BR>"
  END IF
  
  EditForm = strErr
End Function

Function InsertRelationship()
  Dim strSQL
  strSQL = "proc_insert_relationship"
  Cmd.CommandText = strSQL
  Cmd.CommandType = adCmdStoredProc
  SetParms
  
  Cmd.Execute
End Function

Sub SetParms()
    cmd.Parameters.Append cmd.CreateParameter("selFirst", adInteger, _
                                             adParamInput,,Request("selFirst"))
    cmd.Parameters.Append cmd.CreateParameter("selSecond", adInteger, _
                                            adParamInput,,Request("selSecond"))
    cmd.Parameters.Append cmd.CreateParameter("txtFirstRel", adVarChar, _
                                        adParamInput,20,Request("txtFirstRel"))
    cmd.Parameters.Append cmd.CreateParameter("txtSecondRel", adVarChar, _
                                       adParamInput,20,Request("txtSecondRel"))
End Sub

</SCRIPT>

In the body we have the "mainline" of the application, a SELECT CASE statement. We could have put it elsewhere; like the subroutines, placement is a matter of choice.

<BODY>
<FORM name="RelForm" action="<%=Request.ServerVariables("PATH_INFO")%>"  
      method="post">

The function and subroutine calls below appear in bold print to make them easy to pick out. Note that Request("Process") comes from the Submit button.

<%
SELECT Case Request("Process")
Case "Setup" 
  Dim strStatus
  'User submitted the form; insert the data into the DB
  strStatus = EditForm
  If strStatus = "" Then
    'Update the DB
    InsertRelationship
  Else
    'Let them know what the problem is.
    strFeedBack = strStatus
  End If  
  strTitle = "Setup Feedback"
  
Case Else
  'Display a blank form
  strTitle = "Setup a Relationship<BR>"
End Select  

The above Case statement (as opposed to an If...Else statement) makes it easy to add other options that a production program may require.

Response.Write "<H3>" & strTitle & "</H3><BR>"
Response.Write "<B>" & strFeedBack & "</B><BR>"

%>
  <TABLE border="0" width="100%">
    <TR>
      <TD></TD>
      <TD align="left">First Relationship --><BR>
      <INPUT type=text name=txtFirstRel size=20></TD>
      <TD></TD>
    </TR>
    
    <TR>
      <TD align="left">First Person<BR>
       <%SelectBox rsNames, "selFirst", "ent_id", "ent_name", 4, "" %>
      </TD>
      <TD></TD>
      <TD align="left">Second Person<BR>
       <%SelectBox rsNames, "selSecond", "ent_id", "ent_name", 4, "" %>
      </TD>
    </TR>
    
    <TR>
      <TD></TD>
      <TD align="left"><-- Second Relationship<BR>
      <INPUT type=text name=txtSecondRel size=20></TD>
      <TD></TD>
    </TR>
    
  </TABLE>

  <CENTER>
        <INPUT type="submit" value="Setup" name="Process">
  </CENTER>

<%
  'Cleanup.
  If rsNames.State = adStateOpen Then
    rsNames.Close
    SET rsNames    = Nothing
  End If
   
  Conn.Close
  Set Conn = Nothing
  Set Cmd  = Nothing

%>
</FORM>
</BODY>
</HTML>

The stored procedure which does the insert is simple also, but for the sake of completeness here it is:

CREATE PROCEDURE proc_insert_relationship
    (@id1 int, @id2 int, @rel1 varchar(40), @rel2 varchar(40))
AS
INSERT INTO junction
(first_ent_id, second_ent_id, first_sec_relationship, sec_first_relationship)
VALUES (@id1, @id2, @rel1, @rel2)
Return

After you have added the data you can review it in the next screen:

Here we see all of Tom's relatives. Notice that the names of the relatives are links. If you click on one of these names, say Beatrice, the page redisplays showing Beatrice's relatives. You will see that Harry is her nephew and Tom is her brother.

Most of this form is simple also and I won't go into detail on the code; however, the SQL is worth discussing. Here is the stored procedure, proc_retrieve_relatives :

CREATE PROCEDURE proc_retrieve_relatives (@starter varchar(20))
AS
SELECT  a.ent_id AS ent_id1, a.ent_name AS ent_name1,
        j.first_sec_relationship, j.sec_first_relationship,
        b.ent_id AS ent_id2, b.ent_name AS ent_name2
FROM ent a
JOIN junction j ON  j.first_ent_id = a.ent_id
JOIN ent b      ON  b.ent_id       = j.second_ent_id
WHERE a.ent_name = @starter
UNION
SELECT  a.ent_id AS ent_id1, a.ent_name AS ent_name1,
        j.sec_first_relationship, j.first_sec_relationship,
        b.ent_id AS ent_id2, b.ent_name AS ent_name2
FROM ent a
JOIN junction j ON j.second_ent_id = a.ent_id
JOIN ent b      ON b.ent_id        = j.first_ent_id
WHERE a.ent_name = @starter

The first thing to notice is that this procedure uses the ANSI-92 standard syntax, i.e:

FROM table1
JOIN table2 ON table1.field1 = table2.field2 …

instead of the older way:

FROM table1. table2, ...
WHERE table1.field1 = table2.field2 AND ...

I resisted the newer style for a long time because all the samples I'd seen were wizard-generated with horrible formatting and deeply nested (not to mention unnecessary!) sets of paragraphs. Now that I have gotten used to it and have seen how it can be formatted for readability, I prefer it.

Second, and what may have been the first thing many readers noticed, the SQL contains two SELECT clauses joined by a UNION . It may be a little hard at first to see why two SELECT s are needed. They look almost identical. Repeated below is the first SELECT . Look carefully at the parts in bold and compare to the corresponding parts in the second SELECT . You should notice that things are turned around somewhat.

SELECT   a.ent_id AS ent_id1, a.ent_name AS ent_name1,
         j.first_sec_relationship, j.sec_first_relationship,
         b.ent_id AS ent_id2, b.ent_name AS ent_name2
FROM ent a
JOIN junction j ON  j.first_ent_id = a.ent_id
JOIN ent b      ON  b.ent_id       = j.second_ent_id
WHERE a.ent_name = @starter

The importance of the difference is that the first SELECT by itself would have missed the relationship because, the way I set the relationships up, Tom is represented in the junction row as second_ent_id and so would be missed by the combination of the first JOIN , JOIN junction j ON j.first_ent_id = a.ent_id , and the WHERE clause, WHERE a.ent_name = @starter .

The obvious way to fix the problem, by changing the WHERE clause to WHERE a.ent_name = @starter OR b.ent_name = @starter , would have given us "Tom is husband of June" with "Tom" being the link instead of "June". The UNION provides the only satisfactory solution.

If you are unfamiliar with UNION s, the basic rule is that the SELECT s must have the same number of columns and the columns in the second and following SELECT s must be the same data type as the corresponding columns in the first.

Notice that I aliased both the column names and the table names. The table names are aliased because " ent " appears twice in each FROM , in two different roles. The columns ent_id and ent_name must be aliased because they appear twice in each SELECT .

In Conclusion…

This is a difficult topic and even though we used a simple example you may have to think about it carefully before you understand it. I once worked with a lead programmer who, never having seen it before, was very reluctant to believe it was an accepted technique. I also once had a project manager who was so fond of this structure that he was bound and determined to use it, whether it made sense or not.

My favorite application of the Bill of Materials structure was at a company that had a complicated web of relationships with a large number of other companies, involving layers of partnerships and subsidiaries. There was no other way to model the relationships. I hope that, having read this article, you will know how to use the Bill of Materials structure and when.