Database architecture

Database architecture

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

Below is the Entity Relationship diagram for Powerboard.

Tables

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

Column Name	Type	Description
name	string	stores name of a ADCenter

Team Status

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

Team

Team has Many To One relationship with AdCenter and Team Status. It has One To Many relationship with UserTeam (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

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

User Role

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

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

User Session

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

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

It has Many To One relation with Team.

Column Name	Type	Description
survey_median	string	==================================
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

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

Team Links

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

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

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

It has One To One relation with Team.

Column Name	Type	Description
daily_meeting	string	stores name of a link ======
team_link	string	stores url of a link===========
images	string	stores id of link category============
videos	string	stores id of a team================
visibility_team_id	string	stores id of a team

Files

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

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

Column Name	Type	Description
status	string	stores status of a sprint

Sprint Work Unit

Column Name	Type	Description
work_unit	string	stores work_unit of a sprint

Sprint

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

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

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

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

Column Name	Type	Description
name	string	stores name of sprint metric

Sprint Snapshot Metric

It has Many To One relation with Sprint Snapshot and Sprint Mentric.

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