Ok, now that I had defined my goal/target, it was time to start thinking about what design pattern I wanted to use.  I had a few thoughts about I wanted to achieve with the code as follows:

1. I wanted separation between the GUI and the business logic.
2. I only need to support SQL server databases, assuming that if someone is going to use a .NET application that they have access to either SQL Server or MSDE.  I will use stored procedures for the vast majority of queries.
3. I wanted to use a pattern that isn't overly complex, both so that I wouldn't have to spend alot of time building complex objects, and because I do intend to release a source included version someday, and I want that source to be easy to follow.
4. This data model and logic is not very complex, consisting mostly a simple data in/data out.  There is not alot of complex interaction going on between objects beyond a simple primary key pointer reference.

With all this in mind, I decided to use the Table Module Pattern.  The table module pattern is useful in situations where your pattern is shaping up to use the Domain Model combined with the Active Record pattern.  Here's some general info about the Table Module Pattern:

• The data structure is very much in the center of the code.  For a table oriented data structure, this is hands down the most simple and straightforward pattern for data manipulation.  Table Module thrives on .NET recordsets/datasets.
• The objects will basically mimic a SQL table, exposing your CRUD (create, retrieve, update, delete) and encapsulating your business logic/behavior with the data for extra simplicity.  Just about any developer should be able to follow your code easily.
• Table Module exposes a very easy way to use inheritence by overriding the CRUD functions and adding additional properties to support special instances of objects.  (For example, an Employee my have a network login, an ITEmployee may have a second network login with higher priviliges than their normal user account.)  You can also use inhertence to expose commonly used views or other queries.
• Complicated domain logic and polymorphism don't work very well in this pattern, if you are going to make use of these, check out the Domain Model.

So with this pattern, I have basically two classes for each table.  The first class is the single instance of the entity that a table holds (ie: employee).  The other class, denoted by it's plural name (ie: employees) is used to run queries and searches that return collections of the employee object.  I generally choose to bind my own collections to grids, dropdownlists, etc instead of using datareaders and datasets.  This is a personal preference, nothing is stopping you from using them.

My next article will show some code examples of how I set up my table module objects.