GraphQL Database Schema

Core Tables

Assigned

• Purpose: Placeholder type, originally might have been intended to manage assignments, but lacks fields for practical use.

• Fields:

  • id: Unique identifier for the assignment record.

Users

• Purpose: Represents individuals who can log into and interact with the system, including both project managers and regular users.

• Fields:

  • id: Unique identifier for each user.

  • FirstName: User's first name.

  • LastName: User's last name.

  • Email: User's email address, formatted as an AWS email type for validation.

  • IsProjectManager: Boolean flag indicating whether the user has project manager privileges.

  • IsAdmin: Boolean flag indicating whether the user has administrative privileges.

Projects

• Purpose: Represents individual projects within the system, containing all relevant details and associated tasks.

• Fields:

  • id: Unique identifier for each project.

  • Project_name: Name of the project.

  • planned_StartDate: The planned start date of the project.

  • planned_completion_date: The planned completion date of the project.

  • actual_start_date: Actual start date of the project, capturing when work began.

  • Description: A detailed description of the project.

  • Tasks: A list of tasks associated with the project through a many-to-many relationship, managed by ProjectTasks.

SkillLevels

• Purpose: Defines various skill levels that can be assigned to tasks or used to qualify users.

• Fields:

  • id: Unique identifier for each skill level.

  • Name: Name of the skill level.

  • SkillRank: A numeric ranking that indicates the skill level's order or precedence.

  • SkillCode: A unique code to represent the skill level succinctly.

Tools

• Purpose: Manages tools that might be required for tasks or projects.

• Fields:

  • id: Unique identifier for each tool.

  • Name: Name of the tool.

  • ToolCode: A unique code representing the tool.

  • AssignedTO: Indicates who the tool is currently assigned to.

  • Available: Boolean status indicating whether the tool is available for use.

TaskTypes

• Purpose: Defines types of tasks that can be created within the system.

• Fields:

  • id: Unique identifier for each task type.

  • Name: Name of the task type.

  • TaskType_code: A unique code representing the task type.

Tasks

• Purpose: Represents individual tasks that can be included in multiple projects.

• Fields:

  • id: Unique identifier for each task.

  • Name: Name of the task.

  • Description: A detailed description of what the task entails.

  • skillsID: A reference to the skills required for the task.

  • TaskTypes: A reference to the type of task.

  • Projects: A list of project-task associations through the ProjectTasks type.

ProjectTasks

• Purpose: Acts as a junction table in the many-to-many relationship between projects and tasks. Each record represents an instance of a task within a specific project, including unique attributes for that instance.

• Fields:

  • id: Unique identifier for each project-task relationship.

  • taskID: Reference to the associated task.

  • projectID: Reference to the associated project.

  • status: Current status of the task within the project.

  • startDate: Start date of the task within the project.

  • endDate: End date or completion date of the task within the project.

Skills

• Purpose: Manages specific skills that can be associated with tasks or required by roles.

• Fields:

  • id: Unique identifier for each skill.

  • Name: Name of the skill.

  • Description: A detailed description of the skill.

  • rolesID: A reference to the roles that require this skill.

Roles

• Purpose: Defines roles within the organization, which could be tied to specific tasks, projects, or departments.

• Fields:

  • id: Unique identifier for each role.

  • Name: Name of the role.

  • Description: Description of what the role entails.

  • Code_Role: A unique code representing the role.

  • divisionsID: Reference to the division that the role is part of.

Divisions

• Purpose: Represents divisions within the organization, possibly aligning with specific areas of expertise or departments.

• Fields:

  • id: Unique identifier for each division.

  • Name: Name of the division.

  • Symbol_Division: A symbol or short code representing the division.

  • Description: A detailed description of the division's focus.

  • guildsID: Reference to the guild that the division is part of.

Guilds

• Purpose: Represents the highest organizational structure within the company, potentially encapsulating multiple divisions.

• Fields:

  • id: Unique identifier for each guild.

  • Name: Name of the guild.

  • Description: A detailed description of what the guild encompasses.

  • Symbol_Guild: A symbol or short code representing the guild.

Assigned

• Purpose: Placeholder type, originally might have been intended to manage assignments, but lacks fields for practical use.

• Fields:

  • id: Unique identifier for the assignment record.

This breakdown should help you understand each type within your GraphQL schema, how they are related, and their role in the project management system you are building.

Sample Schema Code (GraphQL)

type Assigned @model @auth(rules: [{allow: public}]) {
  id: ID!
}

type Users @model @auth(rules: [{allow: public}]) {
  id: ID!
  FirstName: String!
  LastName: String!
  Email: AWSEmail!
  IsProjectManager: Boolean!
  IsAdmin: Boolean!
}

