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.

The Entities, Service, and Table Definition tabs show the setup this example runs against, and the generated parameters below are produced live by executing the example.

import { EmployeeApp } from "./service";

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.

import { EmployeeApp } from "./service";

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.

import { EmployeeApp } from "./service";
import { Task } from "./entities";

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

// on the entity
await Task.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.

import { EmployeeApp } from "./service";
import { Task } from "./entities";

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

// on the entity
await Task.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.

import { EmployeeApp } from "./service";
import { Office } from "./entities";

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

// on the entity
await Office.query.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.

import { EmployeeApp } from "./service";
import { Employee } from "./entities";

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

// on the entity
await Employee.query
  .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.

import { EmployeeApp } from "./service";
import { Employee } from "./entities";

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

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

// on the entity
await Employee.query
  .coworkers({ 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.

import { EmployeeApp } from "./service";
import { Employee } from "./entities";

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

// on the entity
await Employee.query.directReports({ 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: '...'
}