soham874 / order-management-system

Problem Statement

Consider an Order management scenario. An Order signifies a Purchase order raised by a buyer to the seller. A single Order can have multiple Products, each having varying quantities. Eg - Purchase Order PO-1, having two products - P1 (Plastic Bag) for 50 qty, and P2 (Garbage Bag) for 151 qty. Each Product can be sold by multiple Vendors. Eg - P1(Plastic Bag) is sold by two Vendors V1 and V2.

Create a Java rest apis to add/ edit/ delete orders.

API Structure

1. Add Order - User should be able to add an Order and associate the Product and the vendor options for the Product. The orderProduct entity signifies the relation between Order and the Product and the Vendor option.

curl --location --request POST 'http://localhost:8080/v1/oms/add-order' \
--header 'Content-Type: application/json' \
--data-raw '{
"id": 1,
"description": "Order 1",
"orderProducts": [
        {
            "product": {
                "id":1 ,
                "description": "Product 1"
            },
            "vendorList": [
                {
                    "id": 1,
                    "name": "Vendor 1",
                    "address": "Add Line1, Add Line2, US"
                },
                {
                    "id": 2,
                    "name": "Vendor 2",
                    "address": "Add Line1, Add Line2, MX"
                }
            ]
        },
        {
            "product": {
                "id": 2,
                "description": "Product 2"
            },
            "vendorList": [
                {
                    "id": 1,
                    "name": "Vendor 1",
                    "address": "Add Line1, Add Line2, US"
                },
                {
                    "id": 3,
                    "name": "Vendor 3",
                    "address": "Add Line1, Add Line2, IN"
                }
            ]
        }  
    ]
}'

2. Edit Order - The Edit Order json structure should be identical to the Add Order Structure. The difference being the Edit payload will have ids to tell the server what order/ product to modify.

curl --location --request POST 'http://localhost:8080/v1/oms/edit-order?existingOrderId=1' \
--header 'Content-Type: application/json' \
--data-raw '{
"id": 1,
"description": "Order 2",
"orderProducts": [
        {
            "product": {
                "id":1 ,
                "description": "Product 1"
            },
            "vendorList": [
                {
                    "id": 4,
                    "name": "Vendor 4",
                    "address": "Add Line4, Add Line4, OK"
                },
                {
                    "id": 2,
                    "name": "Vendor 2",
                    "address": "Add Line1, Add Line2, MX"
                }
            ]
        },
        {
            "product": {
                "id": 2,
                "description": "Product 2"
            },
            "vendorList": [
                {
                    "id": 1,
                    "name": "Vendor 1",
                    "address": "Add Line1, Add Line2, US"
                },
                {
                    "id": 3,
                    "name": "Vendor 3",
                    "address": "Add Line1, Add Line2, IN"
                }
            ]
        }  
    ]
}'

3. Delete Order - Delete Order should delete the order and related orderProduct entity, but not the underlying Product or Vendor entity.

curl --location --request POST 'http://localhost:8080/v1/oms/delete-order' \
--header 'Content-Type: application/json' \
--data-raw '1'

Solution

1. Install MySQL Server and MySQL Workbench. Ensure MySQL server is running and an be accessed from MySQL workbench.
2. Run the following SQL statements to create schema and tables

CREATE DATABASE `order_management_system`;

CREATE TABLE `order_management_system`.`oms_order_table` (
  `OMS_ORDER_ID` bigint NOT NULL AUTO_INCREMENT,
  `ORDER_ID` varchar(10) NOT NULL,
  `DESCRIPTION` varchar(100) NOT NULL,
  `PRODUCT_ID` varchar(10) NOT NULL,
  `VENDOR_ID` varchar(10) NOT NULL,
  `CREATED_TS` varchar(100) NOT NULL,
  `LAST_CHANGE_TS` varchar(100) NOT NULL,
  `LAST_CHANGE_USER_ID` varchar(45) NOT NULL,
  PRIMARY KEY (`OMS_ORDER_ID`)
);

CREATE TABLE `order_management_system`.`oms_products` (
  `ID` varchar(10) NOT NULL,
  `DESCRIPTION` varchar(100) NOT NULL,
  `CREATED_TS` varchar(100) NOT NULL,
  `LAST_CHANGE_TS` varchar(100) NOT NULL,
  `LAST_CHANGE_USER_ID` varchar(45) NOT NULL
);

CREATE TABLE `order_management_system`.`oms_vendors` (
  `ID` varchar(10) NOT NULL,
  `NAME` varchar(100) DEFAULT NULL,
  `ADDRESS` varchar(100) DEFAULT NULL,
  `CREATED_TS` varchar(100) NOT NULL,
  `LAST_CHANGE_TS` varchar(100) NOT NULL,
  `LAST_CHANGE_USER_ID` varchar(45) NOT NULL
);

3. Install IntelliJ and add run configuration with following VM options

database.url=jdbc:mysql://127.0.0.1:3306/order_management_system
database.username=root

4. Click on run. Application should be up and running