AIO Card System v0.3 7テーブル物理設計 正本

AIO Card System v0.3 7テーブル物理設計 正本

状態：この内容でSQLに制作済み。

本ページは、AIO Card System v0.3 の7テーブル物理設計に関する正本である。

CREATE TABLE aio_invites (
    id BIGINT UNSIGNED NOT NULL AUTO_INCREMENT,
    invite_id VARCHAR(64) NOT NULL,
    status VARCHAR(32) NOT NULL DEFAULT 'active',
    is_enabled TINYINT UNSIGNED NOT NULL DEFAULT 1,
    expires_at DATETIME NULL DEFAULT NULL,
    created_at DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP,
    updated_at DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP,

    PRIMARY KEY (id),
    UNIQUE KEY (invite_id)
) ENGINE=InnoDB
  DEFAULT CHARSET=utf8mb4;


CREATE TABLE aio_intakes (
    id BIGINT UNSIGNED NOT NULL AUTO_INCREMENT,
    invite_db_id BIGINT UNSIGNED NOT NULL,
    input_method VARCHAR(32) NOT NULL DEFAULT 'self',
    status VARCHAR(32) NOT NULL DEFAULT 'not_started',
    started_at DATETIME NULL DEFAULT NULL,
    last_saved_at DATETIME NULL DEFAULT NULL,
    submitted_at DATETIME NULL DEFAULT NULL,
    created_at DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP,
    updated_at DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP,

    PRIMARY KEY (id),
    UNIQUE KEY (invite_db_id),

    FOREIGN KEY (invite_db_id)
        REFERENCES aio_invites (id)
        ON UPDATE CASCADE
        ON DELETE RESTRICT
) ENGINE=InnoDB
  DEFAULT CHARSET=utf8mb4;


CREATE TABLE aio_intake_values (
    id BIGINT UNSIGNED NOT NULL AUTO_INCREMENT,
    intake_id BIGINT UNSIGNED NOT NULL,
    slot_group VARCHAR(32) NOT NULL,
    slot_key VARCHAR(100) NOT NULL,
    value LONGTEXT NOT NULL,
    value_type VARCHAR(32) NOT NULL DEFAULT 'text',
    created_at DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP,
    updated_at DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP,

    PRIMARY KEY (id),
    UNIQUE KEY (intake_id, slot_group, slot_key),

    FOREIGN KEY (intake_id)
        REFERENCES aio_intakes (id)
        ON UPDATE CASCADE
        ON DELETE CASCADE
) ENGINE=InnoDB
  DEFAULT CHARSET=utf8mb4;


CREATE TABLE aio_intake_links (
    id BIGINT UNSIGNED NOT NULL AUTO_INCREMENT,
    intake_id BIGINT UNSIGNED NOT NULL,
    link_key VARCHAR(100) NOT NULL,
    link_label VARCHAR(255) NULL DEFAULT NULL,
    url TEXT NOT NULL,
    link_role VARCHAR(100) NULL DEFAULT NULL,
    related_group VARCHAR(32) NULL DEFAULT NULL,
    related_key VARCHAR(100) NULL DEFAULT NULL,
    source_note TEXT NULL DEFAULT NULL,
    sort_order INT UNSIGNED NOT NULL DEFAULT 0,
    created_at DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP,
    updated_at DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP,

    PRIMARY KEY (id),

    FOREIGN KEY (intake_id)
        REFERENCES aio_intakes (id)
        ON UPDATE CASCADE
        ON DELETE CASCADE
) ENGINE=InnoDB
  DEFAULT CHARSET=utf8mb4;


CREATE TABLE aio_cards (
    id BIGINT UNSIGNED NOT NULL AUTO_INCREMENT,
    card_id VARCHAR(64) NOT NULL,
    read_id VARCHAR(64) NOT NULL,
    source_intake_id BIGINT UNSIGNED NULL DEFAULT NULL,
    status VARCHAR(32) NOT NULL DEFAULT 'draft',
    reading_spec_version VARCHAR(32) NOT NULL DEFAULT 'v0.3',
    published_at DATETIME NULL DEFAULT NULL,
    suspended_at DATETIME NULL DEFAULT NULL,
    created_at DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP,
    updated_at DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP,

    PRIMARY KEY (id),
    UNIQUE KEY (card_id),
    UNIQUE KEY (read_id),
    UNIQUE KEY (source_intake_id),

    FOREIGN KEY (source_intake_id)
        REFERENCES aio_intakes (id)
        ON UPDATE CASCADE
        ON DELETE SET NULL
) ENGINE=InnoDB
  DEFAULT CHARSET=utf8mb4;


CREATE TABLE aio_card_values (
    id BIGINT UNSIGNED NOT NULL AUTO_INCREMENT,
    card_id BIGINT UNSIGNED NOT NULL,
    slot_group VARCHAR(32) NOT NULL,
    slot_key VARCHAR(100) NOT NULL,
    value LONGTEXT NOT NULL,
    value_type VARCHAR(32) NOT NULL DEFAULT 'text',
    source_note TEXT NULL DEFAULT NULL,
    is_sensitive TINYINT UNSIGNED NOT NULL DEFAULT 0,
    sort_order INT UNSIGNED NOT NULL DEFAULT 0,
    created_at DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP,
    updated_at DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP,

    PRIMARY KEY (id),
    UNIQUE KEY (card_id, slot_group, slot_key),

    FOREIGN KEY (card_id)
        REFERENCES aio_cards (id)
        ON UPDATE CASCADE
        ON DELETE CASCADE
) ENGINE=InnoDB
  DEFAULT CHARSET=utf8mb4;


CREATE TABLE aio_card_links (
    id BIGINT UNSIGNED NOT NULL AUTO_INCREMENT,
    card_id BIGINT UNSIGNED NOT NULL,
    link_key VARCHAR(100) NOT NULL,
    link_label VARCHAR(255) NULL DEFAULT NULL,
    url TEXT NOT NULL,
    link_role VARCHAR(100) NULL DEFAULT NULL,
    related_group VARCHAR(32) NULL DEFAULT NULL,
    related_key VARCHAR(100) NULL DEFAULT NULL,
    source_note TEXT NULL DEFAULT NULL,
    sort_order INT UNSIGNED NOT NULL DEFAULT 0,
    created_at DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP,
    updated_at DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP,

    PRIMARY KEY (id),

    FOREIGN KEY (card_id)
        REFERENCES aio_cards (id)
        ON UPDATE CASCADE
        ON DELETE CASCADE
) ENGINE=InnoDB
  DEFAULT CHARSET=utf8mb4;

楽譜トップへ戻る