On Mon, 26 May 2008 18:06:21 -0400 "Christopher M. Jones" cjones@partialflow.com wrote:
The product catalog should remain fairly stable. It's mainly stock (price, availability, location) that needs to come from the MSSQL db. Prices and availability are managed on site using a custom VB app that talks to the MSSQL server. Quantities, pricing, and locations are constantly changing and need to be accurate to the second.
That looks tricky and that's exactly why I decided to put the business logic into the DB. Especially if you want to be sure that the order is going to be closed with quantities accurate to the second, you'll need some locking mechanism across 2 DB. Then if your DB are in different places latency problem won't make keeping the thing running easier.
Another tricky piece is going to be a 'sell cart' in addition to the traditional 'buy cart'. I foresee this requiring some hacking. Does anyone have initial thoughts on this?
It start to look as if you're going to implement your own e-commerce engine. In Ubercart products are nodes. That means they "should" reside in Drupal DB. If you need "accurate to the second" quantities you'll have to see how Ubercart closes the order and update the inventory. Its design may not be suited for "accurate to the second" inventory management or may just be hard to be converted to a multi DB system.
One thing is "on demand" check of inventory status, another is closing an order coherently... or you may rethink your constraint and specification.
The core of Ubercart cart "workflow" is actually workflow-ng module that doesn't look to use any lock on the DB, nor transactions, making it pretty hard to transform a basket into an order, saving the correct inventory quantities and updating them with an accuracy "to the second".
Ubercart and workflow-ng are pretty impressive pieces of software... and may save you hundreds hours of programming if you're really not that concerned of "accurate to the second" quantities, pricing... and the probability and cost of getting into troubles is acceptable. Consider that the probability of getting into troubles is going to increase if you'll use 2 DB.