Database Column Names != Report Headings
Always remember that the column names returned in a result set do not have to be the same as what you eventually output at your presentation layer.
For example, suppose you have stored procedure that accepts a @CurrentYear parameter and returns a sales variance between the current year and the previous year for each customer. I often see programmers struggling with writing dynamic SQL to produce output like this:
CustomerID 2008 Total 2007 Total Variance
---------- ---------- ---------- --------
ABC $100 $50 $50
DEF $200 $250 -$50
---------- ---------- ---------- --------
ABC $100 $50 $50
DEF $200 $250 -$50
That is, the names of the columns vary based on the data; that is not a good way to return data from your database! A much better result set to return is simply this:
CustomerID CurrentYear PrevYear Variance
---------- ---------- --------- --------
ABC $100 $50 $50
DEF $200 $250 -$50
---------- ---------- --------- --------
ABC $100 $50 $50
DEF $200 $250 -$50
Notice that with that set of columns, no dynamic SQL is needed, and the column names returned are always constant regardless of the value of the @CurrYear parameter.
As mentioned, the fact that your data set has columns labelled "CurrentYear" and "PrevYear" does not mean that you cannot re-label them any way that you like on your report or web page.
If your client code called the stored procedure and provided a @CurrentYear parameter, then it knows exactly what "CurrentYear" and "PrevYear" represent, and you can easily label the columns in the final result exactly as needed with simple formulas or a few lines of code.
Remember that in the world of relational database programming, table names and column names should be constant -- only the data itself should change. Focus on returning consistently structured data from your database, and let your client applications handle the labeling of columns to make them look nice.
Legacy Comments
|
Jeff
2008-08-07 |
re: Database Column Names != Report Headings Too true. Too often I see database schemas designed to support the report. The data should be understandable without having a report to view, and it should be up to the developer/report designer to make the data make sense to their audience. |
|
csundaresh
2008-08-12 |
re: Database Column Names != Report Headings This post is right on the money! The presentation layer should be responsible to create mappings for visualization. |
|
Philip
2008-09-18 |
re: Database Column Names != Report Headings I'd love to show this to my boss. Jeff said it correctly " Too often I see database schemas designed to support the report". What a mess |