455 lines
17 KiB
Markdown
455 lines
17 KiB
Markdown
# Django Manufacturing App - Database Models Design
|
|
|
|
## Overview
|
|
|
|
This document details the database models for each module of the manufacturing application. The models are designed to support all required functionality while maintaining data integrity and relationships.
|
|
|
|
## Core Models
|
|
|
|
### 1. User Management Models
|
|
|
|
```python
|
|
# accounts/models.py
|
|
|
|
from django.contrib.auth.models import AbstractUser
|
|
from django.db import models
|
|
|
|
class User(AbstractUser):
|
|
phone = models.CharField(max_length=20, blank=True)
|
|
department = models.CharField(max_length=100, blank=True)
|
|
position = models.CharField(max_length=100, blank=True)
|
|
is_active = models.BooleanField(default=True)
|
|
date_joined = models.DateTimeField(auto_now_add=True)
|
|
|
|
class Role(models.Model):
|
|
name = models.CharField(max_length=50, unique=True)
|
|
description = models.TextField(blank=True)
|
|
created_at = models.DateTimeField(auto_now_add=True)
|
|
updated_at = models.DateTimeField(auto_now=True)
|
|
|
|
class UserRole(models.Model):
|
|
user = models.ForeignKey(User, on_delete=models.CASCADE)
|
|
role = models.ForeignKey(Role, on_delete=models.CASCADE)
|
|
assigned_at = models.DateTimeField(auto_now_add=True)
|
|
|
|
class Meta:
|
|
unique_together = ('user', 'role')
|
|
|
|
class Permission(models.Model):
|
|
name = models.CharField(max_length=100, unique=True)
|
|
codename = models.CharField(max_length=100, unique=True)
|
|
module = models.CharField(max_length=50) # e.g., 'inventory', 'sales'
|
|
description = models.TextField(blank=True)
|
|
|
|
class RolePermission(models.Model):
|
|
role = models.ForeignKey(Role, on_delete=models.CASCADE)
|
|
permission = models.ForeignKey(Permission, on_delete=models.CASCADE)
|
|
|
|
class Meta:
|
|
unique_together = ('role', 'permission')
|
|
```
|
|
|
|
### 2. Inventory Management Models
|
|
|
|
```python
|
|
# inventory/models.py
|
|
|
|
from django.db import models
|
|
from django.core.validators import MinValueValidator
|
|
|
|
class Category(models.Model):
|
|
name = models.CharField(max_length=100)
|
|
description = models.TextField(blank=True)
|
|
created_at = models.DateTimeField(auto_now_add=True)
|
|
|
|
def __str__(self):
|
|
return self.name
|
|
|
|
class UnitOfMeasure(models.Model):
|
|
name = models.CharField(max_length=50)
|
|
abbreviation = models.CharField(max_length=10)
|
|
|
|
def __str__(self):
|
|
return self.name
|
|
|
|
class Product(models.Model):
|
|
PRODUCT_TYPES = [
|
|
('raw_material', 'Raw Material'),
|
|
('finished_good', 'Finished Good'),
|
|
('component', 'Component'),
|
|
]
|
|
|
|
code = models.CharField(max_length=50, unique=True)
|
|
name = models.CharField(max_length=200)
|
|
description = models.TextField(blank=True)
|
|
category = models.ForeignKey(Category, on_delete=models.SET_NULL, null=True, blank=True)
|
|
unit_of_measure = models.ForeignKey(UnitOfMeasure, on_delete=models.SET_NULL, null=True)
|
|
product_type = models.CharField(max_length=20, choices=PRODUCT_TYPES)
|
|
reorder_level = models.DecimalField(max_digits=10, decimal_places=2, default=0)
|
|
selling_price = models.DecimalField(max_digits=10, decimal_places=2, null=True, blank=True)
|
|
cost_price = models.DecimalField(max_digits=10, decimal_places=2, null=True, blank=True)
|
|
is_active = models.BooleanField(default=True)
|
|
created_at = models.DateTimeField(auto_now_add=True)
|
|
updated_at = models.DateTimeField(auto_now=True)
|
|
|
|
def __str__(self):
|
|
return f"{self.code} - {self.name}"
|
|
|
|
class Warehouse(models.Model):
|
|
name = models.CharField(max_length=100)
|
|
location = models.CharField(max_length=200)
|
|
is_active = models.BooleanField(default=True)
|
|
created_at = models.DateTimeField(auto_now_add=True)
|
|
|
|
def __str__(self):
|
|
return self.name
|
|
|
|
class Inventory(models.Model):
|
|
product = models.ForeignKey(Product, on_delete=models.CASCADE)
|
|
warehouse = models.ForeignKey(Warehouse, on_delete=models.CASCADE)
|
|
quantity = models.DecimalField(max_digits=10, decimal_places=2, default=0)
|
|
reserved_quantity = models.DecimalField(max_digits=10, decimal_places=2, default=0)
|
|
updated_at = models.DateTimeField(auto_now=True)
|
|
|
|
class Meta:
|
|
unique_together = ('product', 'warehouse')
|
|
|
|
@property
|
|
def available_quantity(self):
|
|
return self.quantity - self.reserved_quantity
|
|
|
|
class StockMovement(models.Model):
|
|
MOVEMENT_TYPES = [
|
|
('in', 'Stock In'),
|
|
('out', 'Stock Out'),
|
|
('adjustment', 'Adjustment'),
|
|
('transfer', 'Transfer'),
|
|
]
|
|
|
|
product = models.ForeignKey(Product, on_delete=models.CASCADE)
|
|
warehouse = models.ForeignKey(Warehouse, on_delete=models.CASCADE)
|
|
movement_type = models.CharField(max_length=20, choices=MOVEMENT_TYPES)
|
|
quantity = models.DecimalField(max_digits=10, decimal_places=2)
|
|
reference_number = models.CharField(max_length=100, blank=True)
|
|
notes = models.TextField(blank=True)
|
|
created_by = models.ForeignKey('accounts.User', on_delete=models.SET_NULL, null=True)
|
|
created_at = models.DateTimeField(auto_now_add=True)
|
|
```
|
|
|
|
### 3. Purchase Management Models
|
|
|
|
```python
|
|
# purchasing/models.py
|
|
|
|
from django.db import models
|
|
|
|
class Supplier(models.Model):
|
|
code = models.CharField(max_length=50, unique=True)
|
|
name = models.CharField(max_length=200)
|
|
contact_person = models.CharField(max_length=100, blank=True)
|
|
email = models.EmailField(blank=True)
|
|
phone = models.CharField(max_length=20, blank=True)
|
|
address = models.TextField(blank=True)
|
|
tax_id = models.CharField(max_length=50, blank=True)
|
|
payment_terms = models.CharField(max_length=100, blank=True)
|
|
is_active = models.BooleanField(default=True)
|
|
created_at = models.DateTimeField(auto_now_add=True)
|
|
updated_at = models.DateTimeField(auto_now=True)
|
|
|
|
def __str__(self):
|
|
return self.name
|
|
|
|
class PurchaseOrder(models.Model):
|
|
STATUS_CHOICES = [
|
|
('ordered', 'Ordered'),
|
|
('completed', 'Completed'),
|
|
('cancelled', 'Cancelled'),
|
|
]
|
|
|
|
po_number = models.CharField(max_length=50, unique=True)
|
|
supplier = models.ForeignKey(Supplier, on_delete=models.CASCADE)
|
|
order_date = models.DateField()
|
|
expected_delivery_date = models.DateField(null=True, blank=True)
|
|
status = models.CharField(max_length=20, choices=STATUS_CHOICES, default='ordered')
|
|
subtotal = models.DecimalField(max_digits=12, decimal_places=2, default=0)
|
|
tax_amount = models.DecimalField(max_digits=12, decimal_places=2, default=0)
|
|
total_amount = models.DecimalField(max_digits=12, decimal_places=2, default=0)
|
|
notes = models.TextField(blank=True)
|
|
created_by = models.ForeignKey('accounts.User', on_delete=models.SET_NULL, null=True)
|
|
created_at = models.DateTimeField(auto_now_add=True)
|
|
updated_at = models.DateTimeField(auto_now=True)
|
|
|
|
def __str__(self):
|
|
return self.po_number
|
|
|
|
class PurchaseOrderItem(models.Model):
|
|
po = models.ForeignKey(PurchaseOrder, on_delete=models.CASCADE, related_name='items')
|
|
product = models.ForeignKey('inventory.Product', on_delete=models.CASCADE)
|
|
quantity = models.DecimalField(max_digits=10, decimal_places=2)
|
|
unit_price = models.DecimalField(max_digits=10, decimal_places=2)
|
|
total_price = models.DecimalField(max_digits=12, decimal_places=2)
|
|
received_quantity = models.DecimalField(max_digits=10, decimal_places=2, default=0)
|
|
|
|
def save(self, *args, **kwargs):
|
|
self.total_price = self.quantity * self.unit_price
|
|
super().save(*args, **kwargs)
|
|
|
|
class GoodsReceipt(models.Model):
|
|
gr_number = models.CharField(max_length=50, unique=True)
|
|
po = models.ForeignKey(PurchaseOrder, on_delete=models.CASCADE)
|
|
receipt_date = models.DateField()
|
|
received_by = models.ForeignKey('accounts.User', on_delete=models.SET_NULL, null=True)
|
|
notes = models.TextField(blank=True)
|
|
created_at = models.DateTimeField(auto_now_add=True)
|
|
|
|
def __str__(self):
|
|
return self.gr_number
|
|
|
|
class GoodsReceiptItem(models.Model):
|
|
receipt = models.ForeignKey(GoodsReceipt, on_delete=models.CASCADE, related_name='items')
|
|
po_item = models.ForeignKey(PurchaseOrderItem, on_delete=models.CASCADE)
|
|
received_quantity = models.DecimalField(max_digits=10, decimal_places=2)
|
|
notes = models.TextField(blank=True)
|
|
```
|
|
|
|
### 4. Sales Management Models
|
|
|
|
```python
|
|
# sales/models.py
|
|
|
|
from django.db import models
|
|
|
|
class Customer(models.Model):
|
|
code = models.CharField(max_length=50, unique=True)
|
|
name = models.CharField(max_length=200)
|
|
contact_person = models.CharField(max_length=100, blank=True)
|
|
email = models.EmailField(blank=True)
|
|
phone = models.CharField(max_length=20, blank=True)
|
|
address = models.TextField(blank=True)
|
|
tax_id = models.CharField(max_length=50, blank=True)
|
|
payment_terms = models.CharField(max_length=100, blank=True)
|
|
is_active = models.BooleanField(default=True)
|
|
created_at = models.DateTimeField(auto_now_add=True)
|
|
updated_at = models.DateTimeField(auto_now=True)
|
|
|
|
def __str__(self):
|
|
return self.name
|
|
|
|
class SalesOrder(models.Model):
|
|
STATUS_CHOICES = [
|
|
('processing', 'Processing'),
|
|
('completed', 'Completed'),
|
|
('cancelled', 'Cancelled'),
|
|
]
|
|
|
|
so_number = models.CharField(max_length=50, unique=True)
|
|
customer = models.ForeignKey(Customer, on_delete=models.CASCADE)
|
|
order_date = models.DateField()
|
|
expected_delivery_date = models.DateField(null=True, blank=True)
|
|
status = models.CharField(max_length=20, choices=STATUS_CHOICES, default='processing')
|
|
subtotal = models.DecimalField(max_digits=12, decimal_places=2, default=0)
|
|
tax_amount = models.DecimalField(max_digits=12, decimal_places=2, default=0)
|
|
total_amount = models.DecimalField(max_digits=12, decimal_places=2, default=0)
|
|
notes = models.TextField(blank=True)
|
|
created_by = models.ForeignKey('accounts.User', on_delete=models.SET_NULL, null=True)
|
|
created_at = models.DateTimeField(auto_now_add=True)
|
|
updated_at = models.DateTimeField(auto_now=True)
|
|
|
|
def __str__(self):
|
|
return self.so_number
|
|
|
|
class SalesOrderItem(models.Model):
|
|
so = models.ForeignKey(SalesOrder, on_delete=models.CASCADE, related_name='items')
|
|
product = models.ForeignKey('inventory.Product', on_delete=models.CASCADE)
|
|
quantity = models.DecimalField(max_digits=10, decimal_places=2)
|
|
unit_price = models.DecimalField(max_digits=10, decimal_places=2)
|
|
total_price = models.DecimalField(max_digits=12, decimal_places=2)
|
|
shipped_quantity = models.DecimalField(max_digits=10, decimal_places=2, default=0)
|
|
|
|
def save(self, *args, **kwargs):
|
|
self.total_price = self.quantity * self.unit_price
|
|
super().save(*args, **kwargs)
|
|
|
|
class Delivery(models.Model):
|
|
delivery_number = models.CharField(max_length=50, unique=True)
|
|
so = models.ForeignKey(SalesOrder, on_delete=models.CASCADE)
|
|
delivery_date = models.DateField()
|
|
delivered_by = models.ForeignKey('accounts.User', on_delete=models.SET_NULL, null=True)
|
|
notes = models.TextField(blank=True)
|
|
created_at = models.DateTimeField(auto_now_add=True)
|
|
|
|
def __str__(self):
|
|
return self.delivery_number
|
|
|
|
class DeliveryItem(models.Model):
|
|
delivery = models.ForeignKey(Delivery, on_delete=models.CASCADE, related_name='items')
|
|
so_item = models.ForeignKey(SalesOrderItem, on_delete=models.CASCADE)
|
|
delivered_quantity = models.DecimalField(max_digits=10, decimal_places=2)
|
|
notes = models.TextField(blank=True)
|
|
```
|
|
|
|
### 5. Manufacturing Management Models
|
|
|
|
```python
|
|
# manufacturing/models.py
|
|
|
|
from django.db import models
|
|
|
|
class BillOfMaterial(models.Model):
|
|
product = models.ForeignKey('inventory.Product', on_delete=models.CASCADE, related_name='boms')
|
|
bom_code = models.CharField(max_length=50, unique=True)
|
|
version = models.CharField(max_length=20, default='1.0')
|
|
is_active = models.BooleanField(default=True)
|
|
created_at = models.DateTimeField(auto_now_add=True)
|
|
updated_at = models.DateTimeField(auto_now=True)
|
|
|
|
def __str__(self):
|
|
return f"{self.bom_code} - {self.product.name}"
|
|
|
|
class BOMItem(models.Model):
|
|
bom = models.ForeignKey(BillOfMaterial, on_delete=models.CASCADE, related_name='items')
|
|
component = models.ForeignKey('inventory.Product', on_delete=models.CASCADE, related_name='bom_components')
|
|
quantity = models.DecimalField(max_digits=10, decimal_places=2)
|
|
unit_of_measure = models.ForeignKey('inventory.UnitOfMeasure', on_delete=models.SET_NULL, null=True)
|
|
|
|
class Meta:
|
|
unique_together = ('bom', 'component')
|
|
|
|
class ManufacturingOrder(models.Model):
|
|
STATUS_CHOICES = [
|
|
('in_progress', 'In Progress'),
|
|
('completed', 'Completed'),
|
|
('cancelled', 'Cancelled'),
|
|
]
|
|
|
|
mo_number = models.CharField(max_length=50, unique=True)
|
|
bom = models.ForeignKey(BillOfMaterial, on_delete=models.CASCADE)
|
|
quantity_to_produce = models.DecimalField(max_digits=10, decimal_places=2)
|
|
scheduled_start_date = models.DateField()
|
|
scheduled_end_date = models.DateField()
|
|
actual_start_date = models.DateField(null=True, blank=True)
|
|
actual_end_date = models.DateField(null=True, blank=True)
|
|
status = models.CharField(max_length=20, choices=STATUS_CHOICES, default='in_progress')
|
|
created_by = models.ForeignKey('accounts.User', on_delete=models.SET_NULL, null=True)
|
|
created_at = models.DateTimeField(auto_now_add=True)
|
|
updated_at = models.DateTimeField(auto_now=True)
|
|
|
|
def __str__(self):
|
|
return self.mo_number
|
|
|
|
class MOComponent(models.Model):
|
|
mo = models.ForeignKey(ManufacturingOrder, on_delete=models.CASCADE, related_name='components')
|
|
component = models.ForeignKey('inventory.Product', on_delete=models.CASCADE)
|
|
required_quantity = models.DecimalField(max_digits=10, decimal_places=2)
|
|
consumed_quantity = models.DecimalField(max_digits=10, decimal_places=2, default=0)
|
|
|
|
class Meta:
|
|
unique_together = ('mo', 'component')
|
|
```
|
|
|
|
### 6. Database Management Models
|
|
|
|
```python
|
|
# database_management/models.py
|
|
|
|
from django.db import models
|
|
|
|
class DatabaseBackup(models.Model):
|
|
BACKUP_STATUS = [
|
|
('pending', 'Pending'),
|
|
('in_progress', 'In Progress'),
|
|
('completed', 'Completed'),
|
|
('failed', 'Failed'),
|
|
]
|
|
|
|
filename = models.CharField(max_length=25)
|
|
file_path = models.CharField(max_length=500)
|
|
size = models.BigIntegerField()
|
|
status = models.CharField(max_length=20, choices=BACKUP_STATUS)
|
|
created_by = models.ForeignKey('accounts.User', on_delete=models.SET_NULL, null=True)
|
|
created_at = models.DateTimeField(auto_now_add=True)
|
|
completed_at = models.DateTimeField(null=True, blank=True)
|
|
|
|
def __str__(self):
|
|
return self.filename
|
|
```
|
|
|
|
### 7. Reporting Models
|
|
|
|
```python
|
|
# reports/models.py
|
|
|
|
from django.db import models
|
|
|
|
class ReportTemplate(models.Model):
|
|
name = models.CharField(max_length=100)
|
|
description = models.TextField(blank=True)
|
|
template_file = models.FileField(upload_to='report_templates/')
|
|
is_active = models.BooleanField(default=True)
|
|
created_at = models.DateTimeField(auto_now_add=True)
|
|
updated_at = models.DateTimeField(auto_now=True)
|
|
|
|
def __str__(self):
|
|
return self.name
|
|
|
|
class GeneratedReport(models.Model):
|
|
REPORT_FORMATS = [
|
|
('pdf', 'PDF'),
|
|
('excel', 'Excel'),
|
|
('csv', 'CSV'),
|
|
]
|
|
|
|
report_template = models.ForeignKey(ReportTemplate, on_delete=models.CASCADE)
|
|
name = models.CharField(max_length=200)
|
|
format = models.CharField(max_length=10, choices=REPORT_FORMATS)
|
|
file_path = models.CharField(max_length=500)
|
|
size = models.BigIntegerField()
|
|
generated_by = models.ForeignKey('accounts.User', on_delete=models.SET_NULL, null=True)
|
|
generated_at = models.DateTimeField(auto_now_add=True)
|
|
|
|
def __str__(self):
|
|
return self.name
|
|
```
|
|
|
|
### 8. Dashboard Models
|
|
|
|
```python
|
|
# dashboard/models.py
|
|
|
|
from django.db import models
|
|
|
|
class DashboardWidget(models.Model):
|
|
WIDGET_TYPES = [
|
|
('chart', 'Chart'),
|
|
('table', 'Table'),
|
|
('kpi', 'KPI'),
|
|
('summary', 'Summary'),
|
|
]
|
|
|
|
name = models.CharField(max_length=100)
|
|
title = models.CharField(max_length=200)
|
|
widget_type = models.CharField(max_length=20, choices=WIDGET_TYPES)
|
|
config = models.JSONField() # Store widget configuration as JSON
|
|
is_active = models.BooleanField(default=True)
|
|
order = models.IntegerField(default=0)
|
|
created_at = models.DateTimeField(auto_now_add=True)
|
|
updated_at = models.DateTimeField(auto_now=True)
|
|
|
|
class Meta:
|
|
ordering = ['order']
|
|
|
|
class UserDashboard(models.Model):
|
|
user = models.ForeignKey('accounts.User', on_delete=models.CASCADE)
|
|
widgets = models.ManyToManyField(DashboardWidget, through='UserDashboardWidget')
|
|
created_at = models.DateTimeField(auto_now_add=True)
|
|
updated_at = models.DateTimeField(auto_now=True)
|
|
|
|
class UserDashboardWidget(models.Model):
|
|
user_dashboard = models.ForeignKey(UserDashboard, on_delete=models.CASCADE)
|
|
widget = models.ForeignKey(DashboardWidget, on_delete=models.CASCADE)
|
|
position = models.IntegerField()
|
|
is_visible = models.BooleanField(default=True)
|
|
|
|
class Meta:
|
|
unique_together = ('user_dashboard', 'widget')
|
|
ordering = ['position'] |