ej log rd

CREATE TABLE Kerbal (
  Id INT NOT NULL AUTO_INCREMENT,   -- every table needs an id field.  auto increment means it increases by one on every insert, so each row is unique
  Name VARCHAR(50),
  Gender VARCHAR(10),
  Career VARCHAR(20),
  Status VARCHAR(20),
  Stupidity DECIMAL(2,3),
  Bravery DECIMAL(2,3),
  Badass TINYINT(1), -- i know KSP uses it, but not sure we need this
  DefaultRoleId INT,
  IsActive TINYINT(1) DEFAULT 1,                   -- these next 3 fields are what we call in the industry "audit" fields and will be on all tables
  CreatedOn DATETIME DEFAULT CURRENT_TIMESTAMP,
  CreatedBy VARCHAR(30),
  PRIMARY KEY (Id)
)

-- Having a roles table lets you manage roles and it lets Kerbals have different roles for different missions
CREATE TABLE KerbalRole (
  Id INT NOT NULL AUTO_INCREMENT,
  Role VARCHAR(75),  -- Pilot, Commander, Payload specialist, whatever you want
  IsActive TINYINT(1) DEFAULT 1,
  CreatedOn DATETIME DEFAULT CURRENT_TIMESTAMP,
  CreatedBy VARCHAR(30),
  PRIMARY KEY (Id)
)

-- Entries are added for every mission.
CREATE TABLE KerbalMission (
  Id INT NOT NULL AUTO_INCREMENT,
  KerbalId INT, -- points to kerbal
  MissionId INT, -- points to mission
  RoleId INT, -- points to whatever role that kerbal had for that mission
  IsActive TINYINT(1) DEFAULT 1,
  CreatedOn DATETIME DEFAULT CURRENT_TIMESTAMP,
  CreatedBy VARCHAR(30),
  PRIMARY KEY (Id)
)

-- add to this table every time a new vehicle or a revision is made
CREATE TABLE Vehicle (
  Id INT NOT NULL AUTO_INCREMENT,
  Name VARCHAR(50),
  Version VARCHAR(10),
  DryMass DECIMAL(6,3),
  WetMass DECIMAL(6,3),
  PayloadCapacity DECIMAL(6,3),
  BaseCost DECIMAL(12,3),
  IsActive TINYINT(1) DEFAULT 1,
  CreatedOn DATETIME DEFAULT CURRENT_TIMESTAMP,
  CreatedBy VARCHAR(30),
  PRIMARY KEY (Id)
)

-- A header table that is used to group Kerbals, Lifters/Vehicles, Launches
CREATE TABLE Mission (
  Id INT NOT NULL AUTO_INCREMENT,
  MissionCategoryId INT, -- points to MissionCategory
  FacilityId INT, -- points to Facility
  VehicleId INT, -- points to Vehicle
  Name VARCHAR(100),
  METStart VARCHAR(50), -- tentative
  METEnd VARCHAR(50), -- tentative
  PayloadMass DECIMAL(6,3),
  MissionOutcome VARCHAR(100),
  GameTime DATETIME,
  StreamTime DATETIME,
  Notes VARCHAR(5000),
  IsActive TINYINT(1) DEFAULT 1,
  CreatedOn DATETIME DEFAULT CURRENT_TIMESTAMP,
  CreatedBy VARCHAR(30),
  PRIMARY KEY (Id)
)

-- A small list of categories.  Will be useful for users wanting to see all "rescue missions" or all "crew transfers"
CREATE TABLE MissionCategory (
  Id INT NOT NULL AUTO_INCREMENT,
  Category VARCHAR(100), -- i.e. Rescue Mission, Resource Reconnaissance, Station Assembly,
  IsActive TINYINT(1) DEFAULT 1,
  CreatedOn DATETIME DEFAULT CURRENT_TIMESTAMP,
  CreatedBy VARCHAR(30),
  PRIMARY KEY (Id)
)

-- Will hold a growing list of facilities.  I.E: Crippen Station, Mun Base, Minmus Refinery, etc...
CREATE TABLE Facility (
  Id INT NOT NULL AUTO_INCREMENT,
  Name VARCHAR(100),
  PartCount INT, -- should be updated after every mission, or at least once complete
  Location VARCHAR(50),  -- either orbital params or physical location
  CompletionDate DATETIME, -- null if not complete.  Also, we'd know the start of the construction based on the gametime of the first mission to work on assembly
  IsActive TINYINT(1) DEFAULT 1,
  CreatedOn DATETIME DEFAULT CURRENT_TIMESTAMP,
  CreatedBy VARCHAR(30),
  PRIMARY KEY (Id)
)

-- holds Launch info for the mission.  Some missions may not have a launch and won't need an entry here (i.e. rover exploratory mission)
CREATE TABLE Launch (
  Id INT NOT NULL AUTO_INCREMENT,
  MissionId INT NOT NULL, -- points to the mission
  Apoapsis DECIMAL(9,3), -- in KM
  Periapsis DECIMAL(9,3), -- in KM
  Inclination DECIMAL(6,2), -- in degrees
  Parameters VARCHAR(50), -- if user wants to provide a string instead of orbial params, i.e: Direct Duna Transfer
  IsActive TINYINT(1) DEFAULT 1,
  CreatedOn DATETIME DEFAULT CURRENT_TIMESTAMP,
  CreatedBy VARCHAR(30),
  PRIMARY KEY (Id)
)



-- All that is pretty extensive, and right out the gate we probably won't utilize most of the functionality that
-- this highly normalized table structure provides.  But setting it up like this now allows us to do some pretty
-- cool future features to let the users slice and dice the data.

-- it's late and the syntax probably won't be akin to MySQL, but here are some examples

-- Want to know stats and mission count of all the Kerbals?
SELECT
k1.Name,
k1.Gender,
k1.Career,
k1.Status,
k1.Stupidity,
k1.Bravery,
K2.TotalMissionCount
FROM Kerbals K1
LEFT OUTER JOIN (SELECT COUNT(*) AS TotalMissionCount, KerbalId
                FROM KerbalMisson GROUP BY KerbalId
                WHERE IsActive = 1) K2 ON K2.KerbalId = K1.Id



-- Want to know all Kerbals involved in Crippen Station Construction and how many missions they were on?
SELECT K.Name, COUNT(*) AS MissionCount,
FROM Mission
JOIN KerbalMission KM ON KM.MissionId = M.Id
JOIN Kerbal K ON K.Id = KM.KerbalId
WHERE MissionCategoryId = 5 -- assuming 5 = Construction
AND FacilityId = 1 -- assuming 1 = Crippen station
AND IsActive = 1


-- Want to know every Mission Voyager has been on?
SELECT M.Name, MC.Category, F.Name, M.GameTime
FROM Mission M,
LEFT OUTER JOIN MissionCategory MC ON MC.Id = M.MissionCategoryId
LEFT OUTER JOIN Facilty F ON F.Id = M.FacilityId
WHERE M.