[설계] Office Reservation System (Architecture, API Flow, Database)

 
 

✅ Office Reservation System

• View All Employee Work Status API
• Seat Reservation API
• Cancel Seat Reservation API

 

✅ Architecture

 
Language

• Kotlin

Server

• SpringBoot **2.7.17
• Gradle - Kotlin

DB

• MySQL
• Spring Data JPA (Hibernate)
• QueryDsl

Document

• Swagger 3.0.0 

Testing

• MockK (Mockiing Library)
• IntelliJ (Test Coverage)

 

 

---

 

✅ Detailed Requirements Definition

• There are a total of 100 seats available (1~100).
• Each employee can reserve only [one seat].
• Each seat can be used by only [one employee].
• Total number of employees: 150.
• Work Status: In-office / Remote / Vacation / No show.
  • 추가한 세부 정의 
  • Reservation cancellation: Change the status of the employee who cancelled to [No show].
  • Successful reservation: Change the status of the employee who succeeded to [In-office].
  • When all seats are reserved: Change the status of employees who didn't show up to [Remote].
  • Every day at 12 AM: Change the status of all employees to [No show].
  • Adding an employee: When adding employee data, set the default status to [No show].
• When all seats are reserved, employees who did not reserve a seat are automatically assigned to work remotely.
• If there are remaining seats, employees can reserve a seat.

---

 

✅ Structure of Directory 

├── main
│   ├── kotlin
│   │   └── com
│   │       └── sdoaolo.officereservationsystem
│   │           ├── OfficeReservationSystemApplication.kt
│   │           ├── aop
│   │           ├── common
│   │           │   └── mapping
│   │           ├── configuration
│   │           │   └── querydsl
│   │           │   └── swagger
│   │           ├── employee
│   │           │   ├── controller
│   │           │   ├── dto
│   │           │   ├── entity
│   │           │   ├── repository
│   │           │   └── service
│   │           ├── error
│   │           │   └── exception
│   │           ├── scheduling
│   │           └── seat
│   │               ├── controller
│   │               ├── dto
│   │               ├── entity
│   │               ├── repository
│   │               └── service
│   └── resources
│       ├── application.yml
│       ├── static
│       └── templates
└── test
    ├── kotlin
    │   └── com
    │       └── sdoaolo.officereservationsystem
    │           ├── employee
    │           │   ├── controller
    │           │   └── service
    │           └── seat
    │               ├── controller
    │               └── service
    └── resources

 
 

✅ API 

• All API endpoints start with    /api/v1/  
•   ApplicationResponseDto   (common response type)

data class ApplicationResponseDto<T>(
    val status: ResponseStatus, //Enum class
    val message: String, //custom message
    val code : Long?, //ResponseStatus' code
    val isSuccess: Boolean, // true/false
    val data: T 
)

 

✚ Additional API    (for DEMO)

 

