Datarelation - Left Join

[RFH Reseller: creativeworks]

using the old Datarelation feature going on in ASP.Net.

here's my stumble.

I have three tables, I'm relating two tables together with ADO.Net - this works great.

Now, if within my parent and child SQLs I join a single column from a third table to both of the parent and child tables - it throws up a unique column error.

I've tried dynamically renaming the offending table column to something different but no glory. Ideas?

[RFH Reseller: Sol]

This is a bit difficult to tell without you giving us a little idea of the data structure, and infact I would suggest that if you are joining a table to both Parent and Child that the database probably isn't normalized prperly.

If you can provide us with the descriptive table names and a few columns in each.

Personally I tend to build my databases seperately and create stored procedures for most of the data processing (a bit old school since LINQ is round the corner). However, by doing it this way I can isolate Data Acess into it's own DLL and use the Provider Model to load the right DLL. This means a change in the database doesn't require the whole project to be re-built and deployed, I can simply update the Data Provider and be done. Of course when LINQ is fully released I could update my Data Providers to take advantage of that without having to re-build the rest of the application.

To save time I sometimes use Code Generation software to inspect the database and create the C# code I need to access it (this code generation will also build CRUD stored procedures so I don't have to repeat mundane tasks).

For a free .Net base code generator have a look at http://www.mygenerationsoftware.com

[RFH Reseller: creativeworks]

the two tables are Categories and Pages.

Pages are Children of Categories, but Categories can have a page 'attached' to them - this is done by saving the id of the page in the Categories table.

I have a third table which includes extra information about the rows in the Pages table, which is used by the Pages table and Categories table.

When I perform a Left Join on the Categories table, to import some information from the third table which includes extra information about the 'attached' page, I get the error:

These columns don't currently have unique values.

This error only occurs if I perfom the Left Join on the Parent table, and then use a DataRelation with DataSets. I can perform as many Left Joins on the Child table as I like.

What am I missing, what don't i understand about DataRelation!?

[RFH Reseller: Sol]

OK, so you got a Categories table and a Pages table with the following:

1). Categories.CategoryID - one-to-many - Pages.CategoryID
2). Categories.PageID - many-to-many - Pages.PageID
3). Pages.PageID - one-to-many - PageRows.PageID

Note, we don't have anything relating to the Categories in the PageRows table since we can get that info using a single SQL statement:

SELECT * FROM PageRows
JOIN Pages AS P ON P.PageID = Pages.PageID
WHERE PageID = (SELECT PageID FROM Categories WHERE CategoryID = @CategotyID)

I think you are confusing yourself by trying to link PageRows to the Categories. However, a second possible scenario is where you have different PageRows depending on the Category in which case both PageID amd CategoryID become the key for the PageRows table, and as such you would need to have a 3rd column to ensure uniqueness (all 3 columns would make up the primary key).

[RFH Reseller: creativeworks]

truth of it now, i've had to shamfully double up on information and store the same data in the Categories table, and the Pages Unpublished table.

BUT! I want to know for the future, so, I'm going to drill you for more thoughts!

OK... so with your lead this is the data structure

three tables - Categories, Pages, Unpublished Pages

1/ Categories.cat_id - one-to-many - Pages.page_cat
2/ Categories.cat_page - one-to-one - Unpublished.unpublished_page_id
3/ Pages.page_id - one-to-one - Unpublished.unpublished_page_id

We are looking at a CMS system with an area for unpublished pages - information is only stored in the Unpublished Pages if a page is being edited - this means a lot of Null values are returned when left joined (unless we replace null values within the SQL statement, but this didn't help at the time!).

Categories are like folders, and have pages in them. BUT! Categories have a page associated with them, so that when you click one it will open that Page. Because of this association, both the Categories table and the Pages table want to be linked to the Unpublished Pages table so they know if their/the page is currently being edited (and if so, locked).

I'm using the DataSet DataRelation because it's able to loop through a nested repeater and deliver a fantastic tree page of categories and it's assoicated pages.

So, for my DataRelation I have a Parent table made up of an SQL of Categories joined to Unpublished Pages. I then have a Child table made from an SQL of Pages joined to Unpublished Pages. As a nested repeater is looping through the information, they both need access to the Unpublished Pages as they are in two different repeaters.

The Parent table kicks out an error (only when a join statement is used, no join statment all works fine, but obviously I need the join to know about unpublished pages!!) when the myDataSet.Relations.Add(myDataRelation) is performed.

I've written far too much, and puzzled by why and where the third column for uniqueness.

[RFH Reseller: askjim]

If you are embarking on creating a cms, consider do you actually want categories?

[RFH Reseller: creativeworks]

well..... it was something that was already created, i'm just trying to adapt it for my employer - and they preach: adaptation is cheaper (in the short term) than creation.

so i'm kind of stuck with it. but i appreciate what your saying. i think the original concept was to have categories (ie, menu items) and pages (ie, content). So you'd create your categories (your menu) then create content (pages) and link pages into categories, some categories wouldn't even need pages as they have direct http links... make (made!) sense when you look at it!!

[RFH Reseller: nick]

One common way to do it would be to use the top page under each category as the category's lead page - provided you have the ability in the cms to specify the order of pages inside a category. If you also just used a 'published' flag in the page table, then you would be down to two tables with a single vanilla SQL join to pull out all the data you need.

[RFH Reseller: creativeworks]

thanks for all the input.

so... i was walking through some fields of england, with the sun making a surprise appearance, when the solution burst out of the wilderness!

so listing pages inside categories is like working a shop - it's a cms (from a shop background!..?), not a shop! so... perhaps pages shouldn't even be listed, and only categories (now reclassified as 'menu items') created with pages attached! that way, two tables don't need to be related!

ta-da! and my life becomes easier...

[RFH Reseller: Sol]

Some of my thoughts.

Firstly, I think you are looking to hard at turning a particular navigation style straight into a database model. I think you should work by looking at the data more:

You need to consider how published pages and unpublished pages will work before anything else. The reason for this is knowing that if a page is edited and hence becomes unpublished would the previous published version still need to exist? ie. Does the previous content need to be displayed until someone approves the new content. Having this in mind I wold probably create a Pages table to hold basic information about each page and then a PageDetail table which would include rows representing the history and state of the page. I would use an enumeration for page state which would be something like :

public enum PageState
{
    Published,
    UnPublished,
    Obsolete
}

I would also probably create a PageChangeLog table, where I record the datetime, userid, and page changes (ie. so I can see who edited which page).

Once I have this Page structure worked out I would look at the categorization, since categories would be relatively simply and for the most part would just need to drop a PageId into the categories table to allow a category to display a specific page.


I should add that a lot of times I build systems like this I use a Meta style engine which I create in the database. This allows me to actually let the user create, edit and delete fields, thereby allowing them to create custom forms and add entries. There are several applications that use similar techniques, which basically requires a table to hold the field datatypes, another table to hold the field definitions, and another table to hold the field values. This structure can be extended by having default values and input validation.

If you want to discuss this more than let me know as I am building a new Meta Data engine in C# and MS SQL at the moment.