We're reviewing some of our practices at work - and the specific thing we're looking at right now is the best method for doing Comms with Flex based clients, and .NET web services.
Our typical approach is to first model the interactions based on requirements, mock up some XML messages and sanity check them, turn those into XSDs, and finally build classes on each end that serialize to/from XML.
This works okay until we hit the database and then things like Join Tables start mucking up all that work we did simplifying down the client end.
We've tried solving this with LINQ to SQL and other OR mappers, but none of them really solve the problem without introducing more serious issues.
So, the question really is:
Without treating a RDBMS as simply an object store, is there a better way to handle complex data requirements without writing a huge amount of conversion code?
I suppose the magic bullet I'm looking for is something that knows what a Join table is and how to deal with it, and still allows me to generate 'nice' serialized XML for Flex and retains strong .NET Typing.
Bonus points if it can analyse the SQL required for each method, generate stored procedures and use them. But that's probably asking too much :)
Note re "Join Tables":
Our definition of this is where you have a table which has two or more foreign keys as it's own primary key. eg: Photos (PK PhotoID) <- PhotoTags (PK FK PhotoID, PK FK TagID) -> Tags (PK TagID).
When a Flex client gets a Photo object, it might also get a List of all Tags. So, that might look like so:
<photo id="3"> <tags> <tag name="park" /> <tag name="sydney" /> </tags> </photo>
Instead, the OR tools I've seen give us:
<photo id="3">
<phototags>
<phototag>
<tag name="park" />
</phototag>
<phototag>
<tag name="sydney" />
</phototag>
</phototags>
</photo>