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
- As a Project Manager, I need to find all tasks and details on a specific employee.
- As a Regional Manager, I need to see all details about an office and its employees
- As an Employee, I need to see all my Tasks.
- As a Product Manager, I need to see all the tasks for a project.
- As a Client, I need to find a physical office close to me.
- As a Hiring manager, I need to find employees with comparable salaries.
- As HR, I need to find upcoming employee birthdays/anniversaries
- 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 DynamoDB = require("aws-sdk/clients/dynamodb");
const client = new DynamoDB.DocumentClient({ 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: '...'
}