OOP, databases and impedence mismatch...

I'm working on a project that started out fine, but has now kind of hit a wall. It's object oriented, but using a relational database (mysql). The original model was to map each table to a class (basic object relational mapping), and for a while everything was fine.

Then I got to the point where I needed to do sql join, which combines the result from multiple tables. And suddenly my object model didn't match the database model any longer, and I was in trouble. It's apperantly called impedence mismatch, and is well known.

I'm looking at different solutions, all requireing a considerable amount of work. The one I'm most happy with (in theory), goes something like this.

I have class for each table, that extends a field class. Rather than have a single instance of that class cover a row in that table, it only covers a single field/column of a single row. Then I create an entity class that collects all the field instances in a list/array. Does that make sense?

In theory now when I do a join sql statement, I can take the result and create one instance of each field, and one entity for the row that collects all the fields. Each field storing the id of the row it came from, so I can find it again in the database.

So if I wanted to draw an entity, it would simply loop through all it's fields and run the draw method on each.

In theory I can't see any big problems with it, but I'm sure there are some? I was wondering if anyone here has been down the same path and could point out to me some of the holes I'm likely to find in the road so that I can avoid them, before I begin the work of changing the code around considerably.

Cheers,

Ragnar

> Then I create an entity class that collects all the field instances in a list/array. Does that make sense?

Makes sense, but sounds horribly inefficient (SP?). I have never gone down this path, mainly having to do with transaction integrity - I seperate my OOP classes from my table structure. If they happen to match, then that is fine, if they don't then that is fine too.

I don't think I can be much help since I would have gone down a totally seperate path.

Why not create yourself a special Data Query Object which maps to all the fields that you need to retrieve from your query of multiple tables.
When you retrieve a row, this object can map the parts of it to the individual objects you use for the separate tables.

I tend to use these to improve efficiency, where you can retrieve from several tables in one shot rather than having to make several separate queries.

Just a thought...

That's kind of what I'm doing at the moment, at least in my test code. A query that goes something like.

SELECT * FROM user LEFT JOIN task ON task.assigned_user_id=user.id WHERE user='ragtag'

This is just a single query. I then go through the every field in each row, creating a field object for each field, collecting them in an entity. At the same time I'm trying to keep my code so that it will be easy to extend and add new features in the future.

dynaman: Don't be to sure you can't help. I've considered many different options, including dropping objects completely and going for procedural/relational code only. You approach sounds interesting. What kind of classes are you creating (drawing, validation, querydatabase etc.)? Would you care to elaborate a littel on what path you would go down?

I do airport tower monitoring work, so my classes are airport, runway complex, runway, and flight. The airport keeps a list of runway complexes, which keeps a list of runways. The flights are all in one class (for performance) with methods to extract all those for a runway or complex or airport. The airports and runways don't change much (have to actually build an airport or runway...), while the flight plans update continually - which is why they are kept in a seperate class. The class keeps a disconnected records of all flightplans, updated every 5 seconds or so.

For updates, flightplans have methods to handle requested operations, take off, taxi scan, etc... When an operation is done I start a transaction and run individual update/insert/delete statements (whatever is needed for that particular operation) and then commit the transaction.

Although flightplans is a table the flightplans class pulls in other table information as needed, restriction data for instance, and then merges it all together in the disconnected recordset.

I hope that didn't ramble too much...