It appears that we can not make attachments else I would show screen shots and diagrams. So, I will do my best in text...


Initially I am not yet using the hierarchical features of Infragistics UltraGrid - although in the following example I very well could. The current problem involves data from multiple DB tables where the relationships are many-to-many and are implemented via link tables. These link tables contain additional information aside from the necessary foreign key pairs.

The design is such that Address is a generic concept and thus does NOT contain any pointers (foreign keys), but rather is pointed to by various link tables. Why, because we have ConstructionProjects, Clients, Vendors, Customers, Employees, Contacts (did I forget anybody?) that have addresses(es); quite many-to-many. Address is not the only generic concept. There is also Communication (which have classification and effective dating within the link) and AccountTransaction, for example.




For the problem at hand...

Specifically I have a Client table:
CREATE TABLE [dbo].[Client](
[ClientID] [int] IDENTITY(1,1) NOT NULL PRIMARY KEY CLUSTERED,
[ClientName] [varchar](50) NOT NULL,
[ClientTypeCode_Legacy] [varchar](20) NULL,
[ContNo_Legacy] [int] NULL
);

a general Address table:
CREATE TABLE [dbo].[Address](
[AddressID] [int] IDENTITY(1,1) NOT NULL PRIMARY KEY CLUSTERED,
[AddressLine1] [nvarchar](50) NOT NULL,
[AddressLine2] [nvarchar](50) NULL,
[City] [nvarchar](50) NOT NULL,
[StateCode] [nvarchar](7) NOT NULL,
[PostalCode] [nvarchar](12) NULL
);

Clients have 'Billing Addresses' that are classified by a LocationCode (a lookup table):
CREATE TABLE [dbo].[ClientBillingLocationCode](
[Code] [nvarchar](20) NOT NULL PRIMARY KEY CLUSTERED ,
[Description] [nvarchar](50) NOT NULL
);

That LocationCode is embedded within the 'link' table between Client and Address:
CREATE TABLE [dbo].[ClientBillingAddressLink](
[ClientID] [int] NOT NULL,
[AddressID] [int] NOT NULL,
[BillingLocationCode] [nvarchar](20) NOT NULL,
[Note] [nvarchar](50) NULL,
CONSTRAINT [PK_ClientBillingAddressLink] PRIMARY KEY CLUSTERED
(
[ClientID] ASC,
[AddressID] ASC,
[BillingLocationCode] ASC
);
Additionally ClientID is constrained as Foreign Key to the Client table, and AddressID is constrained as Foreign Key to the Address table.




Lets say that my UI presents a list of Clients - perhaps in a ListBox. Upon selection of a Client in that ListBox, I need to retrieve the info from both the Address table and the ClientAddressLink table via the selected object's ClientID. Since it is possible for a Client to have many addresses, I wish to display in a grid on a single row the Address fields and the BillingLocationCode field (as a lookup; i.e. UI element = DropDown) and the Note field - the latter two coming from the link table. When presented on a single row it should be fairly easy for a user to enter/modify a list of addresses, classifying them via the LocationCode DropDown. {Wish I could give y'all a screen shot here :( }

I image it is not feasable to bind a grid to two separate tables. Thus I have created a View (ClientBillingAddressView) in the database that combines it for me:
CREATE VIEW [dbo].[ClientBillingAddressView]
AS
SELECT
CBAL.ClientID,CBAL.AddressID, CBAL.BillingLocationCode, CBAL.Note,
ADDR.AddressLine1, ADDR.AddressLine2, ADDR.City, ADDR.StateCode, ADDR.PostalCode
FROM dbo.Address AS ADDR
INNER JOIN
dbo.ClientBillingAddressLink AS CBAL
ON ADDR.AddressID = CBAL.AddressID

This is retrieved from the Client BizObject via a stored procedure:
CREATE PROCEDURE [dbo].[ClientBillingAddressViewSelectByClientID]
(@ClientID int)
AS
BEGIN
SET NOCOUNT ON;
Select * from ClientBillingAddressView
where ClientID = @ClientID
END

BTW, this is a MSSQL database (ver. 2005).

If I stick with using a View there will need to be the requisite Insert, Update and Delete procedures - as of yet unwritten.

I would much rather handle this via Business Objects that "known" how to deal with the concept of many-to-many to the extent that data from multiple backend sources can be "fused" into a seamless presentation. Again, if I stick with a View, I actually have a Business Object and can write (or convince a template to write for me) the necessary backend sprocs. I'm just not convinced that a View is the way to go. Consider also that I do not use DataSets - I'm trying to be pure BizObject. I agree from the documentation that one of the parent BizObjects needs to handle the link table, but then how to merge any additional info from the link table into the complex binding of a UI widget such as a grid?


As for some data so you can visualize this:
{Wish that this reply form could handle rich text and/or attachments}

The Client table...
ClientID ClientName ClientTypeCode_legacy ContNo_legacy
1 Mansions-R-US 1001
2 ABC Custom Homes 1002


The ClientAddressLink table...
ClientID AddressID BillingLocationCode Note
1 1 OFFICE
2 2 OFFICE
2 3 JOB


The Address table... (all AddressLine2s are null)
AddressID AddressLine1 City State PostalCode
1 9000 W. Sunset Blvd. Beverly Hills CA 90210
2 7116 Valjean Ave Van Nuys CA 91406
3 20200 Pacific Coast Hwy Malibu CA 90265


The ClientBillingLocationCode table...
Code Description
JOB Job site
MAIN Corporate Headquarters
OFFICE Local Office




As for parent/child rows in a grid: yes, the above example could be implemented in a purely grid presentation where the Clients are the uppermost of the hierarchy and the (Billing) addresses are presentationally children. The problem again is that the actual 'child' foreign key belongs to the link table such that the hierarchy is abruptly halted. This is one of the prices paid in order to normalize a database!

I have numerous legitimate many-to-many structures in this project that will have differing UI presentations. Your example of Users/Roles using Available vs. Current is just one of them, and good for the nature of that manipulation.


I could rethink the presentation, but 7 columns in a grid is quick for the user (and hopefully me someday!) - I have sufficient real-estate. I suppose I could make the grid just a read-only display and have the user invoke an editor dialog (which could handle dealing with multiple BizObjects). Still the problem of "fusion" (as discussed above) will crop up again most likely.


Anyway, thanks for whatever consideration you can make.

TonyK