Looking for database program design advice from experienced programmers

[Nighthawk]

Hi.
I'm a recently graduated CSci student and I'm currently working on a mysql backed java program for property management and I could really use the perspective of an experienced programmer or two. Anyone interested in having an ongoing dialog about database program design?

A little about the problem:

My initial plan was to use an MVC structure extended by a database interface & model separate from the business logic that could essentially be swapped out if a different database or (heavens forbid) an XML based storage solution was chosen.

As I started the design workup I realized that this is almost a pure CRUD application. There is very little business logic involved. I did a classic pass through the software's description, separated nouns and verbs to generate classes etc. but quickly began to thing that there was very little reason to use the classes I'd defined (property, unit, tenant, etc) if I was using a SQL database.

I was going to be pulling data from the database to create objects, then for the most part simply ripping the objects apart to populate JTables for display. Such an approach would also potentially require maintaining alot of RAM based data structures.

If, on the other hand I populate Jtables directly from SQL record sets I could leverage the power of the db engine to handle searches, maintain indexes, and most of the storage would be hard drive rather than RAM. In the process I'd loose the ability to swap out the storage module for say, an XML solution (or the next best thing in data storage, what ever that will be).

My hope is to follow "good design practices" and "industry standards" as much as possible, and I would eventually like to open source the project, so I want the code to make sense to others. Unfortunately, I don't have any industry experience to base decisions on.

Any advice from the programming community?

[travishein]

not sure how this relates to Java (Swing) clients, but when I build web based clients, frameworks such as Google Web Toolkit and the frameworks built on them (Smart GWT, Vaadin), have facilities like a scroll listener, where it knows the position and the visible portion of a (table, select list, etc). So initially it queries the database for the first 50 rows or matching search results. To populate the display. As the user scrolls down the framework invokes a background request using their RPC (you could use something light weight such as JSON if you have a server, or JDBC type queries, which go query the next 50 rows or so and shoves them into the JTable list. This requires creating a custom JTableModel. Over the operation of the application, yea, the table in the user's memory will approach all records in the the database, if the query is to fetch all. But the user experience is improved because the initial loading of the jtable contents does not have to wait for the many seconds it could take to fetch all rows from the database. it is efficient to fetch small chunks as needed, and likely reduces the transient load on the database too right.

[Tolls]

From experience, don't have your GUI use DB stuff directly.
At some point in time you'll come up with an idea that involves manipulating this data and then you'll have to carve the whole lot out.

Your model should not require you to hold large amounts of data in ram. Why should you?
You only need to hold what's being displayed. This is where paging rather than scrolling makes life easier, though you can still buffer a couple of screenfuls with a scrollable JTable instead of storing the whole lot (as already mentioned).

As for searching and indexes and the rest...well, you still have access to the db. Everyone else manages to do this, so I don't see where you're seeing a problem. It's done all the time on web-apps, let alone Swing based rich clients.