What are some recommendations for designing classes that we can enter and retrieve information from a database.

Paul Reed

There are several choices here. Depending on your technology platform my answer varies. If you are in the Microsoft world and NOT using Java, then I would recommend 3 layers of classes. Entity classes will obviously model your business (i.e., Order, Customer). I then create a 1:1 class map to entity classes that I call data translation classes. These classes do nothing but build SQL statements and are the interface to the Entity classes to get data. Because Microsoft changes their data access strategy at least every 6 months (i.e., DAO, ADO, RDO)...I then have one class that actually executes the SQL statement. This class is the interface to the data translation classes. Again, the reason I do this is to combat Microsoft's ever changing data access strategy. If a new strategy emerges, I only have to replace this data access class...the entity and translation classes are none the wiser.

If you are in the Java world on any platform, then we have the stability of JDBC and its prominence in this space. However, I still have a data translation class for every entity class that does both building of SQL and executing the SQL using JDBC. If you look at Sun's Pet Store sample you will see they do the very same thing.

Lastly, you can also look at some of the persistence tools out there...like TOPLink from Web Gain. These are classes that make the whole OO to Relational transition a bit less of a headache...but at a price.