name	Method	End point	param / body
Employee Registration	POST	/employees	{ "name" : "강지은" }
Seat Registration	POST	/seats	{ "seatLocation" : "서울 강남구 삼성동 12312 1F Room3” }

 
 

 

 

📝 Defined API

 

name	Method	End point	param / body
View All Employee Work Status	GET	/employees/work-status	?page=1
Seat Reservation	POST	/seats/reservations	{ "employeeNumber": 7, "seatNumber": 19 }
Cancel Seat Reservation	DELETE	/seats/reservations	{ "employeeNumber": 7, "seatNumber": 19 }

 
 
 

1️⃣ API  : View All Employee Work Status 

• Check the work status of all employees.
  • For employees whose work mode is "in-office," their seat numbers are also provided.
• Implement pagination to display 20 people per page

 

 
 

⏺ Request

Header

GET    /employees/work-status?page=1

 
 

⏺ Response

{
    "status": "SUCCESS",
    "message": "현재 직원들의 근무 상태입니다.",
    "code": 200,
    "isSuccess": true,
    "data": [
        {
            "name": "a",
            "employeeNumber": 1,
            "currentWorkType": "재택"
        },
        {
            "name": "b",
            "employeeNumber": 2,
            "currentWorkType": "출근"
            "seatNumber": 3
        },
        {
            "name": "c",
            "employeeNumber": 3,
            "currentWorkType": "출근"
            "seatNumber": 2
        },
        --- 생략 ---
    ]
}

 
 

2️⃣ API  :  Seat Reservation

• Employees reserve seats for today.
• Multiple employees cannot reserve the same seat at the same time
• Implement concurrency control.  (https://jie0025.tistory.com/604)

[JPA] 비관적락을 사용해 동시성 문제 해결하기 (curl command로 동시요청)

 
 

 

⏺ Request

Header

POST    /seats/reservations

 
Body

{
    "employeeNumber": 1, //The Employee Number
    "seatNumber": 2 //The seat number the employee wishes to reserve
}

 

⏺ Response

{
  "status": "SUCCESS",
  "message": "The seat has been reserved.",
  "code": 200,
  "isSuccess": true,
  "data": {
		"employeeNumber" : 1,
		"seatNumber": 2
  }
}

 
 

⏺ Exception

1.     Out of the effective range  
 
request

{
	"employeeNumber": 333,   //★
	"seatNumber": 444  //★
}

 
response

{
    "status": "BAD_REQUEST",
    "message": "employeeNumber: 150 이하여야 합니다, seatNumber: 100 이하여야 합니다",
    "code": 400,
    "isSuccess": false
}

 
 
 
2.     Data entered incorrectly   
 
request

{
    "employeeNumber": "HIHIHI",    //★
    "seatNumber": 1
}

 
response

{
    "timestamp": "2023-11-19T05:59:42.762+00:00",
    "status": 400,
    "error": "Bad Request",
    "path": "/api/v1/seats/reservations"
}

 
 
3.    No data exists  

{
    "status": "NOT_FOUND",
    "message": "Employee Not Found",
    "code": 404,
    "isSuccess": false
}

{
    "status": "NOT_FOUND",
    "message": "Seat Not Found",
    "code": 404,
    "isSuccess": false
}

 
 
4.     No seats available  

{
    "status": "UNPROCESSABLE_ENTITY",
    "message": "There are no remaining seats. ",
    "code": 422,
    "isSuccess": false
}

 
 
5.     Seat already reserved  

{
    "status": "CONFLICT",
    "message": "This seat is already reserved. Please choose a different seat",
    "code": 409,
    "isSuccess": false
}

 
 
6.     User has already made a reservation  

{
    "status": "CONFLICT",
    "message": "This user has already completed a reservation",
    "code": 409,
    "isSuccess": false
}

 
 
7.     Seats canceled today cannot be rebooked

{
    "status": "BAD_REQUEST",
    "message": "Previously reserved seats cannot be re-reserved",
    "code": 400,
    "isSuccess": false
}

 
 
 

3️⃣  API  :  Cancel Seat Reservation

• If an employee cancels their reservation, another employee can reserve the seat.
• If a seat is canceled by any employee, it cannot be re-booked on the same day.

 
 

⏺ Request

Header

DELETE    /seats/reservations

 
Body

{
    "employeeNumber": 1, //The Employee Number
    "seatNumber": 2 //The seat number the employee wishes to cancel
}

 

⏺ Response

{
  "status": "SUCCESS",
  "message": "The seat has been canceled.",
  "code": 200,
  "isSuccess": true,
  "data": {
		"employeeNumber" : 1,
		"seatNumber": 2
  }
}

 
 

⏺ Exception

1.    No data exists  

{
    "status": "NOT_FOUND",
    "message": "Reservation Not Found",
    "code": 404,
    "isSuccess": false
}

 
 
 

 

✅  Database Design (MySQL)

 

⏺ ER-Diagram

 

a. Table  : employee 

• employeeId
  • Primary Key 
  • BIGINT AUTO INCREMENT
• name
  • VARCHAR(20)
  • NOT NULL
• employeeNumber - (1~150)
  • NOT NULL
  • SMALLINT
• currentWorkType
  • VARCHAR(255)
  •  In-office / Remote / Vacation / No show
• created_date : Date the employee's account was created
  • DATE

 

b. Table  :  employee-seat

• id
  • Primary Key
  • BIGINT AUTO_INCREMENT
• employee_id : FK (from Employees_employeeId)
  • INT
  • employee : employee_seat = 1: N
• seatId : FK (from seats_seatId)
  • INT
  • seat : employee_seat = 1: N
• isValid    (It will notify you if the reservation is valid.)
  • BOOLEAN
  • Current Reservation (Valid)
  • Canceled Reservation (Invalid)
• reserve_date
  • DATE

 

c.  Table  :  seat

• seatId 
  • PK 
  • BIGINT AUTO INCREMENT
• seatLocation
  • VARCHAR(50)
    • Room numbers can consist of numbers, letters, or a combination of both.
      • Example) AA Building 3F 20, BBB Tower 6F 4
• seatNumber (1~100)
  • NOT NULL
  • SMALLINT
• created_date   (Date the seat was created)
  • DATE

 

⏺ Relationships between Tables

Reference: employee_seat.employee_id > employees.employeeId

• Many-to-One Relationship:
  • Employees can create multiple entries of reservation information.
  • Each reservation record is associated with a single employee.

 
Reference: employee_seat.seatId > seats.seatId

• Many-to-One Relationship:
  • A single seat can be associated with multiple reservation records.
  • Each reservation record is associated with a single seat.

 
 

⏺ Compound UNIQUE Constraints

• To ensure that employees can make valid reservations for only one seat per day, a compound UNIQUE constraint has been applied.
• UNIQUE Constraint Settings: reserve_date, employee_id, seat_id.

It is possible to reserve different seats for different employees on the same day.

Through business logic validation (isValid check), we have implemented a system that prevents employees from making multiple reservations for seats

 

---

✅ Test Coverage

• Unit testing of the Controller and Service units is conducted.

 

• Test coverage has been verified using the IntelliJ