What Happened to Database Design?

Database design seems to take a much lower priority on a system architecture than it used to, and I can understand why…

These are my rather generalised stakeholder categorisations based on personal industry experience;

Customers are not interested in the boring table schema demo or SQL scripts in Agile projects, and who can blame them, why would they be when there is a flashy UI to look at when it come round to sprint reviews?

Project managers are not interested as it is not a demonstrable feature. How far does the statement ‘ we have developed a fully normalised and performance tuned database for you’ really go with a customer. The customer generally wants a fully functional system that looks good that they can show their manager. Main interest factors are cost and time overruns.

UI developers refer to it as a repository and don’t care what it looks like, as far as they are concerned it can be a stack of XML files. In actual fact, these days they only care about the service layer as the database is abstracted from their layer.

Service layer developers are generally happy if they don’t have to join one table to another table. So completely de-normalised tables can be quite common when developed by WCF developers. In fact if tables are completely de-normalised they rarely need to address joining tables or addressing foreign keys.

The only parties actually interested in a good database design are the database developer as they realise that they can write SQL that really performs, and the DBA as they can see the benefits of a scaleable system that is unlikely to bloat over time.

One thing we’ve noticed is that with all the layers involved in even medium-sized systems these days, most developers tend to specialise in either one or two of the layers.  It’s tricky to find a SQL developer/DBA with good UI knowledge and vice versa. Most people pick the closest tier to their speciality and learn a little bit more about how it interacts with their own tier. This makes them more marketable and allows them to fully specialise with their own technology.

I’ve interviewed a lot of candidates over the last decade and there is a definite shift away from the data layer. I can see why…

• Good database architecture can be complex.
• Experience counts when normalising a database correctly.
• Understanding set theory is a must for good SQL.
• SQL itself is a fairly large language these days.
• UI work is much, much sexier !

We aren’t the only ones to have noticed this shift. Software Tools are starting to come out to address the lack of knowledge. Entity framework now has the option to ‘code first’ and generate a database layer from the service layer.

Whilst it’s great that there are tools and support out there for the lonely UI developer struggling to create a demo, and has little database knowledge. I get very disappointed about the number of these prototype systems which make it into production and are left for the DBA to sort out when performance starts to suck.  It brings back memories of little MS Access database systems that got upgraded to SQL Server and shoved into a full business use. They generally grew and grew, degraded and degraded to the point of complete failure.

So what’s the answer?  I wish I knew.

There is an excellent and diverse SQL Community out there willing to pass on knowledge; PASS, SQLBits, SQLServerCentral, SQLServerClub,  and I still only look for recruits that have some decent database skills in their portfolio. I firmly believe that a database is the most important foundation in a system architecture.

But perhaps part of the problem is that creating a good data layer is that it is time consuming and can be costly when done correctly. If we can all find ways of building standards and perhaps abstracting the table design changes using template stored procedures then we may at least get the Service Layer guys more interested!

Until then spare a thought for where the application is going to end up. Don’t throw your ‘code first’ database at your Production DBA and run, try spending some time up front talking to them and actually try to design a database first. You will be much more marketable in future, although probably a lot less interesting to the rest of the project team…!

Advertisement

My Experience using Entity Framework for WCF/SQL Server Layered Application

This post is a little late coming , as I first starting using Entity Framework 4 when it was on CTP 1 at the start of the year. I used it up to CTP2 in March/April 2010.  Forgive me if there have been changes since and please do visit the MSDN site here and do read up on it (I read Julia Lermans book and her blog here)

First let me start by saying EF4 is a great product. It has the potential to not only generate code and classes for communication with database but also now has the ability to generate the database schema based on your code. This is great for developers who are not confident with databases or T-SQL, or do not have a lot of time to create a working system.

My background is primarily as a sql dba and developer, perhaps this is why I had a few problems with EF.

I have always believed that a good database layer is the best starting point for a good application design.  I realise there are times when this is not always possible …

However, I like using stored procedure when writing real applications.

1. They have the ability to protect the application from schema changes making it easier to change code without redeploying your front end.
2. They can protect the database from unscrupulous client developers who like writing badly performing inline sql (you know who you are …!)
3. They generally have the capability to perform well (of course they have to also be written well!)
4. They can be used to protect from direct table access and permissions can be applied both externally and programatically within the procedure.
5. They can enforce atomic updates to your data across multiple tables.
6. Most importantly for me.  They can remove a lot of complexity from the application and can allow you to create a normalised data schema, but protect the developers from the complexity of multi-tabled updates.

So why is this a problem I hear your ask.  Entity framework can be used with stored procedures can’t they?

Well yes and no …

Of course you can map your insert/update/delete portion of the entity to your stored procedures.  But they have to be written in a certain way and they must return key values in a select statement (no output parameters allowed here).

No out of the box support for the Get data process being a stored procedure, this needs to be a table or a view, unless you like editing the xml files.  I found this side of the entity framework to be incredibly painful, in terms of setting an exact entity that could use a view for the Get and stored procedures for the rest.  Let alone when you want to add a column and have to start all over again.

However what I did find useful in the entity framework was the ability to knock up a working demo system in an incredibly short amount of time. I had a WPF/WCF/SQL 2008 system working with a single table process in a matter of days.  But what I found was that I couldn’t take it any further without wasting a lot of time remapping schema changes to the entities.  I also decided to take a look at some of the generated SQL statements from the entity sql and linq sql. It wasn’t the worst I’ve seen but boy was it verbose !

My conclusion is … use the EF if you want create quick proof of concepts or prototypes but when it comes to writing real database applications stick to the stored procedures and real T-SQL, your DBA will thank you for it when he has to take it over and support it.  Microsoft seem to be approaching the whole code generation tools from the front end.  What I really need is the reverse, I want a really good database design, with standard stored procedures for data updates and tool to help me connect it to a front end without me writing all the code!

Please try it for yourself and do read the material at the start of the post  – make up your own mind!