source: other-projects/the-macronizer/trunk/src/sql-scripts/MySQL_Code.sql@ 32742

SET names utf8mb4;

CREATE database Macroniser;

USE Macroniser;


CREATE TABLE IF NOT EXISTS MarkedWords (
    word_id INT AUTO_INCREMENT,
    marked_word varchar (255) UNIQUE,
    PRIMARY KEY (word_id)
    );

CREATE TABLE IF NOT EXISTS EntryTimes (
    time_id INT AUTO_INCREMENT,
    entry_time TIME NOT NULL UNIQUE,
    PRIMARY KEY (time_id)
    );

CREATE TABLE IF NOT EXISTS EntryDates (
    date_id INT AUTO_INCREMENT,
    entry_date DATE NOT NULL UNIQUE,
    PRIMARY KEY (date_id)
    );

CREATE TABLE IF NOT EXISTS Occurrences (
    occurrence_id INT AUTO_INCREMENT,
    word_id INT,
    time_id INT,
    date_id INT,
    PRIMARY KEY (occurrence_id),
    FOREIGN KEY (word_id) REFERENCES MarkedWords(word_id),
    FOREIGN KEY (time_id) REFERENCES EntryTimes(time_id),
    FOREIGN KEY (date_id) REFERENCES EntryDates(date_id)
    );