April 27, 2012 at 8:42 am
Before I go and build the schema, I'd like to compare notes with whomever has either seen this done before or has done this before.
I'm looking to create an application that shows a grid with a variable number of columns that are taken from a look-up table. Each column can be given set of values shown in a drop-down control taken from a values look-up table. This is much like Excel.
Anyone have notes to compare on this?
Thanks,
Bill
Bill Nicolich: www.SQLFave.com.
Daily tweet of what's new and interesting: AppendNow
April 27, 2012 at 8:47 am
Bill Nicolich (4/27/2012)
Before I go and build the schema, I'd like to compare notes with whomever has either seen this done before or has done this before.I'm looking to create an application that shows a grid with a variable number of columns that are taken from a look-up table. Each column can be given set of values shown in a drop-down control taken from a values look-up table. This is much like Excel.
Anyone have notes to compare on this?
Thanks,
Bill
And you want to write the updates back to the database?
Are you thinking of a custom C#.NET app?
Out of interest, what is the business driver behind this?
If you haven't even tried to resolve your issue, please don't expect the hard-working volunteers here to waste their time providing links to answers which you could easily have found yourself.
April 27, 2012 at 8:59 am
If you are using .NET the application side of this is trivial. Just bind a datagrid to your datasource and it will generate the columns dynamically.
The sql side of this could be pretty ugly based on the brief description.
_______________________________________________________________
Need help? Help us help you.
Read the article at http://www.sqlservercentral.com/articles/Best+Practices/61537/ for best practices on asking questions.
Need to split a string? Try Jeff Modens splitter http://www.sqlservercentral.com/articles/Tally+Table/72993/.
Cross Tabs and Pivots, Part 1 – Converting Rows to Columns - http://www.sqlservercentral.com/articles/T-SQL/63681/
Cross Tabs and Pivots, Part 2 - Dynamic Cross Tabs - http://www.sqlservercentral.com/articles/Crosstab/65048/
Understanding and Using APPLY (Part 1) - http://www.sqlservercentral.com/articles/APPLY/69953/
Understanding and Using APPLY (Part 2) - http://www.sqlservercentral.com/articles/APPLY/69954/
April 27, 2012 at 9:36 am
Out of interest, what is the business driver behind this?
The idea is an intranet application to compare a large list of projects that we want to refactor to make more standard and similar. For instance, half of the projects use an old stored procedure for step 3 while others use a new one - and so in my grid, I want a column for facet "step 3" and the rows represent the projects and the value would be "Old SP", "New SP" or "Still Older SP", etc.
We do our analysis and set the values to represent what we've got today. That helps us define the size and scope of all the refactoring and standardization work.
Then we go back into the application and start adjusting the values as we do the actual refactoring work and the tool takes on a reporting function to show progress.
Sure - I could use a spreadsheet. But having a web application lets me radiate the data to a lot of people as opposed to passing around a static spreadsheet that few would open.
So, the projects are complicated, multi-stage ETL Projects with lots of steps and lots of facets. The facets are points of comparison that we continue to think of at various levels of abstraction. So, the model doesn't lend itself well to a static number of columns.
I hear that SharePoint does something similar, but uses a table structure with 500 (or a lot) columns with generic names - and then goes about aliasing these names.
This is one approach that I'll look into. Problem is, it's going to complicate the use of a quick UI-builder tool like LightSwitch. Granted, a dynamic table approach also poses problems for UI - but that could possibly be mitigated by views or pivots. This is where I'm looking to draw experience from you all.
What I'm looking for apparently also goes under the name of EAV or Entity Atribute Value model. There's lots of trade-offs with EAV. But I think my requirement is potentially a legitimate need for an EAV-like data model.
There is the problem of constraints and integrity with any EAV model. This I'd look to mitigate by requiring drop-down choices only - so there are no free-form entries to deal with for the "value" part of EAV. I'm only opening up the "attribute" creation to a set of administrators - and this is controlled tightly. The name is text and the possible values come from a few choices of drop-down value sets.
Make sense?
Bill Nicolich: www.SQLFave.com.
Daily tweet of what's new and interesting: AppendNow
April 27, 2012 at 9:43 am
I have worked with a few EAV systems in the past and I can tell you from my experience I would run away from this as fast as possible. It is a nightmare to maintain. Most of your queries will end up being dynamic sql because all the joins will have be dynamic based on values found.
As I said in my first post the UI side of this is really simple because datagrids can handle dynamic table structures with no effort. The sql will make you want to hang yourself.
Just my 2¢.
_______________________________________________________________
Need help? Help us help you.
Read the article at http://www.sqlservercentral.com/articles/Best+Practices/61537/ for best practices on asking questions.
Need to split a string? Try Jeff Modens splitter http://www.sqlservercentral.com/articles/Tally+Table/72993/.
Cross Tabs and Pivots, Part 1 – Converting Rows to Columns - http://www.sqlservercentral.com/articles/T-SQL/63681/
Cross Tabs and Pivots, Part 2 - Dynamic Cross Tabs - http://www.sqlservercentral.com/articles/Crosstab/65048/
Understanding and Using APPLY (Part 1) - http://www.sqlservercentral.com/articles/APPLY/69953/
Understanding and Using APPLY (Part 2) - http://www.sqlservercentral.com/articles/APPLY/69954/
April 27, 2012 at 10:57 am
Sean Lange (4/27/2012)
I have worked with a few EAV systems in the past and I can tell you from my experience I would run away from this as fast as possible. It is a nightmare to maintain. Most of your queries will end up being dynamic sql because all the joins will have be dynamic based on values found.As I said in my first post the UI side of this is really simple because datagrids can handle dynamic table structures with no effort. The sql will make you want to hang yourself.
Just my 2¢.
+1 EAV is an anti-pattern IMHO
This is a classic use-case for data-bound Grids on ASP.NET Web Forms. If you want to get fancy with it you can buy one of the third-party Grid libraries like Telerik Controls for instance. They are mostly configuration based and will save you a ton of time.
There are no special teachers of virtue, because virtue is taught by the whole community.
--Plato
Viewing 6 posts - 1 through 5 (of 5 total)
You must be logged in to reply to this topic. Login to reply