I often talk about "database layer" versus "presentation layer", but even the within just the database layer it is important to understand that how the data is physically stored does not always have to correlate with how the database returns results.  There is still room for your SQL statements to use Views, Stored Procedures and User-Defined Functions to occasionally transform your well designed, clean normalized data into something that is easier for reporting tools to consume – especially if those tools are used by clients who don't have strong skills.

We often see bad designs submitted at SQLTeam.com with the justification of that design being "the client wanted it that way! They insisted that the database should store things in all in one XML column" and so on.  While some clients can indeed be insistent with these requirements, remember that you can often have the best of both worlds – a clean, normalized design AND data that "looks" the way your clients want – if you take the time to demonstrate to them how the database can transform your data so that it looks the way they expect and the way they feel comfortable working with.

Some examples ….

"I want to see all categories listed in 1 column in the Product table.  A related table is too much trouble and it's hard to do the joins.  All of the categories that the Product belongs to should be stored as CSVs like this: 'shirts,clothing,summer'."

This is a common one, where a client or junior programmer complains that "joins are a lot of work!" or "confusing" and that they'd like to avoid them.  It's really ironic when you think about it (this design is much more difficult to work with rather than using simple JOINS), but I see this time and time again. 

So, how do we handle this situation?

Chances are, explaining the benefits of a normalized database to your client is not going to work; you need to demonstrate to them that even when storing the data properly, we can still create a View which returns those exact results, making them happy. Using a simple UDF or other techniques to do this will result in the best of both worlds:  Your client is pleased that things "look" they way they want, and you have a clean database design.   Don't just give up and give them what they want, and don't lecture them with technical talk about normalization that they won't respond do, just demonstrate to them how the View works and how it is instantly updated and flexible and fast and they will be pleased.

Here's another common one:

"We want to store account balances in a table with columns for Jan, Feb, Mar, etc.  All of our reports format the data this way, and it will make our reporting much easier."

This is another common request for an unnormalized database to make things "easier". If only they knew how much harder this would be to have things set up this way!   Again, we can try to explain to them the benefits of a reporting tool or even an Excel pivot table, and how these results are easily returned from a standard, flexible transactions table, but explaining and "talking down" to them won't help; we need to demonstrate to them that a simple View can once again do the trick.   I usually strongly recommend to cross-tab data at your presentation layer and not at your database, but since this is static (it will always be 12 monthly columns), it is fairly easy and efficient to do in T-SQL.   Just create a View that presents the information this way, while storing the data in a standard normalized format, and everyone wins.

In addition, we have the classics:

• "dates that must be stored like 04-Jan-07"
  
• "phone numbers must be stored like 617.123.456" (no trendy web site uses (xxx) xxx-xxx any more!!)
  
• "The customer names must be stored in all capitals because our invoice displays them that way"
• "We'll want reports that show Budgets and Actuals and also calculates variances, so there must be a Budget column and an Actual column in the tables."
  

• Composite Primary Keys
• The problem isn't the poor database model; It's that external code is bound to the schema
• Distinguishing data from code
• Data Types -- The Easiest Part of Database Design
• SQL Data Modeling: Entities versus Attributes
• Custom Auto-Generated Sequences in SQL Server
• Data belongs in your tables -- not in your code
• Delete Duplicates And Resolve Foreign Key References in SQL