The following is the initial version of the database design suggested for the ScholarX platform.
Please note that this ERD doesn’t include all the attributes related to the entities and it only covers the initial phase of the platform where the main aim of the platform is to pair mentees with suitable mentors.
The following is a simple explanation of the purpose of each table mentioned in the ERD.
Usage: To map the firebase unique id to the user id generated by the platform
With every new user registration as an admin, a mentor, or as a mentee in the platform, an entry will be created in this table with the generated user id and the user type along with the unique id extracted from the firebase user token.
This data will be used to authenticate users’ and uniquely identify each user’s role in the backend.
Usage: To store details of the administrations
With every new user registration as an administrator, the table will be populated with the required details.
Usage: To store data of the programmes
This table enables the platform to run several programmes at once.
Each programme has a status which enables the administrators to change phases in the active programmes.
Usage: To store details of the mentors
With every new user registration as a mentor, the table will be populated with the required details.
Each mentor will have a status such as (P: Pending, A: Approved)
Usage: To store skills of mentors
This table enables the mentors to maintain a profile with a set of skills.
Each skill has a status that allows the administrators to review and approve.
The data from this table may be used to categorize the mentors based on their expertise.
Usage: To map mentors with programmes
The table will be populated each time when a mentor registers to an ongoing programme.
This table enables the mentor to take part in multiple mentoring programmes at once.
Each record has a status which allows the administrators the power to approve a mentor mentoring in a programme.
Usage: To store details of the mentees
With every new user registration as a mentee, the table will be populated with the required details.
Usage: To map mentors and mentees and to store the mentoring process data
This table will be populated each time when a mentee applies for a mentor.
Each log entry will have a status which will store the status of the mentoring process such as (Draft, Pending, Accepted, Declined)
The suggested ERD is based on my exposure to the requirements of the proposed system and this is just the initial design. Therefore feel free to let me know if you have any feedback or suggestions.
What about giving a feature to sync with their Linkedin profile? Then we can retrieve those data and store it on our DB without asking the user to fill data. We can use the same mentor table or a separate table (ex: mentor_meta) and store that in a single field with JSON type or whatever.
If it is possible to leave out the created_time, updated_time until we finalise the ERD as I understand it is common throughout all the tables and does not add much value at this stage. But if it is automatic, just ignore this comment.
I like the idea of @jaye, but I am sceptical about it expanding the scope of the first phase(something to discuss during the Saturday call). For the time being, we can have a basic description within the mentor class instead of the mentor_skill table. Just a suggestion.
Mentor program log should have attributes like available slots which varies on the mentor.
A better name for mentoring_log class would be application
If we are going with our existing web interface, programme class should have a variable to hold the link to FAQs which are relative to that specific program.
These are my thoughts.
With regard to functionalities that each type of user has(admin, mentee and mentor). Are we creating a separate UML diagram for that?
token_id is the authentication id of the Firebase login.
end_time is the time when a program will end.
Below two tables are created as an attempt to create a timeline for past states and for upcoming future states.
Program lifecycle states are persisted here.
There should be a way to manage these states(create, update, remove) in the admin dashboard.
When a program is created rows equal to the number of rows in the state will be created here. And for a given time a single program_state will be active(is_active: true).
An admin will have to provide end_time for each of program_state of a single program.
state: approved, disapproved(as a mentor of the program).
state: approved, disapproved(as a mentee for a mentor).
Below some of the relationships are explained.
mentor-mentee relationship: one(mandatory) to many(optional)
Users cannot apply as mentors to the program instead they have to apply to the mentors. So each time a user is applying for a mentor they will have a new entry in the mentee table. As a result, each entry in the mentee table should always have a referring mentor.
Should we have a mentee-mentor application table?(instead of having state and application-related data in the mentee table itself)
Should we have a mentor-program application table(instead of having state and application-related data in the mentor table itself)?
What are the missing mentee/mentor application-related data?
We can move the state to the program as a string if we are not planning to do calculations with the dates. Currently the state changing is a manual task, and we can display the timeline on the HTML page.
Btw, I think we don’t need the state table anyway since the states are just pre-defined enum which doesn’t/can’t change with the time.
Hi @akshika47, to apply mentees will provide their resume link and Linkedin profile. These data is stored in the user table.
Do we need any other data from mentees when they are applying for the mentors?
another PDF link would do. Just like the last ScholarX round. There are different questions that we put in for students to answer. So we can ask them to create a PDF with the answers and provide the link to that? what do you think?