On this page

On this page

Human resources database

Building an human resources database

Let’s look at the needs of an application used to manage Employees. The application Looks at employees, offices, tasks, and projects.

Table Definition

Example Setup

Table Definition
{
  "TableName": "electro",
  "KeySchema": [
    {
      "AttributeName": "pk",
      "KeyType": "HASH"
    },
    {
      "AttributeName": "sk",
      "KeyType": "RANGE"
    }
  ],
  "AttributeDefinitions": [
    {
      "AttributeName": "pk",
      "AttributeType": "S"
    },
    {
      "AttributeName": "sk",
      "AttributeType": "S"
    },
    {
      "AttributeName": "gsi1pk",
      "AttributeType": "S"
    },
    {
      "AttributeName": "gsi1sk",
      "AttributeType": "S"
    }
  ],
  "GlobalSecondaryIndexes": [
    {
      "IndexName": "gsi1pk-gsi1sk-index",
      "KeySchema": [
        {
          "AttributeName": "gsi1pk",
          "KeyType": "HASH"
        },
        {
          "AttributeName": "gsi1sk",
          "KeyType": "RANGE"
        }
      ],
      "Projection": {
        "ProjectionType": "ALL"
      }
    }
  ],
  "BillingMode": "PAY_PER_REQUEST"
}

Requirements

  1. As a Project Manager, I need to find all tasks and details on a specific employee.
  2. As a Regional Manager, I need to see all details about an office and its employees
  3. As an Employee, I need to see all my Tasks.
  4. As a Product Manager, I need to see all the tasks for a project.
  5. As a Client, I need to find a physical office close to me.
  6. As a Hiring manager, I need to find employees with comparable salaries.
  7. As HR, I need to find upcoming employee birthdays/anniversaries
  8. As HR, I need to find all the employees that report to a specific manager

Entities

For this example we will create multiple entities and relate them together through a Service.

Employee

The Employee entity represents a single Employee at the company.

import { Entity } from "electrodb";

const Employee = new Entity({
  model: {
    entity: "employee",
    version: "1",
    service: "taskapp",
  },
  attributes: {
    employee: {
      type: "string",
    },
    firstName: {
      type: "string",
    },
    lastName: {
      type: "string",
    },
    office: {
      type: "string",
    },
    title: {
      type: "string",
    },
    team: {
      type: ["development", "marketing", "finance", "product"] as const,
    },
    salary: {
      type: "string",
    },
    manager: {
      type: "string",
    },
    dateHired: {
      type: "string",
    },
    birthday: {
      type: "string",
    },
  },
  indexes: {
    employee: {
      pk: {
        field: "pk",
        composite: ["employee"],
      },
      sk: {
        field: "sk",
        composite: [],
      },
    },
    coworkers: {
      index: "gsi1pk-gsi1sk-index",
      collection: "workplaces",
      pk: {
        field: "gsi1pk",
        composite: ["office"],
      },
      sk: {
        field: "gsi1sk",
        composite: ["team", "title", "employee"],
      },
    },
    teams: {
      index: "gsi2pk-gsi2sk-index",
      pk: {
        field: "gsi2pk",
        composite: ["team"],
      },
      sk: {
        field: "gsi2sk",
        composite: ["title", "salary", "employee"],
      },
    },
    employeeLookup: {
      collection: "assignments",
      index: "gsi3pk-gsi3sk-index",
      pk: {
        field: "gsi3pk",
        composite: ["employee"],
      },
      sk: {
        field: "gsi3sk",
        composite: [],
      },
    },
    roles: {
      index: "gsi4pk-gsi4sk-index",
      pk: {
        field: "gsi4pk",
        composite: ["title"],
      },
      sk: {
        field: "gsi4sk",
        composite: ["salary", "employee"],
      },
    },
    directReports: {
      index: "gsi5pk-gsi5sk-index",
      pk: {
        field: "gsi5pk",
        composite: ["manager"],
      },
      sk: {
        field: "gsi5sk",
        composite: ["team", "office", "employee"],
      },
    },
  },
});

