CREATE TABLE  Category (
       categoryId INT NOT NULL
     , name VARCHAR(256) NOT NULL
     , PRIMARY KEY (categoryId)
)TYPE=InnoDB;

CREATE TABLE  Template (
       templateId INT NOT NULL
     , name VARCHAR(256) NOT NULL
     , PRIMARY KEY (templateId)
)TYPE=InnoDB;

CREATE TABLE  AttributeType (
       attributeTypeId INT NOT NULL
     , Type VARCHAR(512) NOT NULL
     , PRIMARY KEY (attributeTypeId)
)TYPE=InnoDB;

CREATE TABLE  Page (
       pageId INT NOT NULL AUTO_INCREMENT
     , Title VARCHAR(1024) NOT NULL
     , categoryId INT NOT NULL
     , templateId INT NOT NULL
     , filename VARCHAR(1024) NOT NULL
     , createdDate DATETIME NOT NULL
     , modifiedDate TIMESTAMP
     , UNIQUE UQ_Page_1 (filename)
     , PRIMARY KEY (pageId)
     , INDEX (categoryId)
     , CONSTRAINT FK_Page_1 FOREIGN KEY (categoryId)
                  REFERENCES  Category (categoryId)
     , INDEX (templateId)
     , CONSTRAINT FK_Page_2 FOREIGN KEY (templateId)
                  REFERENCES  Template (templateId)
)TYPE=InnoDB;
CREATE INDEX IX_Page_1 ON  Page (filename ASC);

CREATE TABLE  Item (
       itemId INT NOT NULL AUTO_INCREMENT
     , pageId INT NOT NULL
     , containerId INT NOT NULL
     , contentTypeId INT NOT NULL
     , createdDate DATETIME NOT NULL
     , modifiedDate TIMESTAMP
     , UNIQUE UQ_Item_1 (pageId, containerId)
     , PRIMARY KEY (itemId)
     , INDEX (pageId)
     , CONSTRAINT FK_Item_1 FOREIGN KEY (pageId)
                  REFERENCES  Page (pageId)
)TYPE=InnoDB;

CREATE TABLE  ItemAttributes (
       attributeId INT NOT NULL AUTO_INCREMENT
     , itemId INT NOT NULL
     , attributeTypeId INT NOT NULL
     , attributeTextValue TEXT
     , attributeNumValue BIGINT
     , PRIMARY KEY (attributeId)
     , INDEX (itemId)
     , CONSTRAINT FK_ItemAttributes_1 FOREIGN KEY (itemId)
                  REFERENCES  Item (itemId)
     , INDEX (attributeTypeId)
     , CONSTRAINT FK_ItemAttributes_2 FOREIGN KEY (attributeTypeId)
                  REFERENCES  AttributeType (attributeTypeId)
)TYPE=InnoDB;