CS340SMITEProject/CREATETABLES.sql at master · mbaiza27/CS340SMITEProject · GitHub

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
51
52
53
54
55
56
57
58
59
60
61
CREATE TABLE Items (
    itemID int(11) AUTO_INCREMENT,
    itemName varchar(255) NOT NULL,
    effect text,
    PRIMARY KEY (itemID),
    UNIQUE (itemName)
) ENGINE=InnoDB;

CREATE TABLE Roles (
    roleID int(11) AUTO_INCREMENT,
    roleName varchar(255) NOT NULL,
    description text,
    PRIMARY KEY (roleID),
    UNIQUE (roleName)
) ENGINE=InnoDB;

CREATE TABLE Classes (
    classID int(11) AUTO_INCREMENT,
    className varchar(255) NOT NULL,
    description text,
    PRIMARY KEY (classID),
    UNIQUE (className)
) ENGINE=InnoDB;

CREATE TABLE Types (
    typeID int(11) AUTO_INCREMENT,
    typeName varchar(255) NOT NULL,
    PRIMARY KEY (typeID),
    UNIQUE (typeName)
) ENGINE=InnoDB;

CREATE TABLE ItemTypes (
    itemTypeID int(11) AUTO_INCREMENT,
    itemID int(11),
    typeID int(11),
    PRIMARY KEY (itemTypeID),
    FOREIGN KEY (itemID) REFERENCES Items(itemID),
    FOREIGN KEY (typeID) REFERENCES Types(typeID)
) ENGINE=InnoDB;

CREATE TABLE Characters (
    characterID int(11) AUTO_INCREMENT,
    characterName varchar(255) NOT NULL,
    primaryRoleID int(11),
    primaryClassID int (11),
    primaryTypeID int(11),
    PRIMARY KEY (characterID),
    UNIQUE (characterName),
    FOREIGN KEY (primaryRoleID) REFERENCES Roles(roleID),
    FOREIGN KEY (primaryClassID) REFERENCES Classes(classID),
    FOREIGN KEY (primaryTypeID) REFERENCES Types(typeID)
) ENGINE=InnoDB;

CREATE TABLE Builds (
    buildID int(11) AUTO_INCREMENT,
    characterID int(11),
    itemID int(11),
    PRIMARY KEY (buildID),
    FOREIGN KEY (characterID) REFERENCES Characters(characterID),
    FOREIGN KEY (itemID) REFERENCES Items(itemID)
) ENGINE=InnoDB;