[SQL] User Defined columns for a database table

Our project has got a particular requirement. I understand it but I don't think it can be easy to implement.

Description: There is a table in database with 500 columns. Now, the end users (power users or admins) may want to define derived columns. A derived column is a column based on other columns. The derived column can technically be a actual physical column or just a logical column which we generate at runtime by dynamic SQL query.

Example: say there is a column called 'WageOnDay' and there is another called 'WageDate'. Now end user may want to define a derived column called 'WageForPastMonth' or 'WageForPastYear' or just simply a derived column with 'WageDate'+2000. The user may want to define any number of such additional derived fields and would want to define them via a UI. The user would also want to specify a custom display name for these derived columns. Example: user may want to call the derived column for 'WageDate'+2000 as 'WageWithBonus' or 'WageAfterAdjustment'. The user may change the display name of the derived column and its definition (current definition 'WageDate'+2000 to 'WageDate'+4000) from time to time.. The derived column definition could also be an aggregation like sum over the wages for a date range etc.

Now - we thought over this from technical aspect. And there does not seem to be a way to implement this. The user may define an aggregation for derived column or a simple expression etc.. we could go on about actually modifying the table definition and adding these additional fields as per the users definition for derived columns..

Other approach would be to store these derived column definitions in another configuration table and use those to to generate a dynamic SQL which will have to generate the derived columns according to the definition.

Currently, we think implementing this would require a huge effort. We would need to also create a semantic language which will store the derived column definition and would have to implement a parser which generates part of the SQL based on the user definition.

I know there are reporting tools like QlikView, CrystalReports and JasperReport which have the functionality I described.

But, can we implement it in our project ? If not, then is integrating with any of the other tools an option.

Please share your thoughts and suggestions. Let us know if such a requirement has been addressed and a high level technical approach.

Continue reading...