SubSonic

Like a lot of people, writing Data Access Layers is not something I really want to spend a lot of time doing. Thankfully there are a vast number of ways that you can improve the process, ORM's and code generators are the two basic methods of assisting in this fashion. Many will be familiar with Hibernate (and its .Net equivalent), possibly the most popular ORM on the market. I quiet like it, but it’s fiddly and config heavy in terms of mapping files. You can Generate these thanks to product like MyGeneration but it often requires tweaking. once its up and running it really is a nice way to deal with persistence for very OO based applications. The other option I have used is Codesmith with it NetTiers templates. Well, up until finding Subsonic. It (so far) has been great.
What I like:

• Can have the benefits of SP’s and ORM


• Can set it to generate the code at build time, so you know every time you run it it is synched with the data base


• If you don’t like the generate on build option you can easily generate the class files for the DAL as well as the create scripts for DB including data, all from with in VS (great for Source control!)


• Author(s) seem to be very proactive in their support and maintenance


• Can be used with in VS as an external tool, meaning one click generation from within VS.


• Minimal additional config settings


• Video showing you how to do it.


• Its pretty fast

I last ran into Subsonic when it was ActionPack. Being happy with Codesmith I didn’t really follow it up. However, as I no longer have a license for CodeSmith, SubSonic being OSS is looking a lot nicer :)
No doubt as I continue to play with it I will find features and bugs, but so far so good.

Common MS SQL “Mistakes” - Part 1

In the last year or so I have moved more in to more consulting and contracting based work which means work working with a larger spread of developers with a an equally wide range of skills and abilities. There have been many generic mistakes I have scene (as well as some great code) but the majority of major mess ups I have seen is by far based around Database and Data Access. This is probably one of the major reasons why so many people are moving to ORM’s, because it removes the responsibility of creating the DA layer and the potential pitfalls that can come with it. This is fine, but it also means that a lot of people are becoming ignorant of the details of these layers. Generally these layers are the very foundations on which the application or applications are built on, I believe the blasé nature in which the technologies are approached is the reason why some run in to such major issues mid project. This late in the scene it is very hard to go back and make schema changes, especially if multiple teams are working off this schema.

I have decided to compile a list of issues that I have run in to in the last year, that I believe are potential short comings of the data related architecture. Often these points by themselves are not huge issues, but when I see one I usually can find another just around the corner.

Hopefully these come as some help to you and your design. Please remember this is coming from a developers perspective, a .Net and MS SQL developer at that, and I, in no way, proclaim myself to be a guru in any regard, however I feel these are basic points should be a general rule of thumb.

Database structure
Normalisation
Normalisation to the 3rd normal is standard. If you don’t know what 3rd normal is then it is a good hint that maybe you may not be the best one to be designing the DB. Sure there are reasons to move away from this rule but it should be just that, a reason, not bad design. Perhaps you may even want to document these reasons so it is clear why.
Nullable Columns
There will always be a need for nullable columns, however when tables start to have a growing number of nullable columns, you should start questioning why. Perhaps these can be moved to a separate table.

Property Tables
If a table is starting to have a lot of columns and many of them are null then you may want to implement a Property table and a PropertyType table. This is relatively clean and easy to set up and if done properly, is a fast way of getting a potentially large albeit often unknown number of properties, and a lot cleaner.

Incorrect Use of the Primary Key
Recently I saw a table that had a multi column primary key over five columns. After spanning more than 2 columns alarm bells go off in my head. In this case this was just bad design. The table only needed one Primary Key with one index on another column. Often this set up I find is good especially when there are unique values such as “Product Codes” that could be the PK but are in a format that does not suit performance (e.g. varchar(1000)). Using a smaller data type for the primary key improves performance. I also generally prefer to use [int] IDENTITY(1,1) as the default primary key for tables. Using large columns as PK’s in my mind is a bit of a no-no. Some times GUID’s are used if there is movement across to other databases. If this is happening then there is probably a properly qualified DBA involved leaving us developers to move back up a layer J

Incorrect Use of the Foreign Key
I have actually seen foreign keys been “used” without the being specifically being set, meaning referential integrity can not be there! Why this was done, god only knows, as it was not documented.

Not Appling Basic Coding Standards
Commenting code is a given. I can’t stand working on projects where Classes, Methods, Properties or Parameter are not obviously named and well commented. Most developers share these views, especially one who have come into projects late. For some reason these principles are ignored when it comes to Database development. Columns get named things like “DscInd” and have no comments associated to them. As far as I am aware there are no real penalties for naming you columns something that is easily understandable. The benefits are however large. People now understanding what is actually being stored! These basic procedures also help spot flaws in any business logic. If the intention of a piece of code is explicitly commented then, if for some reason it is wrong, alarm bells go off a little easier.
Naming conventions are typically applied in managed code but again are often missed in DB Development. I could go on, but you get the general idea. Treat DB dev in the same way you would when it comes to basic procedure such as naming, commenting and using the correct types.

