With many web 2.0 applications there’s a basic three-tier architecture..   In our case the client is a Flex 3/Caringorm application, the Services are WCF/ASP.NET Web Services, and the Database SQL 2005.

One of the typical approaches to creating Web Services for this type of system is to use a CRUD type pattern. That is: all methods are based around either Creating, Retrieving, Updating, or Deleting records.  In most usually done on a per-table basis, and means that you’re effectively making the Web Services a HTTP enabled SQL client.

For our situation, this wasn’t really appropriate for a number of reasons, including complex relationships between tables, and a need to reduce the amount of network traffic.

Another concern, although relatively minor, is to reduce the amount of work needed by the Flex team to implement the Web Services. 

Ideally, we wanted to be able to share business objects as widely as possible, to reduce the amount of rework needed by everyone involved in implementing the interfaces.

Therefore we chose to go with task, or semantic based methods, and using the objects as needed by the Flex front-end.  The work of validation, and mapping to appropriate tables would be done by the Web services.

An example of this might be that a Document had many properties, such as Media Items (pictures, video, etc), Tags, Authors, etc.  However, within the database there might be a necessity to track Document Versions, What versions are Live, the relationships between Documents, Document Versions and Media Items. 

Because the objects that I needed to send/receive didn’t match the objects that needed to be saved in the database, I needed to write a lot of “left hand/right hand code”: ServiceDocument.Property =   SQLDocument.Property.  Most of this was fairly simple code to write, but tracking the places where this takes place can be grow to become quite a challenge when the solution grows to dozens of tables.

This is an approximate list of what I need to do to add a property to one table:

• Add the Property to the Service Types
• Add conversion pieces to transpose the Service Type to/from the LINQ to SQL Object equivalents.
• Add the column to the Table in the Database Model for LINQ to SQL
• Add the column to all Stored Procedures in the Database Model which reference this, removing and re-adding them if this means new properties too.  Don’t forget to ensure the return types on the re-added Stored Procedures are set correctly.
• Add the columns to the actual Stored Procedures, update parameters, etc
• Add the column to the actual Table

I can only imagine the Version Control conflict chaos that would ensue if you had several people making these changes concurrently.

I highly recommend grouping changes into a per-table basis, because it can take a while to go through all the additional pieces you have referencing the LINQ to SQL and Service Type object equivilents.