type Projects @model @auth(rules: [{allow: public}]) {
  id: ID!
  Project_name: String
  planned_StartDate: AWSDate!
  planned_completion_date: AWSDate
  actual_start_date: AWSDateTime
  Description: String!
  Tasks: [ProjectTasks] @connection(keyName: "byProject", fields: ["id"])
}

type SkillLevels @model @auth(rules: [{allow: public}]) {
  id: ID!
  Name: String!
  SkillRank: Int!
  SkillCode: String!
}

type Tools @model @auth(rules: [{allow: public}]) {
  id: ID!
  Name: String!
  ToolCode: String!
  AssignedTO: String
  Available: Boolean
}

type TaskTypes @model @auth(rules: [{allow: public}]) {
  id: ID!
  Name: String!
  TaskType_code: String!
}

type Tasks @model @auth(rules: [{allow: public}]) {
  id: ID!
  Name: String!
  Description: String!
  skillsID: ID! @index(name: "bySkills")
  TaskTypes: TaskTypes @hasOne
  Projects: [ProjectTasks] @connection(keyName: "byTask", fields: ["id"])
}

type ProjectTasks @model @auth(rules: [{allow: public}]) {
  id: ID!
  taskID: ID! @connection(fields: ["taskID"])
  projectID: ID! @connection(fields: ["projectID"])
  status: String
  startDate: AWSDate
  endDate: AWSDate
  Task: Tasks @belongsTo(fields: ["taskID"])
  Project: Projects @belongsTo(fields: ["projectID"])
}

type Skills @model @auth(rules: [{allow: public}]) {
  id: ID!
  Name: String!
  Description: String!
  rolesID: ID! @index(name: "byRoles")
  Tasks: [Tasks] @hasMany(indexName: "bySkills", fields: ["id"])
}

type Roles @model @auth(rules: [{allow: public}]) {
  id: ID!
  Name: String!
  Description: String!
  Code_Role: String!
  divisionsID: ID! @index(name: "byDivisions")
  Skills: [Skills] @hasMany(indexName: "byRoles", fields: ["id"])
}

type Divisions @model @auth(rules: [{allow: public}]) {
  id: ID!
  Name: String!
  Symbol_Division: String!
  Description: String!
  guildsID: ID! @index(name: "byGuilds")
  Roles: [Roles] @hasMany(indexName: "byDivisions", fields: ["id"])
}

type Guilds @model @auth(rules: [{allow: public}]) {
  id: ID!
  Name: String!
  Description: String!
  Symbol_Guild: String!
  GuildstoDivision: [Divisions] @hasMany(indexName: "byGuilds", fields: ["id"])
}

Additional Tables for Consideration

Resource Allocation Table

• Purpose: Manages the allocation of non-human resources such as tools, venues, or equipment necessary for tasks.

• Fields: Resource ID, Resource Name, Description, Availability Status, Assigned To (Task ID or Project ID).

Audit Log Table

• Purpose: Tracks changes to critical data within the system, providing a historical record for security, compliance, and troubleshooting.

• Fields: Log ID, User ID, Action Type (create, update, delete), Timestamp, Entity Type (Task, Project, etc.), Entity ID, Change Description.

User Activity Table

• Purpose: Records user activities within the system, which can be used for analytics, user engagement studies, and operational monitoring.

• Fields: Activity ID, User ID, Activity Type, Activity Description, Timestamp.

Skills and Competencies Table

• Purpose: Further detail user skills and competencies to enable better matching of project tasks to member capabilities.

• Fields: Skill ID, Skill Name, Description, Required for Task ID (link to Tasks Table).

Dependency Table

• Purpose: Manages dependencies between tasks to ensure that tasks are completed in the correct order.

• Fields: Dependency ID, Preceding Task ID, Subsequent Task ID, Type of Dependency (start-to-start, finish-to-finish, etc.).

Project Phases Table

• Purpose: Breaks projects into phases to manage large projects more effectively and provide milestone checkpoints.

• Fields: Phase ID, Project ID, Phase Name, Description, Start Date, End Date, Status.

Budget Management Table

• Purpose: Tracks financial resources allocated and spent on projects, essential for managing project costs and financial planning.

• Fields: Budget ID, Project ID, Allocated Amount, Spent Amount, Forecasted Spend, Notes.

Stakeholder Table

• Purpose: Manages information about stakeholders involved in the projects, such as sponsors, clients, or external partners.

• Fields: Stakeholder ID, Name, Contact Info, Role, Notes, Project ID (link to Projects Table).

Notifications Table

• Purpose: Stores notification settings and logs for automated system alerts related to task updates, project milestones, or system changes.

• Fields: Notification ID, User ID, Notification Type, Message, Status (sent, pending, failed), Timestamp.

Feedback and Surveys Table

• Purpose: Collects feedback from users about the system usage experience or specific projects, facilitating continuous improvement.

• Fields: Feedback ID, User ID, Project ID, Feedback Text, Rating, Timestamp.