📚 Database Design Documentation

Overview

This section provides an overview of the database design for the system, outlining the entities and their relationships within the system, along with the schema definitions.

---

📌 Entities

The system comprises the following core entities:

Entity	Description
Venue	Stores venue information, such as name, block, capacity, equipment, images, status, and abbreviation.
Users	Represents the registered users, with attributes like first name, last name, email, registration number, course, phone number, and role (employee/admin).
Order	Contains the details of the venue bookings, including the venue info (name, block, capacity, equipment, images, status), along with the user ID who made the booking.
Logs	Logs user actions in the system, captured using a logging package (e.g., Winston) rather than a model.

---

📌 Define Schema Design

This section outlines the schema design for each entity in the database:

1️⃣ Venue Schema

The Venue entity will store details about each venue in the system.

{
  "_id": ObjectId("..."),
  "name": "Conference Room A",
  "block": "B",
  "capacity": 100,
  "equipment": ["Projector", "Speakers"],
  "images": ["image1.jpg", "image2.jpg"],
  "status": "available",  // booked, available, etc.
  "abbreviation": "CRA",
  "createdAt": ISODate("2025-02-20T12:00:00Z"),
  "updatedAt": ISODate("2025-02-20T12:00:00Z")
}

2️⃣ Users Schema

The Users entity captures information about the users of the system, including their personal details and role.

{
  "_id": ObjectId("..."),
  "firstName": "John",
  "lastName": "Doe",
  "email": "john.doe@example.com",
  "registrationNumber": "12345",
  "course": "Computer Science",
  "phoneNumber": "123-456-7890",
  "role": "employee",  // admin or employee
  "createdAt": ISODate("2025-02-20T12:00:00Z"),
  "updatedAt": ISODate("2025-02-20T12:00:00Z")
}

3️⃣ Order Schema

The Order schema links a user to a venue booking, with details about the venue and booking status.

{
  "_id": ObjectId("..."),
  "venueId": ObjectId("..."),  // Reference to the Venue document
  "userId": ObjectId("..."),  // Reference to the User document
  "status": "booked",  // booked, cancelled, pending
  "bookingDate": ISODate("2025-02-20T14:00:00Z"),
  "createdAt": ISODate("2025-02-20T12:00:00Z"),
  "updatedAt": ISODate("2025-02-20T12:00:00Z")
}

4️⃣ Logs Schema

For logging user activities, you’ll be using the Winston package for logging. Instead of a database schema, logs will be handled by Winston and stored in files or a centralized logging service.

Note:

For logging purposes, we will not use a database model but will rely on Winston for capturing logs. The logs will be stored in files or integrated with services like AWS CloudWatch, Loggly, or Elasticsearch for easier searching and monitoring.

---

📌 Relationships Between Entities

• Users can have many Orders (One-to-Many relationship).
• Orders reference a Venue (Many-to-One relationship).
• Venue can be linked to multiple Orders, but each order is for one venue.

---

📌 Schema Considerations & Constraints

• Data Validation:
• Ensure that fields like email, phone number, and registration number are unique.

• Validate status fields to restrict them to specific values (e.g., booked, available).

• Soft Deletes:

• Instead of hard-deleting, consider adding an isDeleted field to mark entities for deletion (e.g., in the User schema).

• Indexes:

• Create indexes for frequently queried fields like email, venueId, and userId to speed up lookups.

---

📌 Logging with Winston

Logs are a crucial part of monitoring system usage and debugging issues. Here's an example configuration for Winston logging:

const winston = require('winston');

// Create a logger instance
const logger = winston.createLogger({
  level: 'info',
  format: winston.format.json(),
  transports: [
    new winston.transports.File({ filename: 'logs/error.log', level: 'error' }),
    new winston.transports.File({ filename: 'logs/combined.log' }),
    new winston.transports.Console({ format: winston.format.simple() })
  ]
});

// Example of logging an error
logger.error('This is an error log');

// Example of logging an info message
logger.info('User John Doe has successfully booked a venue.');

---

📌 Indexing Strategy for Performance

Collection	Field	Type	Purpose
users	email	Unique Index	To prevent duplicate emails and enable fast lookup
orders	userId	Index	To quickly find all orders by a specific user
venue	status	Index	To quickly find available venues
logs	timestamp	Index	To efficiently query logs by time

---

📌 Best Practices

• Normalization vs Denormalization:
  For MongoDB, we use a denormalized approach to embed related documents when it makes sense (e.g., embedding venue info in orders).

• Data Duplication:
  Although some data duplication might occur, it’s important for performance reasons in MongoDB to reduce the need for complex joins.

• Backup & Recovery:
  Implement a strategy for database backups (daily or weekly) and ensure that your data is resilient to failures.

---

📌 Future Considerations

As the system evolves, here are a few potential improvements:

• Adding a messaging queue for handling asynchronous operations like email notifications and processing payments.
• Implementing user authentication (JWT tokens) to securely manage users and their roles.
• Considering horizontal scaling and sharding in MongoDB for performance as the user base grows.

---