Schema Design Laboratory Information Management System

Working with Data
1

Hello guys, iam working on the design of a LIMS database . I should implement a simplified version, which is more common with a Booking System. So every user should be able to Book a resource ( Classroom, Waffer Machine etc). Every user should be able to see its Bookings and the admin should be able to see all bookings per user. The admin should also be able to see Bookings per Resource, and of course the web app should be able to retrieve booked dates of a specific resource in order to prevent overlaped bookings for the same resource. So my schema is this:

Users {
_Id: ObjectId
name: String
email: String
hashed_password: String
phone: String
role: String, enum:[“admin”,“user”]
}

Bookings{
_Id: ObjectId
userID: reference User
date_started:Date
date_finished:Date
Project_title: String
Project_description: String
total_cost: Number
}

Resources{
_Id: ObjectId
name: String
status: String, enum [“available”,“reserved”]
cost_per_day: Number
photoURL: String
reservation: [
BookingID: reference Booking
date_started: Date
date_finished: Date
}

I would like to hear your opinions, if this schema satisfies both the functional requirements and anti-patterns hints, like unbounded arrays etc

2

Hello @petridis_panagiotis, welcome to the MongoDB Community forum!

In general, I considered these two factors - the amount of data and the kind of queries. These specify how easily you can access the data using simple queries.

My suggestions are based upon:

  • A limited sized application and its data (not many resources and bookings).
  • The queries you had specified in your question post.

I am going with two collections - the users and the resources.

Users:

users: 
  _Id: ObjectId,
  name: String,
  ..., // other info
  role: String, enum: [ “admin”, “user” ],
  bookings: [
     { booking_id: <>, booking_date: <>, from_date: <>, to_date: <>, resource_id: <>, resource_name: <>, project_details: { ... }, total_cost: <> },
     { ... },
     ...
  ]

As you see I have a bookings array field with booking information. Each booking has its own unique id (the booking_id), the resource_id, the from and to booking dates, and other details.

Your queries on users are:

  • User should be able to see his/her Bookings
  • Admin should be able to see all bookings per user

This model will allow query the users collection to perform both the queries - without accessing another collection. Also, note that we are including the resources collection document fields resource_id and resource_name for each booking; this is some data duplication and I think it is tolerable.

Resources:

resources:
  _Id: ObjectId,
  name: String,
  status: String, enum [ “available”, “reserved” ] ,
  cost_per_day: Number,
  photoURL: String,
  reservations: [
    { user_id: <user reference>, booking_id: <booking reference> },
    { ... },
     ...
  ]

Note the reservations array field - it stores each bookings reference and its users reference. This is to fetch the relevant user and booking details for queries.

Your queries on resources are:

  1. Admin should also be able to see Bookings per Resource
  2. The web app should be able to retrieve booked dates of a specific resource in order to prevent overlapped bookings for the same resource.

Both the queries will do an aggregation “join” operation (uses $lookup stage) on the resources and users collections.

Bookings:

With this model, when a new booking is created you will be creating booking data and updating both the users and resources collections.

The bookings as a separate collection can be useful where you have specific queries on this collection only - for example, get all the bookings in the last one month.