# MAGNUM ERP ## Database Design Document **Project:** MAGNUM ERP **Version:** 0.1 **Database:** PostgreSQL --- # 1. Purpose This document defines the database structure for MAGNUM ERP. The database is designed using a relational model where every business object is linked through primary and foreign key relationships. The objective is to ensure data consistency, scalability, and traceability across the complete engineering workflow. --- # 2. Core Database Architecture ```text Users │ ├── Roles ├── Permissions │ Customers │ ├── Contacts ├── Enquiries │ ├── Tasks │ ├── BOQs │ │ ├── BOQ Items │ │ ├── Materials │ │ ├── Labour Items │ │ └── Equipment Items │ │ │ ├── Estimates │ ├── Quotations │ │ ├── Quote Items │ │ └── Quote Revisions │ │ │ └── Documents │ Projects │ ├── Project Tasks ├── Drawings ├── Purchase Orders ├── Deliveries ├── Variations ├── Photos ├── Site Notes └── Documents Suppliers │ ├── Materials ├── Purchase Orders └── Deliveries ``` --- # 3. Phase 1 Database Tables ## Users Stores all system users. Fields * id * username * password_hash * first_name * last_name * email * role_id * active * created_at * updated_at --- ## Roles Defines user roles. Fields * id * role_name * description Examples Administrator Technical Manager Estimator Designer Project Manager Purchasing Officer Management --- ## Customers Stores company information. Fields * id * customer_code * company_name * registration_number * vat_number * address * city * postcode * country * phone * email * website * status * created_at --- ## Contacts Customer contacts. Fields * id * customer_id * first_name * last_name * job_title * mobile * office_phone * email --- ## Enquiries Every job starts here. Fields * id * enquiry_number * customer_id * title * description * enquiry_date * due_date * assigned_to * status * priority * estimated_value * created_by * created_at Status Values * New * Assigned * Site Visit * Design * Estimating * Awaiting Approval * Quoted * Won * Lost * Cancelled --- ## Tasks Tasks linked to enquiries or projects. Fields * id * task_number * enquiry_id * project_id * assigned_to * task_name * description * priority * status * start_date * due_date * completed_date * created_at Status Values * Not Started * In Progress * Waiting * Completed * Cancelled --- # 4. Phase 2 Database Tables ## Materials Stores engineering materials. Fields * id * material_code * description * category * unit * unit_cost * supplier_id * last_updated --- ## Labour Rates Stores labour categories. Fields * id * labour_code * description * hourly_rate * overtime_rate * productivity_factor --- ## Equipment Plant and equipment rates. Fields * id * equipment_code * description * daily_rate * hourly_rate --- ## Suppliers Supplier database. Fields * id * supplier_code * company_name * address * phone * email * contact_person --- # 5. Phase 3 Database Tables ## BOQs Bill of Quantities header. Fields * id * boq_number * enquiry_id * revision * created_by * created_at --- ## BOQ Items Fields * id * boq_id * section * item_number * description * quantity * unit * material_cost * labour_cost * equipment_cost * total_cost --- ## Estimates Stores pricing calculations. Fields * id * boq_id * subtotal * overhead * profit * contingency * final_price --- ## Quotations Quotation header. Fields * id * quotation_number * enquiry_id * revision * quotation_date * valid_until * subtotal * vat * total * status --- ## Quote Revisions Tracks all quotation revisions. Fields * id * quotation_id * revision_number * revision_date * comments * revised_by --- # 6. Phase 4 Database Tables ## Projects Created automatically when a quotation is accepted. Fields * id * project_number * enquiry_id * customer_id * project_name * project_manager * start_date * completion_date * status * contract_value --- ## Drawings Engineering drawings. Fields * id * project_id * drawing_number * revision * title * designer * checker * approver * file_path --- ## Purchase Orders Fields * id * po_number * supplier_id * project_id * order_date * delivery_date * status * total --- ## Deliveries Fields * id * purchase_order_id * delivery_date * received_by * status --- ## Documents Stores project files. Fields * id * project_id * enquiry_id * customer_id * document_type * file_name * file_path * uploaded_by * uploaded_at --- # 7. Audit Tables ## Activity Log Stores user actions. Fields * id * user_id * action * table_name * record_id * timestamp --- # 8. Entity Relationships Customer 1 Customer → Many Contacts 1 Customer → Many Enquiries 1 Enquiry → Many Tasks 1 Enquiry → One BOQ 1 BOQ → Many BOQ Items 1 BOQ → One Estimate 1 Enquiry → Many Quotations 1 Accepted Quotation → One Project 1 Project → Many Tasks 1 Project → Many Drawings 1 Project → Many Purchase Orders 1 Project → Many Documents 1 Supplier → Many Materials 1 Supplier → Many Purchase Orders --- # 9. Database Naming Standards * Table names use lowercase and plural form. * Primary key: id * Foreign keys end with _id * Dates use *_date * Timestamps use created_at and updated_at. * Status fields use predefined values. * Never store plain-text passwords. * Use UUIDs in the future if distributed deployments require them. --- # 10. Future Enhancements * Cost codes * Timesheets * Plant management * Fleet management * Health & Safety records * Quality inspections * Asset maintenance * Client portals * Mobile field reporting * Business intelligence dashboards * AI-assisted estimating ``` ```