Database Schema Design

This document outlines the database schema for the Drinks Inventory System.

Entity Relationship Diagram

+----------------+       +-------------------+       +----------------+
| DrinkType      |       | InventoryRecord   |       | Order          |
+----------------+       +-------------------+       +----------------+
| id             |       | id                |       | id             |
| name           |       | drink_type_id     |<----->| created_at     |
| description    |       | quantity          |       | updated_at     |
| desired_stock  |<----->| timestamp         |       | status         |
| created_at     |       | created_at        |       +----------------+
| updated_at     |       | updated_at        |             ^
+----------------+       +-------------------+             |
                                                           |
                                                           |
                                                +----------------+
                                                | OrderItem      |
                                                +----------------+
                                                | id             |
                                                | order_id       |
                                                | drink_type_id  |
                                                | quantity       |
                                                | created_at     |
                                                | updated_at     |
                                                +----------------+

                         +-------------------+
                         | SystemConfig      |
                         +-------------------+
                         | id                |
                         | key               |
                         | value             |
                         | created_at        |
                         | updated_at        |
                         +-------------------+

Tables

DrinkType

Stores information about different types of drinks.

Column	Type	Constraints	Description
id	INT	PK, AUTO_INCREMENT	Unique identifier
name	VARCHAR(255)	UNIQUE, NOT NULL	Name of the drink type
description	TEXT	NULL	Optional description
desired_stock	INT	NOT NULL	Desired stock level in crates
created_at	DATETIME	NOT NULL	Record creation timestamp
updated_at	DATETIME	NOT NULL	Record last update timestamp

InventoryRecord

Stores the history of inventory changes.

Column	Type	Constraints	Description
id	INT	PK, AUTO_INCREMENT	Unique identifier
drink_type_id	INT	FK, NOT NULL	Reference to DrinkType
quantity	INT	NOT NULL	Current quantity in crates
timestamp	DATETIME	NOT NULL	When the inventory was recorded
created_at	DATETIME	NOT NULL	Record creation timestamp
updated_at	DATETIME	NOT NULL	Record last update timestamp

Order

Stores information about orders.

Column	Type	Constraints	Description
id	INT	PK, AUTO_INCREMENT	Unique identifier
status	ENUM	NOT NULL	Order status (new, in work, ordered, fulfilled)
created_at	DATETIME	NOT NULL	Record creation timestamp
updated_at	DATETIME	NOT NULL	Record last update timestamp

OrderItem

Stores items within an order.

Column	Type	Constraints	Description
id	INT	PK, AUTO_INCREMENT	Unique identifier
order_id	INT	FK, NOT NULL	Reference to Order
drink_type_id	INT	FK, NOT NULL	Reference to DrinkType
quantity	INT	NOT NULL	Quantity to order in crates
created_at	DATETIME	NOT NULL	Record creation timestamp
updated_at	DATETIME	NOT NULL	Record last update timestamp

SystemConfig

Stores system configuration parameters.

Column	Type	Constraints	Description
id	INT	PK, AUTO_INCREMENT	Unique identifier
key	VARCHAR(255)	UNIQUE, NOT NULL	Configuration key
value	TEXT	NOT NULL	Configuration value
created_at	DATETIME	NOT NULL	Record creation timestamp
updated_at	DATETIME	NOT NULL	Record last update timestamp

Relationships

DrinkType to InventoryRecord: One-to-Many
- A drink type can have multiple inventory records
- Each inventory record belongs to one drink type
DrinkType to OrderItem: One-to-Many
- A drink type can be included in multiple order items
- Each order item refers to one drink type
Order to OrderItem: One-to-Many
- An order can contain multiple order items
- Each order item belongs to one order

Indexes

drink_type_id in InventoryRecord table
order_id in OrderItem table
drink_type_id in OrderItem table
key in SystemConfig table

Configuration Parameters

The SystemConfig table will store the following configuration parameters:

stock_adjustment_lookback: How many past orders and the stock at their time should be considered
stock_adjustment_magnitude: How much to adjust desired stock levels by
stock_adjustment_threshold: Threshold for triggering adjustments
Other system-wide configuration parameters

6.7 KiB Raw Permalink Blame History