When we started our application we had the choice of going with the traditional MS-SQL normalized database or with a NoSQL database (RavenDB is what we tried out). Here is a simplified version of our schema:
<Order OrderNumber="1000" OrderVersion="1" ...> <Products> <Product Code="X58" Price="$5.50" Quantity="3" ... /> <Product Code="X70" Price="$9.99" Quantity="1" ... /> </Products> <Contacts> <Contact ... /> <Contact ... /> </Contacts> </Order>
While we could have went with a MS-SQL database, we didn't because we wanted all of our data for an order to be kept historically; we did not want to FK the contact even if they had the same name and address because while it might change, the order should have the contact info at the time of purchase. Also, yes, we could have FK our Products instead of using the Code to reference the product, but Products never get deleted, and again... if they are updated we want to capture their information at the time of purchase. Basically, every Order seems to be a self-contained document that should never rely on FKs.
OrderVersion is just to keep track of our process version when the order was created, we could easily just have used OrderDate to track versioning.
Anyways, so our decision was to go with RavenDB. We built out our application and website, and internally we are testing it out. The one scenario we are running into problems is: "How do you do a bulk update on records as good as SQL does it?"
For example, if we had just pushed our code to production during offline maintenance and somehow all records from MA with ZipCodes that have a leading zero (0) had the zero trimmed off (i.e. the zip code now read 1234 instead of 01234)?
Normally, we would patch things immediately on one fail-over server at a time, and updating records would be easy:
UPDATE c SET c.ZipCode = ... FROM Contact c INNER JOIN Order o ON c.OrderId = o.OrderId WHERE o.Version = 1 AND ...
But, it seems that the best solution we could come up with if we use RavenDB is to get all documents, then programmatically update the records through a custom C# console app. It seems that TSQL is much easier/faster. I'm not sure if there is a better way that we are unaware of, or is this a common shortcoming of using RavenDB (or any NoSQL document store)?
My biggest worry is, while our console app seemed to run just fine enough with a sample of 100K orders, what happens if we get to the point of 50M+ orders!? Or, is that also an issue with T-SQL that we would eventually run into with the example I gave?