URL visual Database :
https://dbdiagram.io/d/68653a69f413ba3508efa6bc
or This is result of exporting design visual Relathionship Database into PNG file
| Table | Field | Description |
|---|---|---|
| Users | id | Primary Key - Unique identifier for each user |
| username | User's login name | |
| User's email address | ||
| password_hash | Encrypted password for secure login | |
| role_id | Foreign Key - references UserRoles.id |
|
| created_at | Timestamp when the user was created | |
| updated_at | Timestamp when the user was last updated | |
| UserRoles | id | Primary Key - Unique identifier for each role |
| role_name | Name of the role (e.g., Admin, Cashier) | |
| Products | id | Primary Key - Unique identifier for each product |
| sku | Unique Stock Keeping Unit code | |
| product_name | Name of the product | |
| description | Detailed description of the product | |
| purchase_price | Last purchase price of the product | |
| selling_price | Default selling price of the product | |
| current_stock_qty | Current quantity available in stock | |
| min_stock_threshold | Minimum quantity for stock alert | |
| created_at | Timestamp when the product was created | |
| updated_at | Timestamp when the product was last updated | |
| Transactions | id | Primary Key - Unique identifier for each transaction |
| transaction_code | Unique code or invoice number | |
| transaction_type | Type of transaction: SALE or PURCHASE | |
| total_amount | Total amount of the transaction | |
| transaction_date | Date and time of the transaction | |
| buyer_seller_name | Name of buyer or seller involved | |
| notes | Additional notes about the transaction | |
| isDeleted | helps to subtly delete data | |
| user_id | Foreign Key - references Users.id |
|
| created_at | Timestamp when transaction was created | |
| updated_at | Timestamp when transaction was last updated | |
| TransactionItems | id | Primary Key - Unique identifier for each item |
| transaction_id | Foreign Key - references Transactions.id |
|
| product_id | Foreign Key - references Products.id |
|
| quantity | Number of products in this item | |
| unit_price_at_transaction | Price per unit at transaction time | |
| subtotal | Total for this item line (quantity * unit price) | |
| StockMovements | id | Primary Key - Unique identifier for stock change |
| product_id | Foreign Key - references Products.id |
|
| movement_type | Stock movement type: IN or OUT | |
| quantity_changed | Quantity added or removed | |
| stock_after_movement | Stock level after movement | |
| reason | Reason for stock change (e.g., Sale, Purchase) | |
| transaction_item_id | Foreign Key - references TransactionItems.id |
|
| movement_date | Date and time of the movement | |
| user_id | Foreign Key - references Users.id |
Users authenticate and get permissions via UserRoles.current_stock_qty in Products is updated in real time from these movements.schema prisma to generate and managing database in mysql
generator client {
provider = "prisma-client-js"
}
datasource db {
provider = "mysql"
url = env("DATABASE_URL")
}
model UserRole {
id Int @id @default(autoincrement())
roleName String @unique @map("role_name")
users User[]
}
model User {
id Int @id @default(autoincrement())
username String @unique
email String @unique
passwordHash String @map("password_hash")
roleId Int @map("role_id")
createdAt DateTime @default(now()) @map("created_at")
updatedAt DateTime @default(now()) @map("updated_at")
role UserRole @relation(fields: [roleId], references: [id])
transactions Transaction[]
stockMovements StockMovement[]
}
model Product {
id Int @id @default(autoincrement())
sku String @unique
productName String @map("product_name")
description String?
purchasePrice Decimal @default(0) @map("purchase_price")
sellingPrice Decimal @default(0) @map("selling_price")
currentStockQty Int @default(0) @map("current_stock_qty")
minStockThreshold Int @default(0) @map("min_stock_threshold")
createdAt DateTime @default(now()) @map("created_at")
updatedAt DateTime @default(now()) @map("updated_at")
transactionItems TransactionItem[]
stockMovements StockMovement[]
}
model Transaction {
id Int @id @default(autoincrement())
transactionCode String @unique @map("transaction_code")
transactionType TransactionType
totalAmount Decimal @map("total_amount")
transactionDate DateTime @default(now()) @map("transaction_date")
buyerSellerName String? @map("buyer_seller_name")
notes String?
userId Int @map("user_id")
createdAt DateTime @default(now()) @map("created_at")
updatedAt DateTime @default(now()) @map("updated_at")
user User @relation(fields: [userId], references: [id])
items TransactionItem[]
}
model TransactionItem {
id Int @id @default(autoincrement())
transactionId Int @map("transaction_id")
productId Int @map("product_id")
quantity Int
unitPriceAtTransaction Decimal @map("unit_price_at_transaction")
subtotal Decimal
transaction Transaction @relation(fields: [transactionId], references: [id])
product Product @relation(fields: [productId], references: [id])
stockMovements StockMovement[]
}
model StockMovement {
id Int @id @default(autoincrement())
productId Int @map("product_id")
movementType MovementType @map("movement_type")
quantityChanged Int @map("quantity_changed")
stockAfterMovement Int @map("stock_after_movement")
reason String?
transactionItemId Int? @map("transaction_item_id")
movementDate DateTime @default(now()) @map("movement_date")
userId Int? @map("user_id")
product Product @relation(fields: [productId], references: [id])
transactionItem TransactionItem? @relation(fields: [transactionItemId], references: [id])
user User? @relation(fields: [userId], references: [id])
}
enum TransactionType {
SALE
PURCHASE
}
enum MovementType {
IN
OUT
}