Task

The Task entity represents a single instance of a task, to be worked on by an Employee.

import { Entity } from "electrodb";

const Task = new Entity({
  model: {
    entity: "task",
    version: "1",
    service: "taskapp",
  },
  attributes: {
    task: "string",
    project: "string",
    employee: "string",
    description: "string",
  },
  indexes: {
    task: {
      pk: {
        field: "pk",
        composite: ["task"],
      },
      sk: {
        field: "sk",
        composite: ["project", "employee"],
      },
    },
    project: {
      index: "gsi1pk-gsi1sk-index",
      pk: {
        field: "gsi1pk",
        composite: ["project"],
      },
      sk: {
        field: "gsi1sk",
        composite: ["employee", "task"],
      },
    },
    assigned: {
      collection: "assignments",
      index: "gsi3pk-gsi3sk-index",
      pk: {
        field: "gsi3pk",
        composite: ["employee"],
      },
      sk: {
        field: "gsi3sk",
        composite: ["project", "task"],
      },
    },
  },
});

Office

The Office entity represents a location/building in which employees can work.

import { Entity } from "electrodb";

const Office = new Entity({
  model: {
    entity: "office",
    version: "1",
    service: "taskapp",
  },
  attributes: {
    office: {
      type: "string",
    },
    country: {
      type: "string",
    },
    state: {
      type: "string",
    },
    city: {
      type: "string",
    },
    zip: {
      type: "string",
    },
    address: {
      type: "string",
    },
  },
  indexes: {
    locations: {
      pk: {
        field: "pk",
        composite: ["country", "state"],
      },
      sk: {
        field: "sk",
        composite: ["city", "zip", "office"],
      },
    },
    office: {
      index: "gsi1pk-gsi1sk-index",
      collection: "workplaces",
      pk: {
        field: "gsi1pk",
        composite: ["office"],
      },
      sk: {
        field: "gsi1sk",
        composite: [],
      },
    },
  },
});

Service

Join models together in a new Service called EmployeeApp

const { DynamoDBClient } = require("@aws-sdk/client-dynamodb");
const { DynamoDBDocumentClient } = require("@aws-sdk/lib-dynamodb");
const client = DynamoDBDocumentClient.from(
  new DynamoDBClient({ region: "us-east-1" }),
);
const { Service } = require("electrodb");
const table = "projectmanagement";

const EmployeeApp = new Service(
  {
    employees: Employee,
    tasks: Task,
    offices: Office,
  },
  { client, table },
);

Access Patterns

All tasks and employee information for a given employee

Fulfilling Requirement #1.

await EmployeeApp.collections.assignments({ employee: "CBaskin" }).go();

Returns the following:

{
  data: {
    employees: [{
      employee: "cbaskin",
      firstName: "carol",
      lastName: "baskin",
      office: "big cat rescue",
      title: "owner",
      team: "cool cats and kittens",
      salary: "1,000,000",
      manager: "",
      dateHired: "1992-11-04",
      birthday: "1961-06-06",
    }],
    tasks: [{
      task: "Feed tigers",
      description: "Prepare food for tigers to eat",
      project: "Keep tigers alive",
      employee: "cbaskin"
    }, {
      task: "Fill water bowls",
      description: "Ensure the tigers have enough water",
      project: "Keep tigers alive",
      employee: "cbaskin"
    }]
  },
  cursor: '...'
}

Find all employees and office details for a given office

Fulfilling Requirement #2.

await EmployeeApp.collections.workplaces({ office: "big cat rescue" }).go();

Returns the following:

{
  data: {
    employees: [{
      employee: "cbaskin",
      firstName: "carol",
      lastName: "baskin",
      office: "big cat rescue",
      title: "owner",
      team: "cool cats and kittens",
      salary: "1,000,000",
      manager: "",
      dateHired: "1992-11-04",
      birthday: "1961-06-06",
    }],
    offices: [{
      office: "big cat rescue",
      country: "usa",
      state: "florida",
      city: "tampa",
      zip: "12345",
      address: "123 Kitty Cat Lane"
    }]
  },
  cursor: '...'
}

