How to store denormalized lookup data?


#1

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:

  1. The way I would have done it in SQL, where I store the SalesRegionId in the Customer document:

    Customer = {
    _id:
    CustomerName:
    SalesRegionId:
    CreatedDate:
    }

  2. Denormalise the the data I’m likely to need often when listing out data from the Customers document

    Customer = {
    _id:
    CustomerName:
    SalesRegionId:
    SalesRegionName:
    CreatedDate:
    }

  3. 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:
    }

  4. 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)


#2

Hi Tom,

just letting you know that I have created a package related to this, that would help you to denormalized a “read-only”-collection, comparable with an “sql-view”: https://github.com/thebarty/meteor-denormalized-views


#3

MongoDB has a lot of interesting posts on schema design. One of the key concepts that departs from SQL normalization is that of one-to-few vs. one-to-many relationships. Coming from a SQL background, the mindset is really quite different.