Databinding and Many to Many Relationships

[drew]

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:

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.

And I have populated the tables as such:

Server

SecurityArea

And Intersection

Now, I've written enough, so I won't go on about how I've been trying to do this, but what I will say is this:

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


There MUST be a straight forward solution, and I guessing all you pro developers must do this on a day to day basis???

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

Many many many thanks.

Drew

 

[cjard]

Looking at your form, I cannot actually see the many-many relationship. Your form appears to be arranged so that a single SQL server can be chosen and many security areas can be applied.

For this reason, your relationship right now in this context is not many/many, it is one server/many areas

Thus, your relations are arranged the wrong way round in this particular dataset. What you would have is a dataset with 3 tables:

[Server] 1P----MC [Intersection] 1P----1C [SecurityArea]

One server parent has many children in the intersection. Routing through the intersection, each child in the intersection when serving as a parent row, has one child in the securityarea. One server, many intersections, each intersection one securityarea = one server/many areas

Automatic databinding and relations in ADO.NET basically allows you to start with a dataset full of parents and children, and upon selection of a parent, the children are filtered. Thus the screen for choosing a single SQL server and applying security areas to it would have this.

If you had another screen that allowed you to choose a single security area and drop sqlservers into it, then it would have its own dataset but with the relations the other way round:

[Server] 1C----1P [Intersection] MC----1P [SecurityArea]


Look at the Cs and Ps in this case.. they are swapped round and we are traversing right to left in the drilldown


Both these relations can exist in the same dataset as long as you are using them as relators and not constraint enforcers (or are willing to toggle constraints on and off during updtes to avoid chicken/egg)

Conceptually, both the functions can be achieved on a single screen and depending which list you interacted with last affects which binding route you use, but I would strongly recommend that you use separate screens (or at least tabs) and datasets