Project history (May 2018): A plan for SQL database

The development team originally planned to deploy a standard SQL database (possibly PostgreSQL) for Luupeli.

Here you can see the database model and the preliminary create tables for what was planned during late May 2018. While none of this work was directly used in the project, the actually deployed MongoDB tables are actually quite closely based on the original SQL plan.

CREATE TABLE User

(

userid INT NOT NULL,

username VARCHAR(40) NOT NULL,

password VARCHAR(40) NOT NULL,

email VARCHAR(80) NOT NULL,

admin boolean NOT NULL,

creationtime DATE NOT NULL,

lastmodified DATE NOT NULL,

lastlogin DATE NOT NULL,

logincount INT NOT NULL,

gamecount INT NOT NULL,

coins INT NOT NULL,

PRIMARY KEY (userid)

);

CREATE TABLE SaveGame

(

userid INT NOT NULL,

creationtime DATE NOT NULL,

quizlength INT NOT NULL,

quizindex INT NOT NULL,

bonearray VARCHAR(1024) NOT NULL,

resultarray VARCHAR(1024) NOT NULL,

attempt INT NOT NULL,

attemptmax INT NOT NULL,

difficultylevel VARCHAR(64) NOT NULL,

includestructures boolean NOT NULL,

includedetails boolean NOT NULL,

quizphase VARCHAR(64) NOT NULL,

userid INT NOT NULL,

PRIMARY KEY (userid),

FOREIGN KEY (userid) REFERENCES User(userid)

);

CREATE TABLE Species

(

speciesid INT NOT NULL,

name VARCHAR(255) NOT NULL,

name_lat VARCHAR(255) NOT NULL,

speciesanatomyid INT NOT NULL,

PRIMARY KEY (speciesid),

FOREIGN KEY (speciesanatomyid) REFERENCES SpeciesAnatomy(speciesanatomyid)

);

CREATE TABLE Bone

(

boneid INT NOT NULL,

name VARCHAR(255) NOT NULL,

name_lat VARCHAR(255) NOT NULL,

difficulty VARCHAR(8) NOT NULL,

bonesize INT NOT NULL,

handedness VARCHAR(32) NOT NULL,

description VARCHAR(255) NOT NULL,

creationtime DATE NOT NULL,

lastmodified DATE NOT NULL,

bodypart VARCHAR(255) NOT NULL,

attemps INT NOT NULL,

correctattemps INT NOT NULL,

shape VARCHAR(32) NOT NULL,

bonestructureid INT NOT NULL,

speciesanatomyid INT NOT NULL,

boneimageid INT NOT NULL,

PRIMARY KEY (boneid),

FOREIGN KEY (bonestructureid) REFERENCES BoneStructure(bonestructureid),

FOREIGN KEY (speciesanatomyid) REFERENCES SpeciesAnatomy(speciesanatomyid),

FOREIGN KEY (boneimageid) REFERENCES BoneImage(boneimageid)

);

CREATE TABLE BoneStructure

(

bonestructureid INT NOT NULL,

name VARCHAR(255) NOT NULL,

name_lat VARCHAR(255) NOT NULL,

difficulty INT NOT NULL,

NewAttribute INT NOT NULL,

boneid INT NOT NULL,

PRIMARY KEY (bonestructureid),

FOREIGN KEY (boneid) REFERENCES Bone(boneid)

);

CREATE TABLE SpeciesAnatomy

(

speciesanatomyid INT NOT NULL,

boneid INT NOT NULL,

speciesid INT NOT NULL,

PRIMARY KEY (speciesanatomyid),

FOREIGN KEY (boneid) REFERENCES Bone(boneid),

FOREIGN KEY (speciesid) REFERENCES Species(speciesid)

);

CREATE TABLE BoneImage

(

boneimageid INT NOT NULL,

difficulty INT NOT NULL,

description VARCHAR(255) NOT NULL,

photographer VARCHAR(255) NOT NULL,

copyright VARCHAR(255) NOT NULL,

creationtime DATE NOT NULL,

path VARCHAR(255) NOT NULL,

imagefiletype VARCHAR(32) NOT NULL,

imagetype VARCHAR(32) NOT NULL,

boneid INT NOT NULL,

bonedetailid INT NOT NULL,

PRIMARY KEY (boneimageid),

FOREIGN KEY (boneid) REFERENCES Bone(boneid),

FOREIGN KEY (bonedetailid) REFERENCES BoneDetails(bonedetailid)

);

CREATE TABLE Result

(

resultid INT NOT NULL,

userid INT NOT NULL,

boneid INT NOT NULL,

memorizedtype boolean NOT NULL,

memorizedstructure boolean NOT NULL,

lastattempt DATE NOT NULL,

speciesid INT NOT NULL,

attempts INT NOT NULL,

memorizedpicklist boolean NOT NULL,

memorizationdate DATE NOT NULL,

memorizeddetails boolean NOT NULL,

boneid INT NOT NULL,

userid INT NOT NULL,

speciesid INT NOT NULL,

PRIMARY KEY (resultid),

FOREIGN KEY (boneid) REFERENCES Bone(boneid),

FOREIGN KEY (userid) REFERENCES User(userid),

FOREIGN KEY (speciesid) REFERENCES Species(speciesid)

);

CREATE TABLE BoneDetails

(

bonedetailid INT NOT NULL,

name INT NOT NULL,

name_lat INT NOT NULL,

description VARCHAR(255) NOT NULL,

difficulty INT NOT NULL,

boneid INT NOT NULL,

boneimageid INT NOT NULL,

PRIMARY KEY (bonedetailid),

FOREIGN KEY (boneid) REFERENCES Bone(boneid),

FOREIGN KEY (boneimageid) REFERENCES BoneImage(boneimageid)

);

CREATE TABLE Achievement

(

achievementid INT NOT NULL,

name VARCHAR(255) NOT NULL,

description VARCHAR(255) NOT NULL,

score INT NOT NULL,

secret boolean NOT NULL,

coinvalue INT NOT NULL,

speciesid INT NOT NULL,

PRIMARY KEY (achievementid),

FOREIGN KEY (speciesid) REFERENCES Species(speciesid)

);

CREATE TABLE UserAchievement

(

userachievementid INT NOT NULL,

creationtime DATE NOT NULL,

achievementid INT NOT NULL,

userid INT NOT NULL,

PRIMARY KEY (userachievementid),

FOREIGN KEY (achievementid) REFERENCES Achievement(achievementid),

FOREIGN KEY (userid) REFERENCES User(userid)

);