National Property Management System - Database Tables
Core Property Management Tables
1. Properties (res_property)
sql
-- Main property registry table
id SERIAL PRIMARY KEY
name VARCHAR(255) NOT NULL -- Property identifier/name
property_code VARCHAR(50) UNIQUE NOT NULL -- Unique property code
property_type_id INTEGER REFERENCES property_type(id)
property_category_id INTEGER REFERENCES property_category(id)
property_status_id INTEGER REFERENCES property_status(id)
title_deed_number VARCHAR(100) UNIQUE -- Official title deed number
area_sqm DECIMAL(15,2) -- Area in square meters
area_hectares DECIMAL(15,2) -- Area in hectares
address TEXT
wilaya_id INTEGER REFERENCES res_wilaya(id) -- Province
daira_id INTEGER REFERENCES res_daira(id) -- District
commune_id INTEGER REFERENCES res_commune(id) -- Municipality
postal_code VARCHAR(10)
latitude DECIMAL(10,8) -- GPS coordinates
longitude DECIMAL(11,8)
description TEXT
market_value DECIMAL(15,2) -- Current market value
tax_value DECIMAL(15,2) -- Tax assessment value
date_registered DATE
date_last_updated TIMESTAMP
registered_by_id INTEGER REFERENCES res_users(id)
created_date TIMESTAMP DEFAULT CURRENT_TIMESTAMP
updated_date TIMESTAMP DEFAULT CURRENT_TIMESTAMP
active BOOLEAN DEFAULT TRUE
2. Property Types (property_type)
sql
id SERIAL PRIMARY KEY
name VARCHAR(100) NOT NULL -- Residential, Commercial, Agricultu
code VARCHAR(20) UNIQUE NOT NULL
description TEXT
active BOOLEAN DEFAULT TRUE
3. Property Categories (property_category)
sql
id SERIAL PRIMARY KEY
name VARCHAR(100) NOT NULL -- House, Apartment, Office, Land, et
property_type_id INTEGER REFERENCES property_type(id)
code VARCHAR(20) UNIQUE NOT NULL
description TEXT
active BOOLEAN DEFAULT TRUE
4. Property Status (property_status)
sql
id SERIAL PRIMARY KEY
name VARCHAR(50) NOT NULL -- Available, Sold, Disputed, Frozen,
code VARCHAR(20) UNIQUE NOT NULL
color VARCHAR(7) -- Hex color for UI
description TEXT
active BOOLEAN DEFAULT TRUE
Owner Management Tables
5. Property Owners (res_property_owner)
sql
id SERIAL PRIMARY KEY
partner_id INTEGER REFERENCES res_partner(id) -- Link to contact/citizen
national_id VARCHAR(20) UNIQUE -- National ID number
passport_number VARCHAR(20)
birth_date DATE
birth_place VARCHAR(100)
nationality_id INTEGER REFERENCES res_country(id)
marital_status VARCHAR(20)
profession VARCHAR(100)
phone VARCHAR(20)
mobile VARCHAR(20)
email VARCHAR(100)
address TEXT
is_legal_entity BOOLEAN DEFAULT FALSE -- Individual or company
company_registration VARCHAR(50) -- For legal entities
tax_number VARCHAR(30)
created_date TIMESTAMP DEFAULT CURRENT_TIMESTAMP
active BOOLEAN DEFAULT TRUE
6. Property Ownership (property_ownership)
sql
id SERIAL PRIMARY KEY
property_id INTEGER REFERENCES res_property(id)
owner_id INTEGER REFERENCES res_property_owner(id)
ownership_type_id INTEGER REFERENCES ownership_type(id)
ownership_percentage DECIMAL(5,2) DEFAULT 100.00 -- Percentage of ownership
start_date DATE NOT NULL
end_date DATE
is_primary_owner BOOLEAN DEFAULT FALSE
acquisition_method_id INTEGER REFERENCES acquisition_method(id)
deed_number VARCHAR(100)
registration_date DATE
notes TEXT
created_date TIMESTAMP DEFAULT CURRENT_TIMESTAMP
active BOOLEAN DEFAULT TRUE
7. Ownership Types (ownership_type)
sql
id SERIAL PRIMARY KEY
name VARCHAR(50) NOT NULL -- Full, Partial, Usufruct, etc.
code VARCHAR(20) UNIQUE NOT NULL
description TEXT
active BOOLEAN DEFAULT TRUE
8. Acquisition Methods (acquisition_method)
sql
id SERIAL PRIMARY KEY
name VARCHAR(50) NOT NULL -- Purchase, Inheritance, Gift, etc.
code VARCHAR(20) UNIQUE NOT NULL
requires_payment BOOLEAN DEFAULT TRUE
description TEXT
active BOOLEAN DEFAULT TRUE
Transaction Management Tables
9. Property Transactions (property_transaction)
sql
id SERIAL PRIMARY KEY
transaction_number VARCHAR(50) UNIQUE NOT NULL
property_id INTEGER REFERENCES res_property(id)
transaction_type_id INTEGER REFERENCES transaction_type(id)
seller_id INTEGER REFERENCES res_property_owner(id)
buyer_id INTEGER REFERENCES res_property_owner(id)
transaction_date DATE NOT NULL
registration_date DATE
sale_price DECIMAL(15,2)
tax_amount DECIMAL(15,2)
fees_amount DECIMAL(15,2)
total_amount DECIMAL(15,2)
payment_method_id INTEGER REFERENCES payment_method(id)
transaction_status_id INTEGER REFERENCES transaction_status(id)
contract_number VARCHAR(100)
notary_id INTEGER REFERENCES res_partner(id) -- Notary public
processed_by_id INTEGER REFERENCES res_users(id)
notes TEXT
created_date TIMESTAMP DEFAULT CURRENT_TIMESTAMP
active BOOLEAN DEFAULT TRUE
10. Transaction Types (transaction_type)
sql
id SERIAL PRIMARY KEY
name VARCHAR(50) NOT NULL -- Sale, Transfer, Inheritance, etc.
code VARCHAR(20) UNIQUE NOT NULL
requires_payment BOOLEAN DEFAULT TRUE
tax_rate DECIMAL(5,2) -- Tax percentage
description TEXT
active BOOLEAN DEFAULT TRUE
11. Transaction Status (transaction_status)
sql
id SERIAL PRIMARY KEY
name VARCHAR(50) NOT NULL -- Pending, Approved, Completed, Canc
code VARCHAR(20) UNIQUE NOT NULL
color VARCHAR(7) -- Hex color for UI
is_final BOOLEAN DEFAULT FALSE -- Cannot be changed if true
description TEXT
active BOOLEAN DEFAULT TRUE
Document Management Tables
12. Property Documents (property_document)
sql
id SERIAL PRIMARY KEY
property_id INTEGER REFERENCES res_property(id)
document_type_id INTEGER REFERENCES document_type(id)
document_number VARCHAR(100)
title VARCHAR(255) NOT NULL
file_name VARCHAR(255)
file_path TEXT
file_size INTEGER -- Size in bytes
mime_type VARCHAR(100)
issue_date DATE
expiry_date DATE
issued_by VARCHAR(100) -- Issuing authority
document_status_id INTEGER REFERENCES document_status(id)
uploaded_by_id INTEGER REFERENCES res_users(id)
notes TEXT
created_date TIMESTAMP DEFAULT CURRENT_TIMESTAMP
active BOOLEAN DEFAULT TRUE
13. Document Types (document_type)
sql
id SERIAL PRIMARY KEY
name VARCHAR(100) NOT NULL -- Title Deed, Survey Plan, etc.
code VARCHAR(20) UNIQUE NOT NULL
category VARCHAR(50) -- Legal, Technical, Financial
is_mandatory BOOLEAN DEFAULT FALSE
allowed_file_types TEXT -- JSON array of allowed extensions
max_file_size INTEGER -- Maximum file size in MB
description TEXT
active BOOLEAN DEFAULT TRUE
14. Document Status (document_status)
sql
id SERIAL PRIMARY KEY
name VARCHAR(50) NOT NULL -- Valid, Expired, Cancelled, etc.
code VARCHAR(20) UNIQUE NOT NULL
color VARCHAR(7)
description TEXT
active BOOLEAN DEFAULT TRUE
Legal & Compliance Tables
15. Legal Cases (legal_case)
sql
id SERIAL PRIMARY KEY
case_number VARCHAR(50) UNIQUE NOT NULL
property_id INTEGER REFERENCES res_property(id)
case_type_id INTEGER REFERENCES legal_case_type(id)
plaintiff_id INTEGER REFERENCES res_property_owner(id)
defendant_id INTEGER REFERENCES res_property_owner(id)
court_id INTEGER REFERENCES res_partner(id) -- Court information
case_status_id INTEGER REFERENCES legal_case_status(id)
filing_date DATE NOT NULL
hearing_date DATE
resolution_date DATE
case_description TEXT
resolution_description TEXT
lawyer_id INTEGER REFERENCES res_partner(id)
judge_name VARCHAR(100)
created_by_id INTEGER REFERENCES res_users(id)
created_date TIMESTAMP DEFAULT CURRENT_TIMESTAMP
active BOOLEAN DEFAULT TRUE
16. Legal Case Types (legal_case_type)
sql
id SERIAL PRIMARY KEY
name VARCHAR(100) NOT NULL -- Ownership Dispute, Boundary Dispute
code VARCHAR(20) UNIQUE NOT NULL
priority_level INTEGER DEFAULT 1 -- 1=Low, 2=Medium, 3=High
description TEXT
active BOOLEAN DEFAULT TRUE
17. Legal Case Status (legal_case_status)
sql
id SERIAL PRIMARY KEY
name VARCHAR(50) NOT NULL -- Open, In Progress, Resolved, etc.
code VARCHAR(20) UNIQUE NOT NULL
color VARCHAR(7)
is_final BOOLEAN DEFAULT FALSE
description TEXT
active BOOLEAN DEFAULT TRUE
Geographic Information Tables
18. Administrative Divisions - Wilaya (res_wilaya)
sql
id SERIAL PRIMARY KEY
name VARCHAR(100) NOT NULL -- Province name
code VARCHAR(10) UNIQUE NOT NULL -- Official code
country_id INTEGER REFERENCES res_country(id)
area_sqkm DECIMAL(10,2)
population INTEGER
capital VARCHAR(100)
created_date TIMESTAMP DEFAULT CURRENT_TIMESTAMP
active BOOLEAN DEFAULT TRUE
19. Administrative Divisions - Daira (res_daira)
sql
id SERIAL PRIMARY KEY
name VARCHAR(100) NOT NULL -- District name
code VARCHAR(10) UNIQUE NOT NULL
wilaya_id INTEGER REFERENCES res_wilaya(id)
area_sqkm DECIMAL(10,2)
population INTEGER
created_date TIMESTAMP DEFAULT CURRENT_TIMESTAMP
active BOOLEAN DEFAULT TRUE
20. Administrative Divisions - Commune (res_commune)
sql
id SERIAL PRIMARY KEY
name VARCHAR(100) NOT NULL -- Municipality name
code VARCHAR(10) UNIQUE NOT NULL
daira_id INTEGER REFERENCES res_daira(id)
wilaya_id INTEGER REFERENCES res_wilaya(id)
area_sqkm DECIMAL(10,2)
population INTEGER
postal_code VARCHAR(10)
created_date TIMESTAMP DEFAULT CURRENT_TIMESTAMP
active BOOLEAN DEFAULT TRUE
Service Request Tables
21. Service Requests (service_request)
sql
id SERIAL PRIMARY KEY
request_number VARCHAR(50) UNIQUE NOT NULL
requester_id INTEGER REFERENCES res_property_owner(id)
property_id INTEGER REFERENCES res_property(id)
service_type_id INTEGER REFERENCES service_type(id)
request_status_id INTEGER REFERENCES request_status(id)
priority_id INTEGER REFERENCES request_priority(id)
request_date DATE NOT NULL
required_date DATE
completion_date DATE
description TEXT
internal_notes TEXT
assigned_to_id INTEGER REFERENCES res_users(id)
department_id INTEGER REFERENCES hr_department(id)
estimated_cost DECIMAL(12,2)
actual_cost DECIMAL(12,2)
created_date TIMESTAMP DEFAULT CURRENT_TIMESTAMP
active BOOLEAN DEFAULT TRUE
22. Service Types (service_type)
sql
id SERIAL PRIMARY KEY
name VARCHAR(100) NOT NULL -- Property Certificate, Ownership Tra
code VARCHAR(20) UNIQUE NOT NULL
category VARCHAR(50) -- Certificate, Transfer, Inquiry
processing_time_days INTEGER -- Standard processing time
base_fee DECIMAL(12,2) -- Standard fee
requires_documents TEXT -- JSON array of required document typ
description TEXT
active BOOLEAN DEFAULT TRUE
23. Request Status (request_status)
sql
id SERIAL PRIMARY KEY
name VARCHAR(50) NOT NULL -- Submitted, Under Review, Approved,
code VARCHAR(20) UNIQUE NOT NULL
color VARCHAR(7)
sequence INTEGER -- Order in workflow
is_final BOOLEAN DEFAULT FALSE
description TEXT
active BOOLEAN DEFAULT TRUE
24. Request Priority (request_priority)
sql
id SERIAL PRIMARY KEY
name VARCHAR(30) NOT NULL -- Low, Normal, High, Urgent
code VARCHAR(10) UNIQUE NOT NULL
color VARCHAR(7)
response_time_hours INTEGER -- Expected response time
sequence INTEGER
active BOOLEAN DEFAULT TRUE
Financial Management Tables
25. Payment Methods (payment_method)
sql
id SERIAL PRIMARY KEY
name VARCHAR(50) NOT NULL -- Cash, Bank Transfer, Online, etc.
code VARCHAR(20) UNIQUE NOT NULL
is_online BOOLEAN DEFAULT FALSE
requires_verification BOOLEAN DEFAULT FALSE
processing_fee_rate DECIMAL(5,4) -- Fee percentage
description TEXT
active BOOLEAN DEFAULT TRUE
26. Fee Structure (fee_structure)
sql
id SERIAL PRIMARY KEY
service_type_id INTEGER REFERENCES service_type(id)
property_type_id INTEGER REFERENCES property_type(id)
fee_name VARCHAR(100) NOT NULL
base_amount DECIMAL(12,2)
percentage_rate DECIMAL(5,4) -- Percentage of property value
minimum_amount DECIMAL(12,2)
maximum_amount DECIMAL(12,2)
effective_date DATE NOT NULL
expiry_date DATE
created_date TIMESTAMP DEFAULT CURRENT_TIMESTAMP
active BOOLEAN DEFAULT TRUE
27. Payments (property_payment)
sql
id SERIAL PRIMARY KEY
payment_number VARCHAR(50) UNIQUE NOT NULL
service_request_id INTEGER REFERENCES service_request(id)
transaction_id INTEGER REFERENCES property_transaction(id)
payer_id INTEGER REFERENCES res_property_owner(id)
payment_method_id INTEGER REFERENCES payment_method(id)
amount DECIMAL(12,2) NOT NULL
payment_date DATE NOT NULL
due_date DATE
reference_number VARCHAR(100) -- Bank reference or receipt number
payment_status_id INTEGER REFERENCES payment_status(id)
notes TEXT
processed_by_id INTEGER REFERENCES res_users(id)
created_date TIMESTAMP DEFAULT CURRENT_TIMESTAMP
active BOOLEAN DEFAULT TRUE
28. Payment Status (payment_status)
sql
id SERIAL PRIMARY KEY
name VARCHAR(50) NOT NULL -- Pending, Confirmed, Failed, Refunde
code VARCHAR(20) UNIQUE NOT NULL
color VARCHAR(7)
is_paid BOOLEAN DEFAULT FALSE
description TEXT
active BOOLEAN DEFAULT TRUE
System Configuration Tables
29. System Settings (system_setting)
sql
id SERIAL PRIMARY KEY
key VARCHAR(100) UNIQUE NOT NULL
value TEXT
data_type VARCHAR(20) -- string, integer, boolean, json
category VARCHAR(50) -- General, Security, Integration
description TEXT
is_encrypted BOOLEAN DEFAULT FALSE
updated_by_id INTEGER REFERENCES res_users(id)
updated_date TIMESTAMP DEFAULT CURRENT_TIMESTAMP
30. Audit Log (audit_log)
sql
id SERIAL PRIMARY KEY
table_name VARCHAR(100) NOT NULL
record_id INTEGER NOT NULL
operation VARCHAR(10) NOT NULL -- INSERT, UPDATE, DELETE
old_values JSONB
new_values JSONB
user_id INTEGER REFERENCES res_users(id)
ip_address VARCHAR(45)
user_agent TEXT
timestamp TIMESTAMP DEFAULT CURRENT_TIMESTAMP
Integration Tables
31. External System Integration (external_integration)
sql
id SERIAL PRIMARY KEY
system_name VARCHAR(100) NOT NULL -- National ID, Banking, Tax Authority
integration_type VARCHAR(50) -- API, File Transfer, Database
endpoint_url VARCHAR(500)
api_key VARCHAR(255)
is_active BOOLEAN DEFAULT TRUE
last_sync_date TIMESTAMP
sync_frequency_hours INTEGER
configuration JSONB -- System-specific config
created_date TIMESTAMP DEFAULT CURRENT_TIMESTAMP
32. Data Synchronization Log (sync_log)
sql
id SERIAL PRIMARY KEY
integration_id INTEGER REFERENCES external_integration(id)
sync_type VARCHAR(50) -- Import, Export, Bidirectional
records_processed INTEGER
records_successful INTEGER
records_failed INTEGER
start_time TIMESTAMP
end_time TIMESTAMP
status VARCHAR(20) -- Success, Failed, Partial
error_message TEXT
log_details JSONB
Indexes and Constraints
sql
-- Performance indexes
CREATE INDEX idx_property_code ON res_property(property_code);
CREATE INDEX idx_property_owner_national_id ON res_property_owner(national_id);
CREATE INDEX idx_property_location ON res_property(wilaya_id, daira_id, commune_id);
CREATE INDEX idx_transaction_date ON property_transaction(transaction_date);
CREATE INDEX idx_service_request_status ON service_request(request_status_id, request_date);
CREATE INDEX idx_property_coordinates ON res_property(latitude, longitude);
CREATE INDEX idx_audit_log_table_record ON audit_log(table_name, record_id);
-- Foreign key constraints and check constraints
ALTER TABLE property_ownership ADD CONSTRAINT chk_ownership_percentage
CHECK (ownership_percentage > 0 AND ownership_percentage <= 100);
ALTER TABLE fee_structure ADD CONSTRAINT chk_fee_dates
CHECK (expiry_date IS NULL OR expiry_date > effective_date);
ALTER TABLE res_property ADD CONSTRAINT chk_property_area
CHECK (area_sqm > 0);
