Change Schema Design #40
Description
I have discovered some issues with our current schema. As an example think of this problem, I have a user and I would like to calculate the size in KB of all the user's output data. What does that require. It requires a search over all the projects to find the user's projects. It then requires a search over all the records to find the records for all the user's projects. Once we have the projects we then need to do another search over all the record bodies to match the record bodies to the head. This is a great many expensive queries to do this operation. I read the following blog post...
http://blog.mongodb.org/post/87200945828/6-rules-of-thumb-for-mongodb-schema-design-part-1
and it seems clear that we are not using correct schema. Firstly, there is no reason why the record head and record body should not be bidirectional, in fact they should be. There is no benefit and many drawbacks to having the reference in only one direction. Secondly the user should store references to its projects since this is a one to few relationship (but still have the project store its user as well) again making this bidirectional. This is a classic case where there may be a large number of projects in the database but a user is only identified with a very few of the projects so a user should store the project references. The project / record relationship is probably correct since that is truly a one to many (squillion?) relationship (not one to few).