Flattern child/parent data with unknown number of columns

Question

I'm struggling to find the best way to store and represent the data I have in SQL (MySQL DB) and C# windows form.

My data when mapped to classes which looks like this;

public class Parent
{
        public string UniqueID { get; set; }   //Key
        public DateTime LoadTime { get; set; }
        public string Reference { get; set; }
        private List<Child> Elements { get; set; }
}

public class Child
{
        public int MemberCode { get; set; }   //Composite key
        public int ElementCode { get; set; }  //Composite key
        public Object Data { get; set; }
}

My data is very dynamic. So a parent record can have any number of child records.

In the child record then the MemberCode and ElementCode are actually foreign keys to other tables/classes, which when a look-up is performed gives me details of what the data actually is. For example

MemberCode = 1 & ElementCode = 1 means data is a Date
MemberCode = 1 & ElementCode = 3 means data is a telephone number
MemberCode = 2 & ElementCode = 11 means data is a Product Code
MemberCode = 2 & ElementCode = 12 means data is a Service Code
etc

These effectively combine to indicate what the column name is, and these are always the same (so MemberCode = 1 & ElementCode = 1 will always be a Date no matter which child object it is associated with). At the moment these are references/lookups but I could also put the data in a variable in the class as that might make it easier. Then it would be more like a Key Value Pair.

At the moment in my DB I have these stored as two tables, with the child record also containing the UniqueID from the parent. But I'm, not sure that this is the best way as I will explain.

My tables are created as such

CREATE TABLE `PARENT` (
        `ID` INT(11) NOT NULL AUTO_INCREMENT,
        `LOADTIME` TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP,
        `REFERENCE` VARCHAR(100) NOT NULL,
        PRIMARY KEY (`ID`)
    )

CREATE TABLE `CHILD` (
    `ID` INT(11) NOT NULL,
    `MEMBER_CODE` INT(11) NOT NULL,
    `ELEMENT_CODE` INT(11) NOT NULL,
    `DATA` VARCHAR(4000) NULL DEFAULT NULL,
    PRIMARY KEY (`ID`, `MEMBER_CODE`, `ELEMENT_CODE`),
    CONSTRAINT `fk_ID` FOREIGN KEY (`ID`) REFERENCES `Parent` (`ID`)
)

Now what I want to do is to flatten out this data so that I can display a single parent record with all child records as a single row. I ideally want to display it in an ObjectListView (http://objectlistview.sourceforge.net/cs/index.html) but can consider datagrid if it makes life easier.

Because my data is dynamic, then I'm struggling to flatten this out and if I select 10 parent records then each can have different number of child elements, and each can have different MemberCodes and ElementCode, which means that they are effectively different columns.

So my data could look like the following (but on a larger scale);

But because of the dynamic nature of the data, then I struggling to do this. Either in SQL or in Objects in my code. Maybe there is even another way to store my data which would suit it better.

Answer 1

After many many days working on this then I have managed to resolve this issue myself. What I done was the following;

In my original child class then the MemberCode and ElementCode make a unique key that basically stated what the column name was. So I took this a step further and added a "Column_Name" so that I had

public class Child
{
        public int MemberCode { get; set; }   //Composite key
        public int ElementCode { get; set; }  //Composite key
        public string Column_Name { get; set; }  //Unique.  Alternative Key
        public Object Data { get; set; }
}

This was obviously reflected in my database table as well.

My SQL to extract the data then looked like this;

select  p.UniqueID, p.LoadTime, p.reference, c.MemberCode, c.ElementCode , c.column_name, c.Data 
from parent as p, child as c
where p.UniqueID = c.UniqueID 
//aditional filter criteria
ORDER BY p.UniqueID, MemberCode, ElementCode

ordering by the UniqueID first is critical to ensure the records are in the right order for later processing.

The I would use a dynamic and a ExpandoObject() to store the data.

So I iterate over the result to the convert the sql result into this structure as follows;

List<dynamic> allRecords = new List<dynamic>();  //A list of all my records
List<dynamic> singleRecord = null;  //A list representing just a single record

bool first = true;   //Needed for execution of the first iteration only
int lastId = 0;      //id of the last unique record

foreach (DataRow row in args.GetDataSet.Tables[0].Rows)
{
    int newID = Convert.ToInt32(row["UniqueID"]);  //get the current record unique id   

    if (newID != lastId)  //If new record then get header/parent information
    {
        if (!first)
            allRecords.Add(singleRecord);   //store the last record
        else
            first = false;

        //new object
        singleRecord = new List<dynamic>();

        //get parent information and store it
        dynamic head = new ExpandoObject();
        head.Column_name = "UniqueID";
        head.UDS_Data = row["UniqueID"].ToString();
        singleRecord.Add(head);

        head = new ExpandoObject();
        head.Column_name = "LoadTime";
        head.UDS_Data = row["LoadTime"].ToString();
        singleRecord.Add(head);

        head = new ExpandoObject();
        head.Column_name = "reference";
        head.UDS_Data = row["reference"].ToString();
        singleRecord.Add(head);                    
    }

    //get child information and store it.  One row at a time
    dynamic record = new ExpandoObject();

    record.Column_name = row["column_name"].ToString();
    record.UDS_Data = row["data"].ToString();
    singleRecord.Add(record);

    lastId = newID;   //store the last id
}
allRecords.Add(singleRecord);  //stores the last complete record

Then I have my information stored dynamically in the flat manner that I required.

Now the next problem was the ObjectListView I wanted to use. This could not accept such dynamic types.

So I had the information stored within my code as I wanted, but I could still not display it as was required.

The solution was that was to use a variant of the ObjectListView known as the DataListView. This is effectively the same control but can be data bound. Another alternative would also be to use a DatagridView, but I wanted to stick to the ObjectListView for other reasons.

So now I had to convert my dynamic data into a Datasource. This I done as follows;

DataTable dt = new DataTable();            
foreach (dynamic record in allRecords)
{
    DataRow dr = dt.NewRow();
    foreach (dynamic item in record)
    {
        var prop = (IDictionary<String, Object>)item;
        if (!dt.Columns.Contains(prop["Column_name"].ToString()))
        {
            dt.Columns.Add(new DataColumn(prop["Column_name"].ToString()));
        }

        dr[prop["Column_name"].ToString()] = prop["UDS_Data"];
    }
    dt.Rows.Add(dr);
}

Then I simply assign my datasource to the DataListView, generate the columns, and hey presto I now have my dynamic data extracted, flattened and displayed how I require.