DMS Database Design

After analyzing the paper-based system used to handle academic registration and gathering the necessary requirements, the next step in the implementation process was the architectural design.

I used standard software modelling techniques such as Data Flow Diagrams (DFDs), Class Diagrams, Sequence Diagrams, and Entity Relationship Diagrams (ERDs) to model the system.

The ERD was particularly important because it translated the conceptual data model of my DMS into a database schema. The entities in the ERD became tables, while their attributes became fields/columns in those tables.

The main entities in my design are:

• User — Parent entity; also stores admin information.
• Student — Child entity inheriting from User.
• Staff — Child entity inheriting from User.
• Department
• Academic Session
• Registration — Junction table created from the many-to-many relationship between Student and Academic Session.
• Document — Stores records of documents uploaded by students.
• Required Document — Stores the master list of documents required by the faculty.
• Student Category — Categorizes students as fresher, sophomore, or finalist.
• Required Document Map Student Category — Junction table created from the many-to-many relationship between Required Document and Student Category.
• Notification

For the rebuild, I plan to use Prisma ORM in the application code to build and manage the database schema and handle communication between the application and the database.

Entity Relationships

The main relationships between my entities are:

• Many Students belong to One Department (M:1)
• Many Students register for Many Academic Sessions (M:N)
• One Academic Session has Many Registrations (1:M)
• One Student has Many Registrations (1:M)
• One Student uploads Many Documents (1:M)
• One Staff member reviews Many Documents (1:M)
• Many Student Categories map to Many Required Documents (M:N)

The last relationship is particularly important to the DMS. Each student category requires a specific subset of the master document list, while an individual required document can apply to multiple student categories.

The junction table, Required Document Map Student Category, therefore connects the two entities:

• One Student Category has Many mappings (1:M)
• One Required Document has Many mappings (1:M)

Similarly, notifications can be associated with the users who receive them:

• One Staff member receives Many Notifications (1:M)
• One Student receives Many Notifications (1:M)

Highlights of the Design

Role-based extension:
Instead of putting every role-specific attribute directly into the User table, I separated role-specific data into related tables for Student and Staff, while keeping the common user information in the parent User entity.

Composite Key:
The Registration table uses a composite primary key consisting of matric_no and sessionId. This ensures that the same student cannot have multiple registration records for the same academic session.

Referential Integrity:
Foreign keys ensure that relationships between tables remain consistent by requiring referenced values to correspond to existing records.

I also use cascading deletes on appropriate relationships involving the User entity so that related records can be removed automatically when their parent record is deleted, helping prevent orphaned records.

This is the database structure I'm starting with for the rebuild. As I implement it with Prisma, I'll likely encounter decisions that require me to revisit parts of the design.