Everything I ever needed to Know about Database Design, I learned from .netTiers

OK - not really. But, it's a good play on words.

I recently evaluated CodeSmith and .netTiers to help me develop the middleware for a project I had been working on. I'd like to talk about some of my experiences, and what I learned.

1. .netTiers doesn't like tbl_.
   
   Originally, the database had been developed with the tbl_table_name convention - and although there's a StripTablePrefixes in .netTiers, it seemed like it was being ignored. I remedied this problem by scripting up the entire database, applying Regular Expression Magic™ to change the table names to PascalCase, and then re-created.


2. Don't name any of your tables anything that might be a type in .netTiers framework - like Entity, for example. Originally, we had thought to have a generic "business entity" table that held customers and system users. This turned out to be a bad idea.


3. Relationships and indexes are awesome. .netTiers will generate any "Get" methods based on any index that return strongly-typed Entities or List collections, depending on if the index is a unique constraint or not. Very cool. Also, you can have GetCollectionFromRelationship() methods that do the same thing.
   
   Plus - you've made a head start on optimizing your database.


4. If your schema has problems, and .netTiers doesn't compile - better to find out now, rather than later. This was a rather rude awakening for me - I thought I had a grasp on some DB design. I now have a much better one.

Overall - highly recommended. It doesn't come cheap (you need a licensed version of CodeSmith), but if you're already using CodeSmith - definitely give this template a shot.

One other caveat - it's open source, which is great. But, the documentation is a work in progress.