Tasks for a given employee

Fulfilling Requirement #3.

// on the service
await EmployeeApp.entities.tasks.query.assigned({ employee: "cbaskin" }).go();

// on the entity
await tasks.query.assigned({ employee: "cbaskin" }).go();

Returns the following:

{
  data: [
    {
      task: "Feed tigers",
      description: "Prepare food for tigers to eat",
      project: "Keep tigers alive",
      employee: "cbaskin"
    }, {
      task: "Fill water bowls",
      description: "Ensure the tigers have enough water",
      project: "Keep tigers alive",
      employee: "cbaskin"
    }
  ],
  cursor: '...',
}

Tasks for a given project

Fulfilling Requirement #4.

// on the service
await EmployeeApp.entities.tasks.query
  .project({ project: "Murder Carol" })
  .go();

// on the entity
await tasks.query.project({ project: "Murder Carol" }).go();

Returns the following:

{
  data: [
    {
      task: "Hire hitman",
      description: "Find someone to murder Carol",
      project: "Murder Carol",
      employee: "jexotic"
    }
  ],
  cursor: '...'
}

Find office locations

Fulfilling Requirement #5.

// on the service
await EmployeeApp.entities.office
  .locations({ country: "usa", state: "florida" })
  .go();

// on the entity
await office.locations({ country: "usa", state: "florida" }).go();

Returns the following:

{
  data: [
    {
      office: "big cat rescue",
      country: "usa",
      state: "florida",
      city: "tampa",
      zip: "12345",
      address: "123 Kitty Cat Lane"
    }
  ],
  cursor: '...'
}

Find employee salaries and titles

Fulfilling Requirement #6.

// on the service
await EmployeeApp.entities.employees
  .roles({ title: "animal wrangler" })
  .lte({ salary: "150.00" })
  .go();

// on the entity
await employees
  .roles({ title: "animal wrangler" })
  .lte({ salary: "150.00" })
  .go();

Returns the following:

{
  data: [
    {
      employee: "ssaffery",
      firstName: "saff",
      lastName: "saffery",
      office: "gw zoo",
      title: "animal wrangler",
      team: "keepers",
      salary: "105.00",
      manager: "jexotic",
      dateHired: "1999-02-23",
      birthday: "1960-07-11",
    }
  ],
  cursor: '...'
}

Find employee birthdays or anniversaries

Fulfilling Requirement #7.

const startDate = "2020-05-01";
const endDate = "2020-06-01";

// on the service
await EmployeeApp.entities.employees
  .workplaces({ office: "gw zoo" })
  .where(
    ({ birthday, dateHired }, { between }) => `
        ${between(dateHired, startDate, endDate)} OR
        ${between(birthday, startDate, endDate)}
    `,
  )
  .go();

// on the entity
await employees
  .workplaces({ office: "gw zoo" })
  .where(
    ({ birthday, dateHired }, { between }) => `
        ${between(dateHired, startDate, endDate)} OR
        ${between(birthday, startDate, endDate)}
    `,
  )
  .go();

Returns the following:

{
  data: [
    {
      employee: "jexotic",
      firstName: "joe",
      lastName: "maldonado-passage",
      office: "gw zoo",
      title: "tiger king",
      team: "founders",
      salary: "10000.00",
      manager: "jlowe",
      dateHired: "1999-02-23",
      birthday: "1963-03-05",
    }
  ],
  cursor: '...'
}

Find direct reports

Fulfilling Requirement #8.

// on the service
await EmployeeApp.entities.employees.reports({ manager: "jlowe" }).go();

// on the entity
await employees.reports({ manager: "jlowe" }).go();

Returns the following:

{
  data: [
    {
      employee: "jexotic",
      firstName: "joe",
      lastName: "maldonado-passage",
      office: "gw zoo",
      title: "tiger king",
      team: "founders",
      salary: "10000.00",
      manager: "jlowe",
      dateHired: "1999-02-23",
      birthday: "1963-03-05",
    }
  ],
  cursor: '...'
}