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

SET names utf8mb4;

CREATE database Macroniser;

USE Macroniser;


/*
Unique constraint on marked_word field needs to be specified specially to deal with utf8
e.g. to distinguish between āno and anō, rather than treating them both the same breaking uniqueness.
https://stackoverflow.com/questions/25318479/unique-constraint-violation-with-utf-8-values
*/
CREATE TABLE IF NOT EXISTS MarkedWords (
    word_id INT AUTO_INCREMENT,
    marked_word varchar(255) CHARACTER SET utf8mb4 COLLATE utf8mb4_bin 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)
    );