Database architecture

Database architecture

PostgreSQL is a powerful, open source object-relational database.

Below is the Entity Relationship diagram for Powerboard.

Tables

User

This table stores data related to the User. It has One To Many (user -> user_team.user) relationship with User Team table.

Column Name	Type	Description
username	string	stores name of a user
password	string	stores encrypted password
email	string	stores email of a user

AD Center

This table stores list of AD Centers.

Column Name	Type	Description
name	string	stores name of a AD Center

Team Status

This table stores status of teams.

Column Name	Type	Description
status_name	string	stores name of a status
status_description	string	stores description of a status

Team

This Table contains necessary data of team.

Team has Many To One relationship with AdCenter and Team Status. It has One To Many relationship with User Team (team -> UserTeam.team).

Column Name	Type	Description
name	string	stores name of a team
team_code	string	stores unique team key
project_key	string	stores unique project identification key
logo	string	stores logo path of a team
status_update	boolean	it defines whether status is updated or needs to update
ad_center_id	string	stores id of AD Center
team-status_id	string	stores id of team status
is_team_configured	boolean	it tells that team is configured or not

Privileges

It stores privileges for all users.

Column Name	Type	Description
privilege_name	string	stores name of a privilege
privilege_description	string	stores description of a privilege

User Role

It stores roles of user. It has Many To Many relationship with User Role Privilege.

Column Name	Type	Description
role_name	string	stores name of a role
role_description	string	stores description of a role

User Role Privilege

This is a mapping table which connects user role and user privilege.

Column Name	Type	Description
role_id	string	stores id of a user role
privilege_id	string	stores id of a user privilege

User Session

This table stores last checked-in project and reset password details of a user.

Column Name	Type	Description
user_id	string	stores id of user
is_password_changed	boolean	Boolean to define whether the user changed password or not
last_checked_project	string	stores id of last checked-in team with respect to user

User Team

This table maps the user with team and role. It has Many To One relationships with User, Team and User Role.

Column Name	Type	Description
user_id	string	stores id of a user
team_id	string	stores id of a team
user_role_id	string	stores id of a user role

Team Spirit Median

This table stores survey details of team spirit. It has Many To One relation with Team.

Column Name	Type	Description
survey_median	string	stores data related to the feedback
start_date	string	stores timestamp of start date of a team spirit survey
end_date	string	stores timestamp of end date of a team spirit survey
survey_code	string	stores unique identification of team spirit survey
team_name	string	stores name of a team

Links Category

This table stores all the categories of links.

Column Name	Type	Description
title	string	stores title of a link category

Team Links

This table stores details of team link. It has Many To One relation with team and links category.

Column Name	Type	Description
link_name	string	stores name of a link
link	string	stores url of a link
link_title	string	stores id of link category
team_id	string	stores id of a team

Code Quality Snapshot

This table stores key parameters of sonar report. It has Many To One relation with Team.

Column Name	Type	Description
bugs	string	stores number of bugs
code_smell	string	stores value of total code smells
code_coverage	string	stores percentage of code tested
status	string	stores status of code quality
snapshot_time	string	stores timestamp of report created for code quality
team_id	string	stores id of a team

Scheduler Config

This table stores aggregation link details of a team. It has Many To One relation with Team.

Column Name	Type	Description
name	string	stores name of third party service
url	string	stores url path of third party service
start_date	string	stores timestamp of start time of third party service
is_active	string	Boolean to define this third party service is active or not
aggregation_frequency	string	stores value which indicates frequency of call
team_id	string	stores id of a team

Visibility

This table stores authorization details related to the team. It has One To One relation with Team.

Column Name	Type	Description
daily_meeting	boolean	stores boolean value whether daily meeting links are accessible or not
team_link	boolean	stores boolean value whether team links are accessible or not
images	boolean	stores boolean value whether images accessible or not
videos	boolean	stores boolean value whether videos are accessible or not
visibility_team_id	string	stores id of a team

Files

This table stores file name and its album id. It has Many To One relation with Multimedia.

Column Name	Type	Description
file_name	string	stores name of file
multimedia_album	string	stores id of multimedia album

Multimedia

This table stores path of file and its presence in slideshow of a team. it has One To Many relation with Files and Many To One relation with Team.

Column Name	Type	Description
album_name	string	stores name of a album
file_name	string	stores name of a file
in_slideshow	string	identifies whether the album is added to slideshow or not
multimedia_team_id	string	stores id of a team

Sprint Status

This table stores status of sprints.

Column Name	Type	Description
status	string	stores status of a sprint

Sprint Work Unit

This table stores work units of sprint.

Column Name	Type	Description
work_unit	string	stores work unit of a sprint

Sprint

This table stores necessary details of sprint for a team. It has Many To One relation with Sprint Status, Sprint Work Unit and Team.

Column Name	Type	Description
sprint_number	string	stores unique identification number of a sprint
start_date	string	stores timestamp of start date of a sprint
end_date	string	stores timestamp of end date of a sprint
status	string	helps to identify the sprint is running or completed
team_id	string	stores work unit of a sprint
work_unit	string	stores if of a sprint work unit

Client status

This table stores client satisfaction rating of a sprint. It has One To One relation with Sprint.

Column Name	Type	Description
client_rating	string	stores value of client satisfaction
sprint_id	string	stores id of sprint

Team Spirit

This table stores team spirit rating of a sprint. It has One To One relation with Sprint.

Column Name	Type	Description
team_spirit_rating	string	stores value of team spirit
sprint_id	string	stores id of sprint

Sprint Snapshot

This table stores timestamp of snapshot of a sprint. It has Many To One relation with Sprint.

Column Name	Type	Description
date_time	string	stores timestamp of sprint details generation
sprint_id	string	stores id of sprint

Sprint Metric

This table stores all the metrics of a sprint.

Column Name	Type	Description
name	string	stores name of sprint metric

Sprint Snapshot Metric

This table stores value and maps snapshot and metric of a sprint. It has Many To One relation with Sprint Snapshot and Sprint Metric.

Column Name	Type	Description
value	string	stores number of sprint snapshot metric value
snapshot_id	string	stores id of sprint snapshot
metric_id	string	stores id of sprint metric