Re: algorithm help for determining efficient db data retrieval

[dragonchild]

I'm confused. This seriously sounds like you're having a bad case of premature optimization. Write the code in the easiest way you have to code it. If that means pulling all the properties for a given object from the database, then do it! If that means pulling entire objects from the database one at a time, then do it! Later, if stuff is slow, then optimize it. But, you don't even know that it will be slow, let alone where!

As a second note ... your database design sucks. I haven't seen it, I have no idea what it's for, but I know it sucks rocks. How do I know this? Because you have objects and you want to store them in the database. No No NO! This is a "Bad Plan"(tm). Objects are for using. Databases are for storage with referential integrity. They are completely unrelated things. Sure, you may have an INVOICE table and an Invoice object, and they may look like they're 1-1, but this is the exception, not the rule.

Here's the solution you are looking for (and you've probably done most of these steps, but I'm going to state them anyway, just in case):

1. Figure out what you're trying to solve.
2. Figure out what data you need to accomplish #1.
3. Figure out what algorithms you need to accomplish #1.
4. Figure out how to store the data from #2.
5. Figure out how to code up the algorithms from #3. (This may or may not be OO.)
6. Then, AND ONLY THEN, do you figure out how #'s 4 and 5 map together.

Let me repeat - your data and your objects are unrelated entities. You fill the latter with stuff from the former. You should not define your data in terms of your objects. That's just asking for trouble.