27 lines
1.2 KiB
SQL
27 lines
1.2 KiB
SQL
CREATE TABLE IF NOT EXISTS `assets` (
|
|
`id` INT AUTO_INCREMENT PRIMARY KEY,
|
|
`name` VARCHAR(255) NOT NULL,
|
|
`asset_tag` VARCHAR(255) UNIQUE NOT NULL,
|
|
`serial_number` VARCHAR(255),
|
|
`model` VARCHAR(255),
|
|
`manufacturer` VARCHAR(255),
|
|
`category` VARCHAR(255),
|
|
`status` VARCHAR(50) NOT NULL,
|
|
`location` VARCHAR(255),
|
|
`purchase_date` DATE,
|
|
`purchase_cost` DECIMAL(10, 2),
|
|
`warranty_end` DATE,
|
|
`vendor` VARCHAR(255),
|
|
`assigned_to` INT,
|
|
`notes` TEXT,
|
|
`created_at` TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
|
|
`updated_at` TIMESTAMP DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP
|
|
);
|
|
|
|
-- Seed data
|
|
INSERT INTO `assets` (`name`, `asset_tag`, `status`, `location`, `purchase_date`, `assigned_to`, `manufacturer`, `model`) VALUES
|
|
('Laptop', 'ASSET-001', 'In Service', 'Office A', '2023-01-15', 1, 'Dell', 'XPS 15'),
|
|
('Monitor', 'ASSET-002', 'In Service', 'Office A', '2023-01-15', 1, 'Dell', 'UltraSharp 27'),
|
|
('Keyboard', 'ASSET-003', 'In Service', 'Office B', '2023-02-20', 2, 'Logitech', 'MX Keys'),
|
|
('Mouse', 'ASSET-004', 'Under Repair', 'IT Department', '2023-02-20', NULL, 'Logitech', 'MX Master 3'),
|
|
('Projector', 'ASSET-005', 'Retired', 'Storage', '2020-05-10', NULL, 'Epson', 'PowerLite 1781W'); |