This is a simple data model for storing data that could be used in a game like War Thunder.
erDiagram COUNTRY ||--o{ FACTION : "has" COUNTRY ||--o{ TANK : "produces" FACTION ||--o{ TANK : "uses" TANK ||--o{ TANK_TYPE : "has type" TANK ||--o{ TANK_STAT : "has stats" TANK ||--o{ TANK_WEAPON : "has" WEAPON ||--o{ TANK_WEAPON : "used in" COUNTRY { int id PK string name string continent } FACTION { int id PK string name string ideology int country_id FK } TANK { int id PK string name int year_introduced int country_id FK int faction_id FK int tank_type_id FK } TANK_TYPE { int id PK string name string description } TANK_STAT { int id PK int tank_id FK float armor_thickness float top_speed float weight float engine_power } WEAPON { int id PK string name string type float caliber_mm float rate_of_fire } TANK_WEAPON { int id PK int tank_id FK int weapon_id FK string mount_position int ammo_count }
-- 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);