I am building a Meteor.js application that uses MongoDB. The application
is a retail point of sale system aka cash register.
I am not sure how I should architect Invoices and all associated data which
ultimately represents a sale.
Currently, Invoices is a collection that contains parts of data from other
collections like cashier which has data from Users, location which has data
from a Locations collection, register which has data from the Registers
collection. I think you get the point.
The tough decision I am having trouble making is how to design where the
multiple items within an invoice go. Invoices can have multiple items
containing data form the Items collection in them and each Item has
customers, discounts, taxes, and a sales associate containing data from
their respective Collections . Items is a collection that represents
things for sale like products, services or even liabilities like gift cards.
Before continuing, it is important to understand the reason Invoices and
the items in a an invoice contain a copy of some of the data fields of the
other Collections (like Locations, Registers, Users, Items, Taxrates,
Discounts, etc...). It is because the data in those collections may change
over time but an Invoice and it's data can never change once created and
closed (paid for). For example, someone in merchandising may update the
prices of things on sale, so the item in an Invoice cannot be a relation
to those Item records but most contain a static historical subset of the
data. Anyone familiar with Accounting principles understands what I am
talking about. I wanted to state this to eliminate suggestions stating
that only the _ids of the parent collections are needed in an Invoice and
the invoice items. That would not work.
Here is where I am stuck and what I believe my two options are.
1. Store all Invoice Items in Invoices.items{ [ ] } as a field of array
objects aka Full Tree document tree.
"items" : [ {"item" : { } }, {"item" : { } }]
This is a common and I believe fairly recommended Mongo pattern for
document data storage but also has limitations and will complicate
searching and updating.
This is how I am currently doing it and I am finding the experience painful.
2. Store all Invoice Items in a separate collection called InvoiceItems
with the Invoice._id as a parent key. This pattern is more familiar to
many of us because of years of relational database development.
Interestingly, I have found that Meteor lends itself nicely to this model
because I could pub/sub based on whatever Invoice._id is selected in
session by the user by subscribing to InvoiceItems.find({_invoice_id:
Session.get('selected_invoice_id')}); or something similar. I am now
leaning towards going in this direction.
Additional issues that beg the same question is what about each invoice
items sales associate, customer, taxes, discounts, etc... If I go route 2
it begs the question of should there be an InvoiceItemsCustomer,
InvoiceItemsSalesAssociate, etc...
Like I said, I am building this application all by myself and having some
people to bounce ideas off of would help.
I hope I described the situation clearly.
Any and all opinions and ideas would be greatly appreciated.
Thanks
Steeve
