36 lines
1.1 KiB
MySQL
36 lines
1.1 KiB
MySQL
|
-- BMC Hub Database Schema
|
||
|
|
-- PostgreSQL 16
|
||
|
|
|
||
|
|
-- Customers table
|
||
|
|
CREATE TABLE IF NOT EXISTS customers (
|
||
|
|
id SERIAL PRIMARY KEY,
|
||
|
|
name VARCHAR(255) NOT NULL,
|
||
|
|
email VARCHAR(255),
|
||
|
|
phone VARCHAR(50),
|
||
|
|
address TEXT,
|
||
|
|
created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
|
||
|
|
updated_at TIMESTAMP,
|
||
|
|
deleted_at TIMESTAMP
|
||
|
|
);
|
||
|
|
|
||
|
|
-- Hardware table
|
||
|
|
CREATE TABLE IF NOT EXISTS hardware (
|
||
|
|
id SERIAL PRIMARY KEY,
|
||
|
|
serial_number VARCHAR(255) NOT NULL UNIQUE,
|
||
|
|
model VARCHAR(255) NOT NULL,
|
||
|
|
customer_id INTEGER REFERENCES customers(id),
|
||
|
|
created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
|
||
|
|
deleted_at TIMESTAMP
|
||
|
|
);
|
||
|
|
|
||
|
|
-- Create indexes
|
||
|
|
CREATE INDEX IF NOT EXISTS idx_customers_email ON customers(email);
|
||
|
|
CREATE INDEX IF NOT EXISTS idx_hardware_customer ON hardware(customer_id);
|
||
|
|
CREATE INDEX IF NOT EXISTS idx_hardware_serial ON hardware(serial_number);
|
||
|
|
|
||
|
|
-- Insert sample data
|
||
|
|
INSERT INTO customers (name, email, phone, address) VALUES
|
||
|
|
('BMC Networks', 'info@bmcnetworks.dk', '+45 12345678', 'København'),
|
||
|
|
('Test Customer', 'test@example.com', '+45 87654321', 'Aarhus')
|
||
|
|
ON CONFLICT DO NOTHING;
|