Skip to content

Latest commit

 

History

History
49 lines (43 loc) · 3.2 KB

DESIGN.md

File metadata and controls

49 lines (43 loc) · 3.2 KB
Raw

Design of the Railway Reservation System

The source code of the railway reservation system is distributed across the following directories and files in the src directory:

  • main/
    • indexes.sql: Contains the indexes defined on the tables
    • operators.sql: Contains the user-defined operators
    • queries.sql: Contains the main queries(features) of the project
    • tables.sql: Contains the description of different tables along with proper integrity constraints
    • triggers.sql: Contains the triggers defined on the tables
    • types_and_extensions.sql: Contains the user-defined types and PostgreSQL extensions used
    • views.sql: Contains the views defined on the tables
  • roles/
    • dbAdmin.sql: Contains the privileges for the database administrator and dummy users defined in this group
    • passenger.sql: Contains the privileges for the passenger role
    • rls.sql: Contains the code for the implementation of Row Level Security
    • station_master.sql: Contains the privileges for the station master role
    • users.sql: Contains the privileges for the user role
  • tests/
    • railway_station.sql: Contains the test data for different railway stations
    • schedules.sql: Contains the test data for different schedules
    • users_and_bookings.sql: Contains the test data for different users and bookings
  • utils/utils.sql: Contains the utility functions used in the project
  • clean.sql: Contains the code for the clean up of the database
  • makefile.sql: Contains the code for the makefile for the project

Design Choices

  1. We would grant permissions to tables and views to specific roles only
  2. For the rest of the roles, we would grant execute permissions to the procedures and functions that we have defined as the main queries
  3. We are using SECURITY DEFINER to make sure that we don't have to explicitly grant permissions to the underlying views and tables used inside procedures and functions
  4. We are making sure that we are doing proper error handling inside these procedures and functions and they are safe to be called by the other roles with SECURITY DEFINER
  5. We are implementing Row Level Security to make sure that only the roles that are allowed to access the tables can access only the information related to them
  6. Currently, we are not properly handling the delay_time of a train, and it is just a field there but we are not considering it anywhere
  7. The passenger (user without an account) also needs to login to the database with credentials: 
    username: passenger
password: passenger

Restrictions on the Railway Reservation System

  • We are maintaining weekly schedules for our trains, so a train can have a maximum of 7 days of journey
  • We are having infinite number of platforms on a station
  • While adding the train details, the admin will provide us with the train schedule in sorted order
  • A ticket is for individual passengers only
  • A train will follow the same schedule every week
  • For each schedule we will have only one train
  • As passengers are miscellaneous, we will always create a new record for them in the passengers table