For Database: Module, Class or ?

Given a database application, I guess I get that each table should be in its own class and all the basic CRUD stuff should be a method, property of that class - right? What about the database itself? i.e. the open, connection check, etc. Should that be in a class or in a module or none of the above? This would be both for web apps and desktop apps. I assume the principle is the same.

Some developers make a class for each table. I find this very unpractical. Making a class for the connection makes more sense.

The class creation can be automated, but the main issue IMO is that in any non trivial normalized database there will be a lot of cross table relations that need to be dealt with. That can make the one class for each table model turn out to be less useful than one might expect. Because of that i only use it for some tables and tend to use DB views a good bit.

Also it can make changing DB structure dmore work on the client side when only changing SQL statements might otherwise have been needed!

  • Karen

Peter if you buy one thing for Xojo and your into database apps as a business, you need Argen 3

I’ve just completed a complete database system To run a magazine company with 30 tables.
The design and model took 2 weeks using Argen, the crud took 2 hours and I had a complete object oriented model, of one class per table.
Another 2 hours and I had the UI working draft COMPLETE.
The app took another 2 months to complete and I got to spend all that time on the hard stuff, rules tricks etc.
I highly recommend it if you want to be dollar and time effective at database web or desktop apps

Regards James

Generally speaking applications tend to use one of two patterns when working with a database:

The active record pattern is best suited to small databases, say around 50 to 60 tables and where most operations are performed on a table at a time.

I favor the data mapper because it results in a looser coupling between the database and the business model thus making it easier to test and handle more complex situations through using the Unit Of Work pattern.

So for me all database logic ends in some kind of repository class. The only thing I might add to an entity class is a constructor that takes a RecordSet as a parameter and loads the instance values from there.

Thanks all. Interesting views expressed here. Thanks James, I was eyeing Argen. But I’m wondering, given what Beatrix & Karen are saying if classes are the way to go?

something to consider is prepared statements. Without prepared statements, a general class is very straight forward, with prepared statements you will have to add a bit more logic to make it all work.

I think any time you can use the Dot notation with objects and have the compiler help you with obvious errors is good. I’m biased with ARGen, obviously, but it’s worked will for our consulting clients (which is why we originally developed it). And ARGen/ActiveRecord automatically uses PrepareStatements and Transactions for Insert/Update/Delete operations and adds in events that you can piggy-back on (Before/After Create/Update/Save/Delete) that are all in the same transaction.

ARGen isn’t perfect for every situation. We’ve used it in apps that have several hundred tables with no issues however.