I’m wondering how people store look up data in Mongo/Meteor?
Say I have a Customer table, and each customer can belong to one sales region. In SQL I would do something like this:
Customers table
- CustomerId
- CustomerName
- SalesRegionId (optional FK to SalesRegion table)
- CreatedDate
SalesRegions table
- SalesRegionId
- SalesRegionName
- Country
- CreatedDate
For Meteor, it seems to me I have three choices (no, four choices…) as to how to store the data:
-
The way I would have done it in SQL, where I store the SalesRegionId in the Customer document:
Customer = {
_id:
CustomerName:
SalesRegionId:
CreatedDate:
} -
Denormalise the the data I’m likely to need often when listing out data from the Customers document
Customer = {
_id:
CustomerName:
SalesRegionId:
SalesRegionName:
CreatedDate:
} -
Denormalise the data by adding the SalesRegion data as a child record to the customer document
Customer = {
_id:
CustomerName:
SalesRegion {
SalesRegionId:
SalesRegionName:
Country:
CreatedDate:
}
CreatedDate:
} -
Denormalise the data by adding the majority of the SalesRegion data as a child document, but store the SalesRegionId as field in the Customer document, as discussed in this blog post:
Customer = {
_id:
CustomerName:
SalesRegionId:
SalesRegion {
SalesRegionName:
Country:
CreatedDate:
}
CreatedDate:
}
Ultimately the data from this system will need to be reported on and also I want the normal UI pages (customer lists, top customer lists, etc.) to load quickly.
Can anyone give me some guidance of which of the approaches above is best, or when one is more suitable than another?
Also, if one of the denormalized approaches is best, whereabouts in your meteor code do you do that denormalization, would that be in the form code when you pick the item in the list, or would you wait and look up the denormalized data in a collection hook to ensure the id matches the region name (as otherwise a coding error or malicious bit of code could mean you could end up with an SalesRegionId that doesn’t match the SalesRegionName)?
(I had a good read of the Meteor Guide section on Collections, and whilst that talks about denormalization, that’s in reference to child collections rather than lookups, which is a subtly different case)