🧱 Full Database Schema Design
আমি এখানে Proper Accounting Standard মেনে schema দিচ্ছি।
1️⃣ products
products
id (PK)
name
sku (unique)
purchase_price (decimal 12,2)
sell_price (decimal 12,2)
opening_stock (integer)
current_stock (integer)
created_at
updated_at
🔹 current_stock আলাদা রাখছি performance এর জন্য
🔹 opening_stock future auditing এর জন্য থাকবে
2️⃣ customers
customers
id (PK)
name
phone
email (nullable)
address (nullable)
created_at
updated_at
3️⃣ sales
sales
id (PK)
invoice_no (unique)
customer_id (FK)
sale_date (date)
sub_total (decimal 12,2) -- before discount
discount (decimal 12,2)
vat_percent (decimal 5,2)
vat_amount (decimal 12,2)
grand_total (decimal 12,2)
paid_amount (decimal 12,2)
due_amount (decimal 12,2)
created_at
updated_at
🔹 sub_total = qty × price
🔹 grand_total = sub_total - discount + vat
4️⃣ sale_items
sale_items
id (PK)
sale_id (FK)
product_id (FK)
quantity (integer)
unit_price (decimal 12,2)
total_price (decimal 12,2)
created_at
updated_at
🔹 total_price = quantity × unit_price
5️⃣ accounts (Chart of Accounts)
Proper double-entry করার জন্য এটা বাধ্যতামূলক।
accounts
id (PK)
name
code (unique)
type (enum: asset, liability, equity, income, expense)
created_at
updated_at
📌 Initial Accounts Insert করবে
Name Type
Cash asset
Accounts Receivable asset
Inventory asset
Sales Revenue income
VAT Payable liability
Cost of Goods Sold expense
6️⃣ journal_entries
journal_entries
id (PK)
entry_date (date)
reference_type (nullable) -- sale, purchase ইত্যাদি
reference_id (nullable)
description (text)
created_at
updated_at
7️⃣ journal_entry_lines
journal_entry_lines
id (PK)
journal_entry_id (FK)
account_id (FK)
debit (decimal 12,2 default 0)
credit (decimal 12,2 default 0)
created_at
updated_at
🔥 Accounting Structure (Very Important)
Each journal_entry must satisfy:
SUM(debit) = SUM(credit)
এটা তুমি backend validation দিয়ে enforce করবে।
8️⃣ payments (Optional but Recommended)
payments
id (PK)
sale_id (FK)
payment_date (date)
amount (decimal 12,2)
payment_method (string)
created_at
updated_at
এটা future partial payment handling সহজ করবে।
🧮 Sale হলে কী কী হবে (Flow)
Step 1: sale create হবে
→ sales
→ sale_items
Step 2: stock কমবে
→ products.current_stock -= quantity
Step 3: journal entry create হবে
Example:
Debit Accounts Receivable
Credit Sales Revenue
Credit VAT Payable
Debit COGS
Credit Inventory
📊 Date Wise Report এর জন্য দরকার হবে
You can calculate:
Total Sales
SUM(grand_total)
Total Expense (COGS)
journal_entry_lines join করে
where account type = expense
VAT
vat_amount sum
Profit
Sales Revenue - COGS
🧩 Final ER Relationship Structure
customers → sales → sale_items → products
sales → journal_entries → journal_entry_lines → accounts
sales → payments
🚀 Extra Professional Touch (Bonus)
Add:
stock_movements table (future audit)
account_balance view
invoice_number auto generator
soft delete support
🏆 Interview Smart Move
README তে explain করবে:
Accounting flow
Journal logic
Why separate journal_entry_lines
How date filter report works
এটা করলে তুমি Mid না, Senior level impression দিবে 🔥
Top comments (0)