Documentation Index
Fetch the complete documentation index at: https://docs.mejik.web.id/llms.txt
Use this file to discover all available pages before exploring further.
1. Core Design Principles
The best approach for this system is to use an inheritance model, also known as a superclass/subclass or generalization/specialization model.
- General Asset (Superclass): A core
Asset entity will hold all the common attributes that every asset has, regardless of its type (e.g., Asset Tag, Purchase Date, Location, Status, Owner).
- Specific Assets (Subclasses): Specialized entities like
Vehicle, Machinery, and ITAsset will inherit from the base Asset and add their own specific attributes (e.g., a Vehicle has a VIN, an ITAsset has a MAC address).
For the database implementation, we will use a Class Table Inheritance (CTI) pattern. This means:
- A base
assets table for the common fields.
- Separate tables for each specific asset type (
vehicles, it_assets, etc.).
- A one-to-one relationship between the base
assets table and each specific table. This is clean, normalized, and highly scalable.
2. Entity-Relationship (ER) Structure
Here is a description of the tables and their relationships.
+----------------+ +------------------+ +----------------+
| Statuses | | Locations | | Manufacturers |
|----------------| |------------------| |----------------|
| PK status_id |<--+ | PK location_id |<--+ | PK manuf_id |
| name | | | name | | | name |
+----------------+ | | address_line1 | | +----------------+
| | city | |
| | country | |
+----------------+ | +------------------+ | +----------------+
| Users | | | | Departments |
|----------------| | | |----------------|
| PK user_id |<--+ +-->| PK dept_id |
| first_name | | | name |
| last_name | +------------------------------>| FK manager_id |
| email | +----------------+
| FK department_id|
+----------------+
^
|
| (Superclass)
+-------------------------+ +-------------------+
| | Assets |
| |-------------------|
+---| PK asset_id |
| asset_tag (U) |
| name |
| purchase_date |
| cost |
| asset_type | --(Discriminator: 'VEHICLE', 'IT_ASSET')
| FK status_id |
| FK location_id |
| FK manufacturer_id|
| FK assigned_to_user_id (nullable) |
+-------------------+
^
| (Inheritance / 1-to-1 Relationship)
+-------------------+-------------------+
| |
(Subclass) | | (Subclass)
+------------------------+-----+ +-------------+----------------+
| Vehicles | | IT_Assets |
|----------------------------| |------------------------------|
| PK/FK asset_id | | PK/FK asset_id |
| vin (U) | | hostname |
| license_plate | | mac_address (U) |
| make | | ip_address |
| model | | os (Operating System) |
| year | | cpu |
| mileage | | ram_gb |
| fuel_type | | storage_gb |
+----------------------------+ +------------------------------+
(U) = Unique Constraint
Relationship Explanation:
- One-to-Many:
- One
Location can have many Assets.
- One
Status can apply to many Assets.
- One
Manufacturer can produce many Assets.
- One
User can be assigned many Assets.
- One
Department can have many Users.
- One-to-One (Inheritance):
- Each record in the
Assets table corresponds to exactly one record in one of the subclass tables (Vehicles, IT_Assets, Machinery, etc.). The asset_id is both the Primary Key (PK) and a Foreign Key (FK) in the subclass tables, enforcing this relationship.
- The
asset_type field in the Assets table acts as a discriminator, telling the application which specific table to join with to get the full details of the asset.
3. Entity Object Structure (Fields & Data Types)
This represents how you might structure these entities as objects in your application code (e.g., in JSON, a class definition, or a struct).
3.1. Lookup/Reference Objects
These are simple objects that provide context to the main asset objects.
// Status Object
{
"status_id": "int (PK)",
"name": "string" // e.g., 'In Use', 'In Storage', 'Under Maintenance', 'Decommissioned'
}
// Location Object
{
"location_id": "int (PK)",
"name": "string", // e.g., 'Building A, 4th Floor', 'Warehouse Section B'
"address_line1": "string",
"city": "string",
"country": "string"
}
// Manufacturer Object
{
"manufacturer_id": "int (PK)",
"name": "string" // e.g., 'Dell', 'Ford', 'Caterpillar'
}
// User Object
{
"user_id": "int (PK)",
"first_name": "string",
"last_name": "string",
"email": "string (unique)",
"department_id": "int (FK to Department)"
}
3.2. Core Asset Object (Superclass)
This is the base object that all other asset types will extend.
// Base Asset Object
{
"asset_id": "int (PK)",
"asset_tag": "string (unique)", // e.g., 'IT-LPT-00123' or 'VEH-TRK-0045'
"name": "string", // e.g., 'Dell Latitude 7420' or 'Ford F-150'
"asset_type": "string", // Discriminator: 'VEHICLE', 'IT_ASSET', 'MACHINERY'
"purchase_date": "date",
"cost": "decimal(10, 2)",
"warranty_expiry_date": "date",
// Relationships (represented by IDs and nested objects)
"status_id": "int (FK)",
"status": { ...Status Object... }, // Populated on retrieval
"location_id": "int (FK)",
"location": { ...Location Object... }, // Populated on retrieval
"manufacturer_id": "int (FK)",
"manufacturer": { ...Manufacturer Object... }, // Populated on retrieval
"assigned_to_user_id": "int (FK, nullable)",
"assigned_to": { ...User Object... }, // Populated on retrieval
// Audit Fields
"created_at": "datetime",
"updated_at": "datetime"
}
3.3. Specific Asset Objects (Subclasses)
These objects inherit all the fields from the base Asset and add their own specific details.
General Asset Class Example: Vehicle
// Vehicle Object (extends Asset)
{
// --- Inherited fields from Asset ---
"asset_id": 101,
"asset_tag": "VEH-TRK-0045",
"name": "Ford F-150",
"asset_type": "VEHICLE",
"purchase_date": "2022-08-15",
"cost": 45000.00,
"status": { "status_id": 1, "name": "In Use" },
"location": { "location_id": 5, "name": "Main Office Parking Lot" },
"manufacturer": { "manufacturer_id": 22, "name": "Ford" },
"assigned_to": { "user_id": 15, "first_name": "John", "last_name": "Doe" },
// ... and all other base asset fields ...
// --- Vehicle-specific fields ---
"vin": "string (unique)", // Vehicle Identification Number
"license_plate": "string",
"make": "string",
"model": "string",
"year": "int",
"mileage": "int",
"fuel_type": "string", // e.g., 'Gasoline', 'Diesel', 'Electric'
"last_service_date": "date"
}
// IT Asset Object (extends Asset)
{
// --- Inherited fields from Asset ---
"asset_id": 250,
"asset_tag": "IT-LPT-00123",
"name": "Dell Latitude 7420",
"asset_type": "IT_ASSET",
"purchase_date": "2023-01-20",
"cost": 1850.50,
"status": { "status_id": 1, "name": "In Use" },
"location": { "location_id": 2, "name": "Building A, 4th Floor" },
"manufacturer": { "manufacturer_id": 10, "name": "Dell" },
"assigned_to": { "user_id": 34, "first_name": "Jane", "last_name": "Smith" },
// ... and all other base asset fields ...
// --- IT Asset-specific fields ---
"hostname": "string",
"mac_address": "string (unique)",
"ip_address": "string",
"os": "string", // Operating System, e.g., 'Windows 11 Pro'
"cpu": "string", // e.g., 'Intel Core i7-1185G7'
"ram_gb": "int",
"storage_type": "string", // e.g., 'SSD', 'HDD'
"storage_gb": "int"
}
Summary of the Design
This high-level design provides a robust and scalable foundation for an Asset Management System:
- Normalized: Avoids data duplication by separating common and specific attributes. Lookup tables (
Statuses, Locations) ensure data consistency.
- Scalable: Adding a new asset type (e.g.,
Furniture) is easy. You just create a new furniture table and add a new asset_type discriminator value. No changes are needed to the core Assets table or existing specific tables.
- Clear and Maintainable: The separation of concerns between the base class and subclasses makes the application logic easier to write and maintain. When you retrieve an asset, you query the base
Assets table and then JOIN to the specific table based on the asset_type to get the complete object.
