Databinding and Many to Many relationships

[gotch_ur_number]

Hello All,

 

How do you databind tables who's relationship have been decomposed to create a third intersecting table?

 

The details:

Lets say we need to create an application that will allow us to manage Security features for all SQL Servers in the organisation (the project I chose). This is what it should do and below is an image of the form.

 

You select the relevent server via the navigator (it displays ServerName from the Server table; see below), and it displays the security areas (it displays the AreaTitle field from table SecurityAreas; see below) that have been chosen for implementing on this server, in listbox "listSecurityArea". When you click on the Area title in the listbox, it then displays the notes that are UNIQUE to each server and it's area implementation (from field SecurityNote in table intersection; see below)

 

 

Here is the form:

 

http://www.salemfloorplans.com/vb/form.jpg

 

There are 2 tables, one called Servers, the other called SecurityAreas:

 

Server (ServerID, ServerName, ServerDescription)

 

SecurityArea (SecurityAreaID, AreaTitle, SecurityAreaDescription)

 

Now, a Server can have many Security Areas that need looking at, such as "what kind of authentication mode is it in?", and "Which ports have been closed" etc. And a Security Area can be applied to many Servers. This means we have a many - to - many relationship. This is not allowed in relational databases and so we decompose the relationship to give us a third table, lets call it Intersection:

 

Intersection (ServerID, SecurityAreaID, SecurityAreaNotes)

 

There is now a one - to - many relationship between Server and Intersection and a one - to - many relationship between SecurityArea and Intersection.

 

 

http://www.salemfloorplans.com/vb/relationship.jpg

 

 

And I have populated the tables as such:

 

Table: Server

 

http://www.salemfloorplans.com/vb/server.jpg

 

 

Table: SecurityArea

 

http://www.salemfloorplans.com/vb/securityArea.jpg

 

And Intersection

 

http://www.salemfloorplans.com/vb/intersection.jpg

 

Using "listSecurityArea"s displaymember to display columns from table Intersection is no good, because it does not contain the SecurityAreaTitle needed to be displayed in the listbox.

 

Also, I cannot set the SelectedValue to a field in this table either as the Primary Key is a composite, and so 2 fields would need to be selected.

 

Also, I cannot use a stored procedure that joins the neccessay tables to display the SecurityAreaTitle in the listbox, because I can only pass 1 item (the SecurityAreaTitle) into the dataset. But I also need the SecurityAreaID in order to further look up the Area Notes when they click on the listbox:

 

 

 

' With myCommand

 

' .CommandType = CommandType.StoredProcedure

 

' .CommandText = "usp_RetrieveSecurityPerServer"

 

' .Parameters.Clear()

 

' .Parameters.Add("@ServerID", SqlDbType.Int)

 

' .Parameters("@ServerID").Direction = ParameterDirection.Input

 

' .Parameters("@ServerID").Value = cboServer.SelectedValue

 

 

 

' MySQLAdapter.Fill(MyDataSet, "ID_AND_TITLE")

 

' For Each myDataRow In MyDataSet.Tables("ID_AND_TITLE").Rows

 

' listSecurityArea.Items.Add(myDataRow("AreaTitle").ToString())

 

' Next

 

I have tried with stored procedures that have joins in them, but no combination seems to provide all I need to display the serverName, securityTitle and Notes while binding the 3 together. It's such a common thing in a database though, 100s of developers must of come across this. I would have thought there was text book way of doing this, without having to denormalise the database.

 

I need to know how it's done purely because I should.

 

Many many many thanks.

 

Drew

Edited October 4, 2006 by gotch_ur_number