False Economies

As I get older and more experienced in the IT game some basic truths continue to rear theirs head... One I am currently running into is the idea of not doing some standard practice thing because it will “take to long” or “cost too much money” to do.
I have no problem with not doing some because it is too expensive, however when you are building a business critical application for a publicly traded, Tier 1 company, you had better make sure that those things that you are not going to implement don’t bite you in the ass later.
Documentation and Unit testing should not be negotiable. Especially around business object and other lower level layers. If these layers are not working correctly, how can the layer dependent on these be reliable?
One thing I see more often than I like is not putting enough emphasis on DB design. If you are using a very basic OO model and the DB is literally just a persistence layer then I am all for ORMs. If you have complex set based structures, hire a good DB geek that can architect a proper data persistence approach in terms of DB design and helping with Data Access. Often a developer is not the right person to be doing this.
(I think I have already emphasized this enough on this blog)
Another issue is allowing for future changes. Udi Dahan proposed a design principle which I agree with and employee:
For any classes A and B in a system where both A and B have behavior, A should interact with B through an interface.
The interface which separates two concrete classes should be packaged separately from either of those classes
By employing the strategy above, you allow for the ability to have tests to isolate each “layer”. You also then allow for changing of dependency. As long as the new class implements the original interface, how the business logic occurs is irrelevant. This came up specifically this week with regards to the data access layer. Using this strategy we could investigate different options in terms of the DA, such as using the current implementation, a modified version of the current, an ORM or a Generated layer based on the DB schema. If this had been done earlier it would have take lesser than a minute for each class. Not really a huge time loss there.

I dont take all of this as bad things, it is learning from mistakes, whether they be yours or others. The point is we need to learn from them so we can find ways to avoid or fix future encounters.

Currently Playing with Castle stuff and GhostDoc

New (to me) stuff i have been playing with:

• Ghost Doc
• Castle Project Active Record
• Active Writer for Castle AR

As well as the CAB/ SCSF stuff ihave been working on at work, I have been playing with AR & AW at home. They dont really go with my normal views on enterprise level data access, however in my vague attampts to broarden my horizons I have started to dable in their worlds.

Benefits:

• Easy to read code. As all the DA is under the hood its code creates the illusion(?) of clearer code.
• Easy to create Business entities and DA, just drag and drop.
• Seperation of Business entities from UI

All of this can be done with Code smith, however I thought I would look at this as it is another "string to my bow" so to say. I am currently building a smalll web app from it to see how well it work under light load.

I can see these tools beign great for very basic web sites. Get someone who understands the data requirements to build a DB and AR classes then just palm it off to the UI guys as the model aspect of an MVC set up. Would be great from 3 tier app with basic data structure.

Ghost doc... well it just helps with documenting your code. Its very simple but anything that helps with the mundane is a good thing in my book.

DB Normalisation and other DB points of view

Over the last couple of months I have heard the commments "its a very normalised database" or various implications that database X is "too" normalised. i often found this odd.
Why?

1. By looking at a DB schema how do you know how well indexed the DB is?
2. How do you know how many rows are in it?
3. How do you know about it performance

I have always been of the opinion that you

1. Normalise first, create a beautiful piece of art that is a nicely and appropriately normailised database.
2. Add indexes.
3. Write your sored procs.
4. Check the performance on test data.
5. Denormalise if nessecary, however if there were issues I would go over everything with a fine tooth comb first to ensure keys, indexes, constraints, triggers are all in place where appropriate, working as intended and not adding unessecary overhead. Then I would start denormalising.
   

Yes i am a fan of stored procs, especially for anything that actaully is DB intensive. As Gumble also points out (frequently) it adheres to a basic concept we use daily, encapsulation. Normal Objects and Layers/Tiers cant see into the next object/layer/tier, why should this be broken at the data access layer. Using SP's also (IMO) aids in security and data control (well it can, anyone can butcher code) ;). The OO coders should not have to know the underlying data structure, they know what they want, they just need a means to get it. The Dba may be adding a whole bunch of stuff that the OO boys dont need to about, inactive flags, triggers, loggin, other extra columns, how the data is retireved etc etc... also this mean performance is left in the DB world and can be easily tested with out any manged code interfering.
One thing i do prefer is isolation of tests, i dont want to not be able to test individual units. A stored proc is a unit, a BusObj --> Repository --> DA--> db --> DA --> Repository --> BusObj is not a very succinct unit. If i want to test that vertical i still can however i want to be able to break it down too.

Then there is the point of ORM's; I'm all for them, anything that gets rid of boring DA code, sweet! But sometime you have some complex resultsets and DB call that do not suit your run of the mill ORM's. I do believe for basic database and when fast turnover of code is of higher importances, then something like NHibernate (perhaps with AR) can be handy... but i think it needs to be reviewed on a case by case basis, unlike some of my peers who believe NHibernate is the silver for all solutions...

no doubt more will come...