Databinding and Many to Many relationships

[gotch_ur_number]

Hello All,

How do you databind tables whos 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 its area implementation (from field SecurityNote in table intersection; see below)


Here is the form:

[Broken External Image]: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.


[Broken External Image]:http://www.salemfloorplans.com/vb/relationship.jpg


And I have populated the tables as such:

Table: Server

[Broken External Image]:http://www.salemfloorplans.com/vb/server.jpg


Table: SecurityArea

[Broken External Image]:http://www.salemfloorplans.com/vb/securityArea.jpg

And Intersection

[Broken External Image]: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. Its 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 its done purely because I should.

Many many many thanks.

Drew