关于数据库关系的 SOA 设计原则
如果我要将当前的会员提供程序从我的解决方案中解脱出来，即作为 dll 并将其公开为具有自己的数据库的 Web 服务，我将如何对与 SOA 设计相关的关系进行建模。
例如
我有一个表：
USER ID、姓名、姓氏、用户名、密码、角色。
表
PRODUCT
id, name, Price, createdate, userid
外键是表 user 的 userid。
我将如何建模关系和/或查询数据库。
例如，如果我想获取今天上传的所有产品，那么在查询之前：
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'
现在我在数据库中没有该表，我将如何执行此查询？
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?
如果你对这篇内容有疑问，欢迎到本站社区发帖提问 参与讨论，获取更多帮助，或者扫码二维码加入 Web 技术交流群。
绑定邮箱获取回复消息
由于您还没有绑定你的真实邮箱，如果其他用户或者作者回复了您的评论，将不能在第一时间通知您！
发布评论
评论（2）
据我了解，您有两项服务，一项与用户相关，另一项与产品相关，当然您不想连接这些服务。
我认为您应该将这两个服务与另一个服务进行编排，并在该编排服务中从产品服务中收集产品，然后调用用户服务获取用户名。
如果它破坏了您的性能，您可以“非规范化”您的服务，将名称添加到产品实体或缓存或其他内容中。但我会尽可能避免这种情况。
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.
我会采取完全不同的方法。
你经常会发现，当程序员编写内联动态sql时，是为了获得数据的只读视图。因此，当您只需要只读访问时，为什么要填充可以带回 n 行的重型实体 bean？如果您正在处理 Web 服务，则尤其如此。
相反，我采用了一种 CRUD 数据服务的方法，该服务返回单个实体 bean 和一组实体 bean（例如产品），对于我需要的只读查询，我有一个查找服务。它接受查找名称“LookupAllProductsUploadedToday”并将其映射到数据库存储过程（消除了对可怕的动态sql的需要！）然后将返回的数据集转换为查找bean，它基本上是键/值对的集合并发送退出应用程序的服务。
出于安全原因，我非常喜欢存储过程而不是内联 sql，因为这样您只能向存储过程授予读取和执行权限，并拒绝执行动态 sql 语句的访问权限。我开发了各种 SOA 应用程序，并且不需要采用这种方法编写任何内联 sql。
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.