I'm developing a POS system and I need to store every transactions (product info, price, quantity, etc.) for each cash register terminal. This of course means that number of transactions documents will grow in time.
My current solution is the following:
There are two collections called 'registers' and 'sales'. Sales documents have a register id reference so I know which sales documents belong to which cash register. The transactions are stored in an array inside each sales document (about 300 new transaction documents every damn day).
To have better performance when updating already large arrays, I have designed a small 'cached' array (about 50 documents - so I only update the small array most of the time) in each sales document and when the cache array is full , I would move them to the main transactions array.
Because the max size of documents in MongoDB is limited to 16MB, I have set a count limit of 10000 transactions for the sales documents, and if the number of transactions exceeds the count limit, I would create a new sales document and have their id reference stored in an array in the registers document to preserve the sales documents order.
I'm not quite happy about this design because I had to write very complicated queries to retrieve about 200 transactions for each query in a way that the transactions stay in order for pagination, and also to handle extreme cases.
So I'm thinking about making a VERY BIG TO BE (constantly growing) collection called just 'transactions', where I would throw all transactions of every cash registers to one pile, then each transaction would have its own register id reference.
Question: Should I do it?
Update: How I need to access data:
- Insert and read only, never update or delete existing documents
- Insert is the most frequent operation
- Read queries should return an array of documents that fit in a transaction numbers range or in a range of time created, the array doesn't need to be sorted)
- Read: Most of the time, I need to show only the first 200 most recent transactions. Then the user can query for more if needed.
- Simple queries (not sure if effective though), e.g. find transactions by id and filter by transaction number/time range while querying for a specific number of transactions
- Avoid unwanted duplicates
- One step closer to array-free database
- Good for sharding (?)
- Too many indexes (is this considered a problem? would it matter if there were trilions of indexes for these small documents?)
- I have no idea what would happen after I do it. In theory, it should work. But reality is more cruel than we know it
- Maybe the main reason why I chose arrays over one-pile collection was that I did not have any experience with MongoDB when I started.
- And yeah, I wanted to make sure the transactions stay in order