3 ms·

I currently use Meteor for a production app, but only use Mongo as a scratch pad (holding data until the user is mostly done changing their mind and other minor

I currently use Meteor for a production app, but only use Mongo as a scratch pad (holding data until the user is mostly done changing their mind and other minor UI state). Data is then packaged and sent to MS SQL Server for long term storage and reporting. As an example, a user can start updating a piece of data (eg: sales order, item, etc), with everyone able to see the changes as they happen (no hard need for locking, easy to support temporary network loss). Once they are done with changes, they are sent to SQL. The advantage of doing it this way, is that the data stored in SQL Server can be completely optimized for reporting/storage and doesn't need the schema to support UI requirements. This makes things much simpler for people writing the UI as they don't have to change the SQL schema often to support the shinny new UI widget of the day and the report writers don't have to sift through a million useless to them tables/columns or partially entered rows to find the data they need.

rashkov 11y ago
This is very interesting. Could you give a little more details on how you accomplish this? How do you serialize unstructured Mongo data into MS SQL? Does this system break down when front-end people change the data structures in Mongo? I'm not familiar with Meteor but maybe you could explain in broad strokes
- extrapickles 11y ago
  There are several ways we go about it depending on the needs of the data to be stored in SQL. All of our data in SQL and Mongo has a uuid as the primary key and effectively lets us use merge-style replication techniques, and support temporary offline use of our application. If the Mongo data is flat, its very easy to go from Mongo->SQL, only a type mapper is needed. The lumpy data case gets more complicated, as its dependent on the data. The complex data generally ends up translating into master/detail tables or similar ideas. Various pieces of data that is nice to have, but changes with the UI/customer needs, gets stuffed into several key/value tables (one for each type). For the completely dynamic Mongo data we store in SQL, we alter the SQL table programmatically to meet the new schema (in part of our application, customers must be able to drive the schema). This way works in our specific application, but it requires a lot of work to make sure your users cant paint themselves into corners with their schema. So far we haven't experienced any issues with the front-end people changing data structures, as the changes they generally make only impact fields that are not stored in SQL. Completely new features generally require the addition of new columns/tables to SQL, but every application has that issue. During startup of the server it double checks the schema of all of the tables used and updates as needed.
  - rashkov 11y ago
    thanks very much for the explanation. That sounds like interesting work and a great solution

rashkov 11y ago

This is very interesting. Could you give a little more details on how you accomplish this? How do you serialize unstructured Mongo data into MS SQL? Does this system break down when front-end people change the data structures in Mongo? I'm not familiar with Meteor but maybe you could explain in broad strokes

extrapickles 11y ago
There are several ways we go about it depending on the needs of the data to be stored in SQL. All of our data in SQL and Mongo has a uuid as the primary key and effectively lets us use merge-style replication techniques, and support temporary offline use of our application. If the Mongo data is flat, its very easy to go from Mongo->SQL, only a type mapper is needed. The lumpy data case gets more complicated, as its dependent on the data. The complex data generally ends up translating into master/detail tables or similar ideas. Various pieces of data that is nice to have, but changes with the UI/customer needs, gets stuffed into several key/value tables (one for each type). For the completely dynamic Mongo data we store in SQL, we alter the SQL table programmatically to meet the new schema (in part of our application, customers must be able to drive the schema). This way works in our specific application, but it requires a lot of work to make sure your users cant paint themselves into corners with their schema. So far we haven't experienced any issues with the front-end people changing data structures, as the changes they generally make only impact fields that are not stored in SQL. Completely new features generally require the addition of new columns/tables to SQL, but every application has that issue. During startup of the server it double checks the schema of all of the tables used and updates as needed.
- rashkov 11y ago
  thanks very much for the explanation. That sounds like interesting work and a great solution