Skip to content
Deep Thought

WarGame Data Model

This is a simple data model for storing data that could be used in a game like War Thunder.

-- SQL Script for creating the database and seeding test data


-- Create database
CREATE DATABASE IF NOT EXISTS wargame;
USE wargame;


-- Drop tables if they exist (for reruns)
DROP TABLE IF EXISTS tank_weapon, tank_stat, tank, weapon, tank_type, faction, country;


-- COUNTRY TABLE
CREATE TABLE country (
    id INT AUTO_INCREMENT PRIMARY KEY,
    name VARCHAR(100) NOT NULL,
    continent VARCHAR(50)
);


-- FACTION TABLE
CREATE TABLE faction (
    id INT AUTO_INCREMENT PRIMARY KEY,
    name VARCHAR(100) NOT NULL,
    ideology VARCHAR(100),
    country_id INT,
    FOREIGN KEY (country_id) REFERENCES country(id)
);


-- TANK_TYPE TABLE
CREATE TABLE tank_type (
    id INT AUTO_INCREMENT PRIMARY KEY,
    name VARCHAR(50) NOT NULL,
    description VARCHAR(255)
);


-- TANK TABLE
CREATE TABLE tank (
    id INT AUTO_INCREMENT PRIMARY KEY,
    name VARCHAR(100) NOT NULL,
    year_introduced INT,
    country_id INT,
    faction_id INT,
    tank_type_id INT,
    FOREIGN KEY (country_id) REFERENCES country(id),
    FOREIGN KEY (faction_id) REFERENCES faction(id),
    FOREIGN KEY (tank_type_id) REFERENCES tank_type(id)
);


-- TANK_STAT TABLE
CREATE TABLE tank_stat (
    id INT AUTO_INCREMENT PRIMARY KEY,
    tank_id INT,
    armor_thickness FLOAT,
    top_speed FLOAT,
    weight FLOAT,
    engine_power FLOAT,
    FOREIGN KEY (tank_id) REFERENCES tank(id)
);


-- WEAPON TABLE
CREATE TABLE weapon (
    id INT AUTO_INCREMENT PRIMARY KEY,
    name VARCHAR(100) NOT NULL,
    type VARCHAR(50),
    caliber_mm FLOAT,
    rate_of_fire FLOAT
);


-- TANK_WEAPON TABLE
CREATE TABLE tank_weapon (
    id INT AUTO_INCREMENT PRIMARY KEY,
    tank_id INT,
    weapon_id INT,
    mount_position VARCHAR(50),
    ammo_count INT,
    FOREIGN KEY (tank_id) REFERENCES tank(id),
    FOREIGN KEY (weapon_id) REFERENCES weapon(id)
);


-- ---------------------------------------------------
-- INSERT DATA
-- ---------------------------------------------------


-- COUNTRIES
INSERT INTO country (name, continent) VALUES
('Germany', 'Europe'),
('USA', 'North America'),
('USSR', 'Europe/Asia'),
('UK', 'Europe'),
('France', 'Europe'),
('Japan', 'Asia');


-- FACTIONS
INSERT INTO faction (name, ideology, country_id) VALUES
('Wehrmacht', 'Axis', 1),
('US Army', 'Allies', 2),
('Red Army', 'Communist', 3),
('British Army', 'Allies', 4),
('French Army', 'Allies', 5),
('Imperial Army', 'Axis', 6);


-- TANK TYPES
INSERT INTO tank_type (name, description) VALUES
('Light Tank', 'Fast and lightly armored tank used for reconnaissance'),
('Medium Tank', 'Balanced tank combining firepower, armor, and speed'),
('Heavy Tank', 'Heavily armored tank with strong firepower'),
('Tank Destroyer', 'Designed for long-range anti-tank engagements');


-- WEAPONS
INSERT INTO weapon (name, type, caliber_mm, rate_of_fire) VALUES
('75 mm KwK 42 L/70', 'Cannon', 75, 10),
('88 mm KwK 36 L/56', 'Cannon', 88, 6),
('37 mm M5', 'Cannon', 37, 20),
('75 mm M3', 'Cannon', 75, 12),
('76 mm ZiS-3', 'Cannon', 76, 15),
('122 mm D-25T', 'Cannon', 122, 4),
('40 mm 2pdr', 'Cannon', 40, 20),
('17-pounder', 'Cannon', 76.2, 10),
('47 mm SA 35', 'Cannon', 47, 15),
('Type 97 57mm', 'Cannon', 57, 10),
('MG34', 'Machine Gun', 7.92, 600),
('M1919 Browning', 'Machine Gun', 7.62, 500),
('DT MG', 'Machine Gun', 7.62, 550),
('BESA MG', 'Machine Gun', 7.92, 600);


