SOA design principles with regards to database relationships

Question

If I were to extricate my current membership provider from my solution, i.e. as a dll and expose it as a web service with it's own db, how would I model the relationships with regards to SOA design.

For example

I have a table:

USER id, name, lastname, username, password, role.

and table

PRODUCT id, name, price, createdate, userid

the foreign key being userid to table user.

How would I model the relationship and/or query the db.

If I wanted to get all products that were uploaded today for example, before I would query:

SELECT u.name, u.lastname, u.username, p.* FROM PRODUCT p INNER JOIN USER u ON p.userid = u.id WHERE createdate = '05/05/2010'

Now that I don't have the table within the database how would I perform this query?

Thanks.

Answer 1

As I understand you have two services, one with users and another with products, and of course you do not want to connect those services.

I think you should orchestrate over those two services with another service and, in that orchestration service, collect the products from the product service and after call the user services for user names.

If it destroys your performance you could "denormalize" your services adding the name to the product entity or caching or whatever. But I would avoid that as possible.

Answer 2

I would take a completely different approach.

You often find that when programmers write inline dynamic sql it is to get a readonly view of the data. Therefore why do you want to populate heavy entity beans which could bring back n rows when you only need readonly access? This is especially true if you are dealing with web services.

Instead I take the approach of having a CRUD data service that returns single and a collection of entity beans say product and for the readonly queries I need I have a lookup service. Which takes in a lookup name say 'LookupAllProductsUploadedToday' and maps it to a db stored proc (eliminating the need for horrible dynamic sql!) the returned dataset is then turned into a lookup bean which is basically a collection of key/value pairs and sent out of the service to the application.

I heavily favour stored procs over inline sql for security reasons as you can then only give read and execute rights to stored procs and deny access to executing dynamic sql statements. I developed various SOA applications and have not needed to write any inline sql taking this approach.