Denormalization support? Discuss, review or feedback

[clark0x]

When using mongodb, it's very common to use denormalizated schema, to make single database query for each request.

For example, User model may have many properties (displayName, avatar, location, address, cellphone, ...), Blog model has an author attribute to associate with User model, but not only with user.id, but also user.displayName. If Listing Blogs only need to display user's name, then GET /blogs request only need to query blogs collection ONCE without querying users collection. Of cause there is cost: data redundancy. But user won't change their displayName every day.

Hence I think it will be great that sails could support denormalization.

My teammates were going to replace associations with simple sub-documents, until I worked out certain commits on waterline, waterline-schema, and sails-mongo.

The idea is to add an embed property on association config, it can has an array of attribute names, then the child model (with specific properties ONLY) will be embed to the parent model, and this embedded document can represent childKey(or foreignKey). Basically, using association api just like before( with foreign key, saved model object, or new model object), then the associated models will be saved and retrieved as sub-document of parent models in mongodb, instead of just foreign keys.

// Blog
module.exports = {
  attributes: {
    title     : { type: 'string' },
    content   : { type: 'string' },
    author    : { model: 'User', required: true, embed: ['nickname'] }
  }
}

User.create({nickname: 'clark', birthday: '2014-01-01'}).exec(function (err, user) {
  Blog.create({title:'', content: '', author: user}).exec(function (err, blog) {
    console.log(blog);
    // { id: '5373c6bcd9d5d200005c31e9',  title:'', content: '', author: { id: '5373c6bcd9d5d200005c31e8', nickname: 'clark' }, ... }
    // > db.blogs.find():
    // { _id: ObjectId('5373c6bcd9d5d200005c31e9'), author: { _id: ObjectId('5373c6bcd9d5d200005c31e8'), nickname: 'clark' }, ... }
  });
});

sails.config.blueprints.populate = false // set `false` to make single query on `find` and `findOne`

[mikermcneil]

@clarkorz thanks for the detailed review. To clarify- is the idea to keep track of the other model in the standard association methodology, as well as embedding it? Or is this just straight-up embed? From a 1000 foot view, I'd say the latter would be a bit simpler to implement, but curious to hear your thoughts.

[mikermcneil]

@clarkorz (ps- haven't looked at the PR yet, but we'll look at is as soon as we can). Thanks!

[particlebanana]

@clarkorz thanks this sounds like a nice solution for mongo. I'll have to spend some time going through the PR's and understanding what all is happening. I'll try to get this into the next rc release if it makes sense and there is time.

[clark0x]

@mikermcneil sorry, not an English speaker, have trouble to understand the phrase "straight-up embed", is that means "embed the model fully without storing it in an separated collection"? If my guess is right, then NO, my idea is more like the former one. The One side model stores its records in its own collection, while the Many side model caches a few attributes of the associated One model. I use the word embed because sub-documents are embedded in a parent document.

My idea is the same as this article, in case I use grammar wrong (I almost confuse myself &_&

[clark0x]

@particlebanana thanks~ I think this feature needs some more work.

1. support query (convert id to xxx._id in sails-mongo)
2. restrict attributes to be denormalizated (cached? embedded?) explicitly, because updating these attributes are expensive
3. support to-Many side (replace array of foreign keys with array of sub-documents that contains foreign key and denormalizated attributes)
4. support eventual consistency: update denormalizated attributes automatically when the source attributes changed, or do it manually in afterUpdate()

[edwardhotchkiss]

@particlebanana @clarkorz Het guys, I'm here from Mongoose.js background. Any updates on this? I'd love to help if possible either with making associations/embeds happen or with docs.

[clark0x]

@edwardhotchkiss I have only finished the 1 in my list. Check my fork of waterline and sails-mongo. I'm not sure of how to complete this feature, and I haven't got any feedback. Do you have any idea or plan?

btw, do you think that embed is the proper keyword for this?

[arxpoetica]

For anyone who's wanting a little clarification on all of this, THIS is a good read: http://blog.mongodb.org/post/87892923503/6-rules-of-thumb-for-mongodb-schema-design-part-2.

(Read parts 1 and 3 as well if you want.)

Essentially, it's very common in the Mongo world to do denormalization (embeds). All based on need. If your data is limited, (one user has just a few addresses) it can make a lot of sense to just embed those addresses. On the other hand, joins can be needed where lots (gazillions) of independent data needs to work together.

Another scenario (not denormalization, but sort of like it) is to support parent arrays of child ids. I don't think Waterline does this either. I'd love to find out I'm wrong, since I need that exact functionality right now. (Will be happy to open a separate ticket to discuss that exact need.)

[arxpoetica]

Another scenario is denormalizing many to many relationship data: http://seanhess.github.io/2012/02/01/mongodb_relational.html#many-to-many-relationships

[mattapperson]

@clarkorz still at a stand still with this?

[sailsbot]

Thanks for posting, @clarkorz. I'm a repo bot-- nice to meet you!

It has been 60 days since there have been any updates or new comments on this page. If this issue has been resolved, feel free to disregard the rest of this message. On the other hand, if you are still waiting on a patch, please:

• review our contribution guide to make sure this submission meets our criteria (only verified bugs with documented features, please; no questions, commentary, or bug reports about undocumented features or unofficial plugins)
• create a new issue with the latest information, including updated version details with error messages, failing tests, and a link back to the original issue. This allows GitHub to automatically create a back-reference for future visitors arriving from search engines.

Thanks so much for your help!

[RWOverdijk]

Bump. This can be extremely useful and I've found myself in need of this. What's the status?

[sailsbot]

Thanks for posting, @clarkorz. I'm a repo bot-- nice to meet you!

It has been 30 days since there have been any updates or new comments on this page. If this issue has been resolved, feel free to disregard the rest of this message. On the other hand, if you are still waiting on a patch, please:

• review our contribution guide to make sure this submission meets our criteria (only verified bugs with documented features, please; no questions, commentary, or bug reports about undocumented features or unofficial plugins)
• create a new issue with the latest information, including updated version details with error messages, failing tests, and a link back to the original issue. This allows GitHub to automatically create a back-reference for future visitors arriving from search engines.

Thanks so much for your help!

[devinivy]

This is a placeholder for ecosystem-wide PRs that have been around for a long time, so I think we have to keep this open so that those PRs aren't orphaned.

[sailsbot]

Thanks for posting, @clarkorz. I'm a repo bot-- nice to meet you!

It has been 30 days since there have been any updates or new comments on this page. If this issue has been resolved, feel free to disregard the rest of this message. On the other hand, if you are still waiting on a patch, please:

• review our contribution guide to make sure this submission meets our criteria (only verified bugs with documented features, please; no questions, commentary, or bug reports about undocumented features or unofficial plugins)
• create a new issue with the latest information, including updated version details with error messages, failing tests, and a link back to the original issue. This allows GitHub to automatically create a back-reference for future visitors arriving from search engines.

Thanks so much for your help!