Show sourcecode
The following files exists in this folder. Click to view.
public_html/smartkortet/database/
schema.sql
44 lines ASCII Windows (CRLF)
CREATE DATABASE IF NOT EXISTS matkortet CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci;
USE matkortet;
CREATE TABLE IF NOT EXISTS users (
id INT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
name VARCHAR(120) NOT NULL,
email VARCHAR(190) NOT NULL UNIQUE,
is_admin TINYINT(1) NOT NULL DEFAULT 0,
password_hash VARCHAR(255) NOT NULL,
created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
updated_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP
) ENGINE=InnoDB;
CREATE TABLE IF NOT EXISTS user_settings (
id INT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
user_id INT UNSIGNED NOT NULL UNIQUE,
starting_balance DECIMAL(10,2) NOT NULL DEFAULT 0,
period_start DATE NOT NULL,
period_end DATE NOT NULL,
updated_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
theme_preference ENUM('light', 'dark') NOT NULL DEFAULT 'dark',
CONSTRAINT fk_user_settings_user FOREIGN KEY (user_id) REFERENCES users(id) ON DELETE CASCADE
) ENGINE=InnoDB;
CREATE TABLE IF NOT EXISTS favorite_places (
id INT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
user_id INT UNSIGNED NOT NULL,
name VARCHAR(160) NOT NULL,
created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
UNIQUE KEY uq_user_place (user_id, name),
CONSTRAINT fk_favorite_places_user FOREIGN KEY (user_id) REFERENCES users(id) ON DELETE CASCADE
) ENGINE=InnoDB;
CREATE TABLE IF NOT EXISTS spend_entries (
id INT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
user_id INT UNSIGNED NOT NULL,
amount DECIMAL(8,2) NOT NULL,
spent_on DATE NOT NULL,
place_name VARCHAR(160) NULL,
created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
CONSTRAINT fk_spend_entries_user FOREIGN KEY (user_id) REFERENCES users(id) ON DELETE CASCADE,
INDEX idx_spend_entries_user_date (user_id, spent_on)
) ENGINE=InnoDB;