CREATE TABLE association(
id INT NOT NULL AUTO_INCREMENT,
PRIMARY KEY (id));


CREATE TABLE topic(
id INT NOT NULL AUTO_INCREMENT,
PRIMARY KEY (id),
INDEX IDX_topic_1 (id));


CREATE TABLE subjectIdentity(
id INT NOT NULL AUTO_INCREMENT,
id_topic INT NOT NULL,
value VARCHAR(255),
type CHAR(1),
FOREIGN KEY (id_topic) REFERENCES topic (id),
PRIMARY KEY (id));


CREATE TABLE subjectIdentityV(
id INT NOT NULL AUTO_INCREMENT,
id_subjectIdentity INT NOT NULL,
value VARCHAR(255),
type CHAR(1),
FOREIGN KEY (id_subjectIdentity) REFERENCES subjectIdentity (id),
PRIMARY KEY (id));


CREATE TABLE member(
id INT NOT NULL AUTO_INCREMENT,
id_association INT NOT NULL,
FOREIGN KEY (id_association) REFERENCES association (id),
PRIMARY KEY (id));


CREATE TABLE memberV(
id INT NOT NULL AUTO_INCREMENT,
id_member INT,
value VARCHAR(255),
type CHAR(1),
FOREIGN KEY (id_member) REFERENCES member (id),
PRIMARY KEY (id));


CREATE TABLE roleSpec(
id INT NOT NULL AUTO_INCREMENT,
id_member INT,
value VARCHAR(255),
type CHAR(1),
FOREIGN KEY (id_member) REFERENCES member (id),
PRIMARY KEY (id));


CREATE TABLE baseName(
id INT NOT NULL AUTO_INCREMENT,
id_topic INT NOT NULL,
value VARCHAR(255) NOT NULL,
type CHAR(1) NOT NULL,
FOREIGN KEY (id_topic) REFERENCES topic (id),
PRIMARY KEY (id),
INDEX IDX_baseName_1 (id),
INDEX IDX_baseName_2 (id_topic));


CREATE TABLE variant(
id INT NOT NULL AUTO_INCREMENT,
id_baseName INT NOT NULL,
FOREIGN KEY (id_baseName) REFERENCES baseName (id),
PRIMARY KEY (id),
INDEX IDX_variant_1 (id),
INDEX IDX_variant_2 (id_baseName));


CREATE TABLE variantName(
id INT NOT NULL AUTO_INCREMENT,
id_variant INT NOT NULL,
value VARCHAR(255),
type CHAR(1),
FOREIGN KEY (id_variant) REFERENCES variant (id),
PRIMARY KEY (id),
INDEX IDX_variantName_1 (id),
INDEX IDX_variantName_2 (id_variant));


CREATE TABLE occurrence(
id INT NOT NULL AUTO_INCREMENT,
id_topic INT NOT NULL,
value VARCHAR(255),
type CHAR(1),
FOREIGN KEY (id_topic) REFERENCES topic (id),
PRIMARY KEY (id));


CREATE TABLE instanceOf(
id INT NOT NULL AUTO_INCREMENT,
id_topic INT,
id_occurrence INT,
id_association INT,
value VARCHAR(255) NOT NULL,
type CHAR(1) NOT NULL,
FOREIGN KEY (id_association) REFERENCES association (id),
FOREIGN KEY (id_occurrence) REFERENCES occurrence (id),
FOREIGN KEY (id_topic) REFERENCES topic (id),
PRIMARY KEY (id));


CREATE TABLE variantRecursive(
id INT NOT NULL AUTO_INCREMENT,
id_variant1 INT NOT NULL,
id_variant2 INT NOT NULL,
FOREIGN KEY (id_variant1) REFERENCES variant (id),
FOREIGN KEY (id_variant2) REFERENCES variant (id),
PRIMARY KEY (id));


CREATE TABLE parameter(
id INT NOT NULL AUTO_INCREMENT,
id_variant INT NOT NULL,
value VARCHAR(255),
type CHAR(1),
FOREIGN KEY (id_variant) REFERENCES variant (id),
PRIMARY KEY (id),
INDEX IDX_parameter_1 (id),
INDEX IDX_parameter_2 (id_variant));


CREATE TABLE scope(
id INT NOT NULL AUTO_INCREMENT,
id_baseName INT,
id_occurrence INT,
id_association INT,
value VARCHAR(255),
type CHAR(1),
FOREIGN KEY (id_association) REFERENCES association (id),
FOREIGN KEY (id_occurrence) REFERENCES occurrence (id),
FOREIGN KEY (id_baseName) REFERENCES baseName (id),
PRIMARY KEY (id));



CREATE TABLE mergeMap(
id INT NOT NULL AUTO_INCREMENT,
value VARCHAR(255),
PRIMARY KEY (id));


CREATE TABLE mergeMapV(
id INT NOT NULL AUTO_INCREMENT,
id_mergeMap INT NOT NULL,
value VARCHAR(255),
type CHAR(1),
FOREIGN KEY (id_mergeMap) REFERENCES mergeMap (id),
PRIMARY KEY (id));

CREATE TABLE history(
id INT NOT NULL AUTO_INCREMENT,
id_topic INT,
id_association INT,
id_mergeMap INT,
owner VARCHAR(255),
ipaddress VARCHAR(255),
object TEXT,
datetime DATETIME,
PRIMARY KEY (id));