Saturday, 9 February 2019

MongoDB - Structuring and querying models and schema for many to many relationships

Trying to battle the shortcomings of relationships in MongoDB, here is what I am trying to have in my database:

Models:

  1. Users: (already have many users in the database) mostly queried via their uid but the _id is also available.
  2. Teams: Contains 1 owner, multiple members, projects and assets.
  3. Members: Can belong to multiple teams, each member can have different roles. A member can belong to multiple teams with different roles.
  4. Roles: Belongs to members. A member can have a different role in each team they belong to. ['view', 'edit', 'admin']. Roles can also be embedded in the Members model to go with the hybrid method.
  5. Projects: projects can be independent or part of a team, the access to the projects which belong to teams is determined by the member's role.
  6. Assets: Assets can be independent or part of a team, the access to the assets which belong to teams is determined by the member's role. An asset can contain multiple projects too.

** Specifications:**

Each user can be a member of a team or independent users of the application. Normal users can have their own private projects and assets.

A member of a team can have access to assets and projects that the team owner decided they can have access to.

A member can create their own private assets and projects.

Depending on the member's role, a member can also share assets with their team members.

A member, depending on their roles can add, remove, edit assets of a team, delete or rename a team, add or remove members from the team.

A member can view the list of the assets and projects shared with them within the team.

A member can view single assets page, where they can add remove items in the asset, depending on their roles.

A member can view, edit, add, remove single projects, depending on their roles.

Current Schema:

const teamSchema = new Schema({
  uid: {
    type: String,
    required: true
  },
  name: {
    type: String,
    required: true
  },
  members: {
    type: [String]
  },
  brands: {
    type: [String]
  }
}, { collection: 'team', timestamps: true });

const memberSchema = new Schema({
  uid: {
    type: String,
    required: true
  },
  owner: {
    type: String,
    required: true
  },
  teamID: {
    type: Schema.Types.ObjectId,
    required: true
  },
  acl: {
    type: String,
    required: true,
    enum: ['view', 'copy', 'edit', 'admin', 'owner'],
    default: 'view'
  },
  pending: {
    type: Boolean,
    default: true
  }
}, { collection: 'member', timestamps: true, _id: false });

const assetsSchema = new Schema({
  uid: {
    type: String,
    required: true
  },
  title: {
    type: String,
    required: true
  },
  fonts: [AssetFile],
  colors: [Color],
  logos: [AssetFile],
  images: [AssetFile],
  projects: Array,
  items: Array,
  members: [String]
}, { collection: 'branding', timestamps: true });

const userSchema = new Schema({
  uid: {
    type: String,
    required: true
  },
  email: {
    type: String,
    required: true,
    lowercase: true,
    index: { unique: true },
    validate: v => validator.isEmail(v)
  },
  avatar: String,
  files: [FilesSchema],
  isAdmin: Boolean,
  projectCount: {
    type: Number,
    default: 0
  },
  userName: {
    type: String,
    trim: true
  },
  userType: {
    type: String,
    required: true,
    default: 'Free User'
  },
  lastSeenAt: {
    type: Date
  },
}, { collection: 'user', timestamps: true });

const projectSchema = new Schema({
  uid: {
    type: String,
    required: true
  },
  objects: Array,
  projectTitle: {
    type: String,
    trim: true,
    default: 'Untitled Project'
  },
  preview: {
    type: String
  },
  folder: {
    type: String,
    trim: true,
    default: null
  },
  archived: {
    type: Boolean,
    default: false
  },
}, { collection: 'project', timestamps: true });


These can be easily achieved in relational databases, but since my app is already out in the open with more than 40K users, moving to another DB is not an easy option.

Could these be achieved in MongoDB and how, or should I stop trying and move to another DB?

I am already using mongoose, but I am also open to using native MongoDB codes (such as aggregate, $lookup etc.)



from MongoDB - Structuring and querying models and schema for many to many relationships

No comments:

Post a Comment