Database architecture - devonfw-forge/powerboard-webapp Wiki

Database architecture

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

Below is the Entity Relationship diagram for Powerboard.

final_class_diagram

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
⚠️ **GitHub.com Fallback** ⚠️