# Design the Initial ERD of ScholarX
**URL:** https://sef.discourse.group/t/design-the-initial-erd-of-scholarx/72
**Category:** ScholarX
**Created:** [May 13, 2020, 2:13am UTC](https://sef.discourse.group/t/design-the-initial-erd-of-scholarx/72 "2020-05-13T02:13:36Z")
**Posts on this page:** 20
**Page:** 1
### Author:  [@Sumudu\_Mohottige](https://sef.discourse.group/u/Sumudu_Mohottige)
#### Post date: [May 13, 2020, 2:13am UTC](https://sef.discourse.group/t/design-the-initial-erd-of-scholarx/72/1 "2020-05-13T02:13:36Z")
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
---
### Author:  [@jaye](https://sef.discourse.group/u/jaye)
#### Post date: [May 23, 2020, 8:02am UTC](https://sef.discourse.group/t/design-the-initial-erd-of-scholarx/72/2 "2020-05-23T08:02:20Z")
Hi @Sumudu_Mohottige,
How are you going with this? any blockers? or any help you need?
Thanks.
---
### Author:  [@Saumiyaa\_P1](https://sef.discourse.group/u/Saumiyaa_P1)
#### Post date: [May 25, 2020, 6:41pm UTC](https://sef.discourse.group/t/design-the-initial-erd-of-scholarx/72/3 "2020-05-25T18:41:52Z")
Let me know if there’s anything I can help with here! (Any info specific to ScholarX you might need?)
@jaye
---
### Author:  [@jaye](https://sef.discourse.group/u/jaye)
#### Post date: [May 26, 2020, 4:06am UTC](https://sef.discourse.group/t/design-the-initial-erd-of-scholarx/72/4 "2020-05-26T04:06:51Z")
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?
---
### Author:  [@Sumudu\_Mohottige](https://sef.discourse.group/u/Sumudu_Mohottige)
#### Post date: [May 26, 2020, 3:57pm UTC](https://sef.discourse.group/t/design-the-initial-erd-of-scholarx/72/5 "2020-05-26T15:57:24Z")
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.
---
### Author:  [@jaye](https://sef.discourse.group/u/jaye)
#### Post date: [May 26, 2020, 4:40pm UTC](https://sef.discourse.group/t/design-the-initial-erd-of-scholarx/72/6 "2020-05-26T16:40:30Z")
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](https://developer.linkedin.com/docs/fields/full-profile) 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
---
### Author:  [@akshika47](https://sef.discourse.group/u/akshika47)
#### Post date: [May 27, 2020, 9:20pm UTC](https://sef.discourse.group/t/design-the-initial-erd-of-scholarx/72/7 "2020-05-27T21:20:19Z")
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?
---
### Author:  [@YohanAvishke](https://sef.discourse.group/u/YohanAvishke)
#### Post date: [July 24, 2020, 3:36am UTC](https://sef.discourse.group/t/design-the-initial-erd-of-scholarx/72/8 "2020-07-24T03:36:20Z")
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](https://sef.discourse.group/t/design-forum-scholarx-platform-finalising-timeline)
Assigned to: @miluckshan-j, @YohanAvishke
---
### Author:  [@YohanAvishke](https://sef.discourse.group/u/YohanAvishke)
#### Post date: [July 24, 2020, 3:42am UTC](https://sef.discourse.group/t/design-the-initial-erd-of-scholarx/72/9 "2020-07-24T03:42:08Z")
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_
---
### Author:  [@shyamal](https://sef.discourse.group/u/shyamal)
#### Post date: [July 24, 2020, 3:55am UTC](https://sef.discourse.group/t/design-the-initial-erd-of-scholarx/72/10 "2020-07-24T03:55:07Z")
Cheers guys, let’s keep the ball rolling✌️
---
### Author:  [@YohanAvishke](https://sef.discourse.group/u/YohanAvishke)
#### Post date: [July 26, 2020, 6:54am UTC](https://sef.discourse.group/t/design-the-initial-erd-of-scholarx/72/11 "2020-07-26T06:54:05Z")
# ERD design call
When: 2020-07-26T03:30:00Z → 2020-07-26T05:00:00Z
Where:
> **[Meet](https://meet.google.com/unsupported?meetingCode=cdw-hthd-nkf&ref=https:%2F%2Fmeet.google.com%2Fcdw-hthd-nkf)**
>
> Real-time meetings by Google. Using your browser, share your video, desktop, and presentations with teammates and customers.
Attendees: @YohanAvishke @Gravewalker @miluckshan-j
## Diagram
Editable file:
> **[ScholarX ERD.drawio](https://drive.google.com/file/d/1L7OXu9KQkxEuWSp-ke7vqSQlS9xaFpPY/view?usp=sharing)**
>
> Google Drive file.
## 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?
---
### Author:  [@Gravewalker](https://sef.discourse.group/u/Gravewalker)
#### Post date: [July 26, 2020, 8:52am UTC](https://sef.discourse.group/t/design-the-initial-erd-of-scholarx/72/12 "2020-07-26T08:52:50Z")
> [@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”
---
### Author:  [@jaye](https://sef.discourse.group/u/jaye)
#### Post date: [July 26, 2020, 1:04pm UTC](https://sef.discourse.group/t/design-the-initial-erd-of-scholarx/72/13 "2020-07-26T13:04:47Z")
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.
---
### Author:  [@YohanAvishke](https://sef.discourse.group/u/YohanAvishke)
#### Post date: [July 26, 2020, 1:13pm UTC](https://sef.discourse.group/t/design-the-initial-erd-of-scholarx/72/14 "2020-07-26T13:13:45Z")
+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.
---
### Author:  [@YohanAvishke](https://sef.discourse.group/u/YohanAvishke)
#### Post date: [July 26, 2020, 1:20pm UTC](https://sef.discourse.group/t/design-the-initial-erd-of-scholarx/72/15 "2020-07-26T13:20:31Z")
Updated Diagram
Editable file:
> **[ScholarX ERD.drawio](https://drive.google.com/file/d/1L7OXu9KQkxEuWSp-ke7vqSQlS9xaFpPY/view?usp=sharing)**
>
> Google Drive file.
---
### Author:  [@YohanAvishke](https://sef.discourse.group/u/YohanAvishke)
#### Post date: [July 26, 2020, 8:40pm UTC](https://sef.discourse.group/t/design-the-initial-erd-of-scholarx/72/16 "2020-07-26T20:40:29Z")
## Physical ERD model
### Diagram
Editable file:
> **[ScholarX ERD.drawio](https://drive.google.com/file/d/1L7OXu9KQkxEuWSp-ke7vqSQlS9xaFpPY/view)**
>
> Google Drive file.
Please check if the variable types are correct for the columns. And any other suggestions to change the tables will be really helpful.
---
### Author:  [@akshika47](https://sef.discourse.group/u/akshika47)
#### Post date: [July 27, 2020, 7:14am UTC](https://sef.discourse.group/t/design-the-initial-erd-of-scholarx/72/17 "2020-07-27T07:14:58Z")
Apologise if this is an obvious question. Where would the application of the mentee fit in?
---
### Author:  [@YohanAvishke](https://sef.discourse.group/u/YohanAvishke)
#### Post date: [July 27, 2020, 7:37am UTC](https://sef.discourse.group/t/design-the-initial-erd-of-scholarx/72/18 "2020-07-27T07:37:29Z")
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?
---
### Author:  [@akshika47](https://sef.discourse.group/u/akshika47)
#### Post date: [July 27, 2020, 8:40am UTC](https://sef.discourse.group/t/design-the-initial-erd-of-scholarx/72/19 "2020-07-27T08:40:04Z")
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?
---
### Author:  [@jaye](https://sef.discourse.group/u/jaye)
#### Post date: [July 28, 2020, 1:59pm UTC](https://sef.discourse.group/t/design-the-initial-erd-of-scholarx/72/20 "2020-07-28T13:59:45Z")
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](https://sef.discourse.group/t/designing-wireframes-for-scholarx-platform/148)
Also, we need to store the set of questions somewhere in the DB.
@akshika47 the questions should be mentor specific right?
[Next page](https://sef.discourse.group/t/design-the-initial-erd-of-scholarx/72.md?page=2)