Design the Initial ERD of ScholarX

[Sumudu_Mohottige]

As per the discussions held and the requirements gathered, I will be designing the initial Entity-Relationship Diagram related to the project.

On completion of the task, the following will be identified;

1. The entities

2. The relationships between the entities

3. The required attributes related to the entities

[jaye]

Hi @Sumudu_Mohottige,
How are you going with this? any blockers? or any help you need?
Thanks.

[Saumiyaa_P1]

Let me know if there’s anything I can help with here! (Any info specific to ScholarX you might need?)
@jaye

[jaye]

Thanks @Saumiyaa_P1!
Btw, me and @Sumudu_Mohottige , had a small discussion on this topic. @Sumudu_Mohottige could you please update this thread with the current progress?

[Sumudu_Mohottige]

Hi all,

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.

user_token

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.

admin

Usage: To store details of the administrations

• With every new user registration as an administrator, the table will be populated with the required details.

programme

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.

mentor

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)

mentor_skill

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.

mentor_programme_log

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.

mentee

Usage: To store details of the mentees

• With every new user registration as a mentee, the table will be populated with the required details.

mentoring_log

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.

Thank You.

[jaye]

Awesome!
Thanks for the detailed description @Sumudu_Mohottige!

Meanwhile, I went through Linkedin API and saw that we can get so many details of a user from his profile,
here are few:

• headline
• associations
• skills
• certifications
• educations
• courses
• volunteer

See Full Profile Fields | Linkedin developer network for the full list.

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.

@akshika47 @Minuri_Adasuriya @Saumiyaa_P1 @shyamal @DannyB @YohanAvishke

[akshika47]

This is a good start @Sumudu_Mohottige! Following are my comments.

• 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?

[YohanAvishke]

All database design and development discussion thread.
Please post your problems and solutions on the DB design and development on this thread.
This thread is created due to the discussions from: https://sef.discourse.group/t/design-forum-scholarx-platform-finalising-timeline

Assigned to: @miluckshan-j, @YohanAvishke

[YohanAvishke]

Initial design. Thankyou @shyamal for designing this diagram for us

Please note this is not a completed design just a diagram made as a starting point

[shyamal]

Cheers guys, let’s keep the ball rolling✌️

[YohanAvishke]

ERD design call

When: 2020-07-26T03:30:00Z → 2020-07-26T05:00:00Z
Where:

Meet

Attendees: @YohanAvishke @Gravewalker @miluckshan-j

Diagram

Editable file:

ScholarX ERD.drawio

Keypoints

• user
  • User is a constant for the whole platform.
  • token_id is the authentication id of the Firebase login.
  • user_type: admin,other.
• program
  • 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.

• state
  • Program lifecycle states are persisted here.
  • There should be a way to manage these states(create, update, remove) in the admin dashboard.
• program_state
  • 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.
• mentor
  • state: approved, disapproved(as a mentor of the program).
• mentee
  • 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.

Problems

1. Should we have a mentee-mentor application table?(instead of having state and application-related data in the mentee table itself)
2. Should we have a mentor-program application table(instead of having state and application-related data in the mentor table itself)?
3. What are the missing mentee/mentor application-related data?
4. Do we need a relationship between mentee-program?

[Gravewalker]

@YohanAvishke

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.

Small typing mistake here - Users cannot apply as “mentees”

[jaye]

Great job! Thanks guys!

Few suggestions,

• The linkdin URL can be moves to the user table
• 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.

[YohanAvishke]

+1 for the suggestions.
Should we move the cv_url to the users as well. I think this is common to both mentors and mentees.

[YohanAvishke]

Updated Diagram

Editable file:

ScholarX ERD.drawio

[YohanAvishke]

Physical ERD model

Diagram

Editable file:

ScholarX ERD.drawio

Please check if the variable types are correct for the columns. And any other suggestions to change the tables will be really helpful.

[akshika47]

Apologise if this is an obvious question. Where would the application of the mentee fit in?

[YohanAvishke]

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?

[akshika47]

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?

[jaye]

Yes @akshika47 . Let’s ask the user to provide a link.

I think it’s better to replace CV URL with the URL of the doc,
From: Designing Wireframes for ScholarX platform

Also, we need to store the set of questions somewhere in the DB.
@akshika47 the questions should be mentor specific right?