# CS5242 YCQL Modling

Reference:
* [Data Modeling for YCQL](https://docs.yugabyte.com/preview/develop/learn/data-modeling-ycql/)

Design Principles:
* Keep frequent queries local to a single node
* Parition by warehouse and cluster by district
* Dont store derived state eg YTD


## Tables

### Warehouse Table

| Attribute | Desc | Remarks |
| -------- | -------- | -------- |
| W_ID | | PK |

* All other warehouse fields except W_YTD

### District Table
| Attribute | Desc | Remarks |
| -------- | -------- | -------- |
| D_W_ID | | PK |
| D_ID | | CK |

* Include all other ditrict fields except D_YTD and D_NEXT_O_ID

### Customer Table
| Attribute | Desc | Remarks |
| -------- | -------- | -------- |
| C_W_ID | | PK |
| C_D_ID | | CK |
| C_ID | | CK |

* Except C YTD PAYMENT,C PAYMENT CNT, C DELIVERY CNT, C_DATA

### Items table
* Add W_ID to replicated items across all nodes

### Order Table
| Attribute | Desc | Remarks |
| -------- | -------- | -------- |
| O_W_ID | | PK |
| O_D_ID | | CK |
| O_ID | | CK |
| O_ORDERS | order line items in JSON |

### Stock Table
| Attribute | Desc | Remarks |
| -------- | -------- | -------- |
| S_W_ID | | PK |
| S_D_ID | |CK |
| S_I_ID | | CK |
| S_ORDERS | order line items in JSON |

* Except YTD

------

## Transactions

## 2.1: New-Order
The first line consists of five comma-separated values: N,C ID,W ID,D ID,M. Each of the M remaining lines specifies an item in the order and consists of three comma- separated values: OL I ID,OL SUPPLY W ID,OL QUANTITY.

## 2.2: Payment
Payment Transaction consists of one line of input with five comma-separated values: P,C W ID,C D ID,C ID,PAYMENT.

## 2.3: Delivery
Delivery Transaction consists of one line of input with three comma-separated values: D,W ID,CARRIER ID.

## 2.4: Order-Status
Order-Status Transaction consists of one line of input with four comma-separated values: O,C W ID,C D ID,C ID.

Order: {
    order_id: "hash ID for order",
    customer_id,
    entry_date,
    carrier_id,
    customer: {
        id,
        first_name,
        last_name,
        balance,
    },
    order_line: [{
        item_id,
        quantiy,
        supply_warehouse,
        delivery_d,
        supply_w_id,
        quantity,
        amount,
    }]
}

item: {
            item_id,
            item_name,
            price,
            brand
        }
            
output:
        
Customer:{
    first_name,
    middle_name,
    last_name,
    balance,
    last_order:{
        id: "order id",
        entry_date_time: "entry date and time",
        carrier: "",
        order_line: [{
            item: {},
            supply_warehouse_id: "Supplying warehouse number"
                 
        
        }]    
    }
}

## 2.5: Stock-Level
Stock-Level Transaction consists of one line of input with five comma-separated values: S,W ID,D ID,T,L.

## 2.6: Popular-Item
Popular-Item Transaction consists of one line of input with four comma-separated values: I,W ID,D ID,L.

## 2.7: Top-Balance
Top-Balance Transaction consists of one line of input with one value: T.

## 2.8: Related-Customer
Related-Customer Transaction consists of one line of input with four comma-separated values: R,C W ID,C D ID,C ID.