-- TANKS
INSERT INTO tank (name, year_introduced, country_id, faction_id, tank_type_id) VALUES
('Panzer II', 1936, 1, 1, 1),
('Panzer III', 1939, 1, 1, 2),
('Panzer IV', 1940, 1, 1, 2),
('Tiger I', 1942, 1, 1, 3),
('Tiger II', 1944, 1, 1, 3),
('M3 Stuart', 1941, 2, 2, 1),
('M4 Sherman', 1942, 2, 2, 2),
('M26 Pershing', 1945, 2, 2, 3),
('T-26', 1933, 3, 3, 1),
('T-34', 1940, 3, 3, 2),
('KV-1', 1940, 3, 3, 3),
('IS-2', 1943, 3, 3, 3),
('Crusader', 1940, 4, 4, 1),
('Cromwell', 1943, 4, 4, 2),
('Churchill', 1941, 4, 4, 3),
('Char B1', 1935, 5, 5, 3),
('Somua S35', 1936, 5, 5, 2),
('Type 95 Ha-Go', 1935, 6, 6, 1),
('Chi-Ha', 1938, 6, 6, 2),
('Chi-Nu', 1944, 6, 6, 2);


-- TANK STATS
INSERT INTO tank_stat (tank_id, armor_thickness, top_speed, weight, engine_power) VALUES
(1, 14, 40, 9.5, 140),
(2, 30, 40, 23, 300),
(3, 50, 38, 25, 300),
(4, 100, 45, 57, 700),
(5, 180, 41, 70, 700),
(6, 25, 58, 12, 250),
(7, 51, 48, 30, 400),
(8, 102, 40, 41, 500),
(9, 15, 30, 9, 90),
(10, 45, 53, 26.5, 500),
(11, 90, 35, 47, 600),
(12, 120, 37, 46, 600),
(13, 30, 42, 20, 340),
(14, 76, 64, 27, 600),
(15, 152, 25, 40, 350),
(16, 60, 28, 32, 300),
(17, 47, 40, 20, 250),
(18, 12, 45, 7.4, 120),
(19, 25, 38, 15, 170),
(20, 50, 39, 20, 240);


-- TANK_WEAPON (main + MGs)
INSERT INTO tank_weapon (tank_id, weapon_id, mount_position, ammo_count) VALUES
(1, 3, 'Main Gun', 180),
(1, 11, 'Coaxial MG', 2000),
(2, 3, 'Main Gun', 150),
(2, 11, 'Coaxial MG', 2000),
(3, 1, 'Main Gun', 90),
(3, 11, 'Coaxial MG', 2000),
(4, 2, 'Main Gun', 92),
(4, 11, 'Coaxial MG', 3800),
(5, 2, 'Main Gun', 86),
(5, 11, 'Coaxial MG', 4800),
(6, 3, 'Main Gun', 200),
(6, 12, 'Coaxial MG', 2500),
(7, 4, 'Main Gun', 90),
(7, 12, 'Coaxial MG', 3000),
(8, 4, 'Main Gun', 70),
(8, 12, 'Coaxial MG', 2800),
(9, 5, 'Main Gun', 200),
(9, 13, 'Coaxial MG', 2700),
(10, 5, 'Main Gun', 100),
(10, 13, 'Coaxial MG', 2800),
(11, 5, 'Main Gun', 90),
(11, 13, 'Coaxial MG', 3200),
(12, 6, 'Main Gun', 28),
(12, 13, 'Coaxial MG', 2800),
(13, 7, 'Main Gun', 110),
(13, 14, 'Coaxial MG', 2000),
(14, 8, 'Main Gun', 65),
(14, 14, 'Coaxial MG', 2200),
(15, 8, 'Main Gun', 150),
(15, 14, 'Coaxial MG', 3000),
(16, 9, 'Main Gun', 80),
(16, 14, 'Coaxial MG', 2000),
(17, 9, 'Main Gun', 118),
(17, 14, 'Coaxial MG', 1900),
(18, 10, 'Main Gun', 120),
(18, 14, 'Coaxial MG', 2000),
(19, 10, 'Main Gun', 100),
(19, 14, 'Coaxial MG', 1800),
(20, 10, 'Main Gun', 90),
(20, 14, 'Coaxial MG', 1900);