I need advice on the best way to structure a database: accounts can have multiple roles in multiple organizations.
Lets say Bob can be a user for xCorp and a manager at yCorp.
Jack has both user and manager roles at zCorp, and a manager at xCorp.
Frequent requests
getOrganizationManagers
getOrganizationUsers
getOrganizationsByAccount({account: accountId, role: ‘user’ or ‘manager’})
Practically any organization will have less than 1000 users with maybe 10 managers (though it would be nice if this could scale).
Most accounts will be users on one or two organizations and a manager and user in one organization.
Option 1 - more like sql normalization
Account Properties
-name: {type: string}
-email: {type: string}
-roles: { type: ObjectId, ref: Role}
Role properties
-account: {type: ObjectId, ref: Account}
-organization: {type: ObjectId, ref: Organization}
-role: {type: string, enum: [‘Manager’, ‘User’]}
Organization properties
-name: string
Option 2 - more denormalized
Account Properties
-name: {type: string}
-email: {type: string}
-organizations: {
–organization: { type: ObjectId, ref: Organization}
–role: {type: string, enum: [‘Manager’, ‘User’]}
}
Organization properties
-name: string
-members: {
–account { type: ObjectId, ref: Account},
–role: {type: string, enum: [‘Manager’, ‘User’]}
}
Question 1: Is one or the other method the more accepted mongodb way? (if neither then what?)
Question 2: How to best structure the requests above for best performance?
I am fairly new to mongodb and use mongoose as part of a node.js app to handle requests to the database.