Dynamically Creating GridView

Question

I want to create a GridView that displays records for PDF Files. These records can have meta data attached that is customizeable by the user, so they can create their own columns and enter their own information in there. I then want it to be displayed in a GridView, so they can order each column and the column order if the column order is -1 it will not display in the GridView.

For example there is a static table

DocumentsTable:
ID int
PDF_Folder varchar
UserID int

Then there is another table that users can create their own columns for

MetaDataColumns:
ID int
userid int foreign key
KeyName varchar
Order int

and the table to hold the values

MetaDataValues:
ID int
UserID int foreign key
DocumentID int foreign key
MetaDataID int foreign key
value varchar(100)

Now the problem is I need to get the columns from the MetaDataColumn to create the GridView and then populate it with the values in MetaDataValue table. My original plan is to have a function that dynamically creates the GridView and adds columns to it, however I am stuck on how to use the values in the MetaDataValue as the columns. Alternatively I could just have the GridView AutoGenerate columns but I need to customize the SQL to display the custom data. I'm a bit stuck on how to even approach this.

One approach I have come up with is this pseudo code:

private DataTable CreateColumns()
{
   var columns =  select * from MetaDataColumns 
                  where userid = UserId;

   DataTable dt = new DataTable();

   foreach (column in columns)
   {
       dt.Columns.Add(new DataColumn(column[keyName], typeof(string));  //assumes all string
   }

 return dt
}

private void PopulateDG(DataGrid dg)
{
    var documents = select * from DocumentsTable
                     where userid=UserId;

    foreach (document in documents)
    {            
        var columnValues = select * from MetaDatavalues 
                           documentID == document.id;

        DataRow dr = dg.NewRow();
        dr[columnValues.KeyName] = columnValues.value;

    }

 }

 private void LoadGV()
 {  
   DataGrid dg = CreateColumns();
   PopulateDG(dg);
   GridView.datasource = dg;
   GridView.DataBind();
  }

One of the things I dont like about this design is for every row in the documents table it creates another query. Im not sure if this is a problem with SQL?

Answer 1

Your problem is mainly due to the design of the database. You have to dynamically add columns because you've translated what would be a column (in 3NF) to a row in your tables. Obviously, this is because you allow users to add their own columns - my mind shudders, but that's the way the app works :-).

Due to the structure of MetaDataColumns I'm going to assume that a user has the ability to define a set of column names that they can then choose to apply to an individual document as they wish.

I think the problem is that in trying to normalise everything properly, in a completely de-normalised database, you've managed to cause yourself a lot of hassle. My solution would be to denormalise your table MetaDataValues. You don't mention what RDBMS you're using but MySQL has a hard limit of 4096 columns or 65k bytes. The limit in Oracle is 1000 and 1024 in SQL Server.

If you change the structure of MetaDataValues to the following you should be able to fit at least 332 sets of information in there. This would be separately unique on UserID, DocumentID so you could, theoretically, remove the surrogate key ID.

MetaDataValues:
ID int
UserID int foreign key
DocumentID int foreign key
KeyName1 varchar
Order1 int
Value1 varchar(100) 
...
KeyNameN varchar
OrderN int
ValueN varchar(100)

Of course, this does set an upper-limit on the number of columns that you'll be able to allow an individual user to create to 332; but, it's normal to limit the abilities of users to go insane and anyone who can think of 332 separate bits of metadata to store on a single PDF deserves to be limited somehow.

If you do have particularly information obsessed users you can always declare a second table with the same structure and keep on filling it in.

Doing this would mean that MetaDataColumns would not be used for anything but displaying the users options to them. You would have to update MetaDataValues each time a change was made and ensuring that you didn't over-write already extant information may be a little bit of a pain. I would suspect you'd have to do something like selecting the record before you update it, iterating through KeyName1 .. KeyNameN and filling in the first one that doesn't have any data in it. Alternatively you could just write an absolutely horrible SQL query. Either way, this would become the "choke point".

The other option would be to add an addition column to MetaDataColumns, which indicated which N the column related to but this restricts a user to 332 columns absolutely rather than 332 per document.

However, your selection from the database is now insanely easy:

select d.*, m.*
  from DocumentsTable d
  join MetaDataValues m
    on d.ID = m.DocumentID
   and d.UserID = m.UserID
 where d.UserId = ?

There's no need to try to iterate through tables dynamically generating 1,000 column select statements. All the information is right there and easily accessible for you.

At the end of the day the "correct" answer to your question depends on where you want to spend the time. Do you want it to take half a second longer to create or update a document or half a second (probably more) to select the information on that document.

Personally, I think users understand that creating something takes time but there's nothing more annoying than having to wait ages to see something appear.

There is another, social, rather than database solution. Don't allow your users to create their own columns. Pick the most common pieces of metadata that your users want and create them properly in a normalised form in the database. You'd be able to create columns with the correct data-type (which will save you a lot of hassle in the long run) and have a much easier time of it. I doubt you'll be lucky enough to have that happen; but it's worth bearing in mind.