Tables Post 1: Some Notes, Creating the DB, Cities and Nations Tables

There are a lot of tables that can be dumped from OOTP, and at this point I don’t need  nearly all of them.    With the exception of the ‘nations’ and ‘cities’ tables, they will all be focused on teams, leagues, and players.

A few important notes that I want to put up front so they don’t get lost in the code:

  • I am not using Foreign Keys (FK’s) in this database. There are two reasons for this.  First, probably due to ignorance, I was having a lot of trouble getting them set up.  There are only so many hours a person can research solutions to Error 1215 before he just throws up his hands and quits.  Second, as long as we’re properly indexed, FK’s shouldn’t have much impact on query performance.  As long as I can trust the data OOTP dumps to have referential integrity (and I do), then we should be OK.
  • I’ve added AUTO_INCREMENT ID’s to player_career and league_history tables.  Without them, the Primary Keys for these tables would be ridiculously composite, and still have PK violations when data is imported.  I will note these in the code.

 

Create the database. Easy enough.  This bit wipes the database and starts fresh:

DROP DATABASE IF EXISTS TEST1;
CREATE DATABASE TEST1;
USE TEST1;

 

Create nations table

I don’t have any plans to use this table right now, I can maybe see a potential use case when using a larger universe.

CREATE TABLE `nations` (
  `nation_id` int(11) NOT NULL,
  `name` varchar(50) DEFAULT NULL,
  `short_name` varchar(50) DEFAULT NULL,
  `abbreviation` varchar(50) DEFAULT NULL,
  `demonym` varchar(50) DEFAULT NULL,
  `population` int(11) DEFAULT NULL,
  `gender` int(11) DEFAULT NULL,
  `baseball_quality` int(11) DEFAULT NULL,
  `continent_id` int(11) DEFAULT NULL,
  `main_language_id` int(11) DEFAULT NULL,
  `quality_total` int(11) DEFAULT NULL,
  `capital_id` int(11) DEFAULT NULL,
  `use_hardcoded_ml_player_origins` tinyint(4) DEFAULT NULL,
  `this_is_the_usa` tinyint(4) DEFAULT NULL,
  PRIMARY KEY (`nation_id`)
) ENGINE=InnoDB DEFAULT CHARSET=latin1;

 

Create cities table

Similar to above, though more potential use cases.  Particularly thinking about the owner goals in game where you are asked to sign a “home town player.”

CREATE TABLE `cities` (
  `city_id` int(11) NOT NULL,
  `nation_id` int(11) DEFAULT NULL,
  `state_id` int(11) DEFAULT NULL,
  `name` varchar(80) DEFAULT NULL,
  `abbreviation` varchar(10) DEFAULT NULL,
  `latitude` double DEFAULT NULL,
  `longitude` double DEFAULT NULL,
  `population` int(11) DEFAULT NULL,
  `main_language_id` int(11) DEFAULT NULL,
  PRIMARY KEY (`city_id`)
) ENGINE=InnoDB DEFAULT CHARSET=latin1;

Leave a Reply

Your email address will not be published. Required fields are marked *