About The Database?

That is a very good question. I know that I turn to "my database" for a lot of information, like the recent questions about Venezuelan players.

Basically, "The Database" is a set of tables I've been working with to power my Player Meikan since 1995. Back then the database was a set of tables in MS Access 2.0. In 1998 I started moving away from Microsoft and toward using FreeBSD full time, so it got ported to MySQL. But the design really didn't change much. In fact, the overall database schema is very much as it was in 1995, with a few additional columns.

You (or anybody) can download my entire database from here as individual tables, but how would you use it? That's a very good question. It would depend a great deal on how good you are with relational databases and SQL.

For example, say you want to know what the highest salary was for 2004. One would query the Salaries table like this:

mysql> select max(Salary) from Salaries where Year=2004;
+-------------+
| max(Salary) |
+-------------+
|       72000 |
+-------------+
1 row in set (0.02 sec)

The "mysql>" is the mysql command line prompt. The remainder of the line is the query. In this case, I selected the maximum value for the "Salary" field in the "Salaries" table where the "Year" field is 2004. The table below the query is the result of that query.

The Salaries table is composed of three fields in all, the Year, PlayerID, and Salary fields. It's the PlayerID field that is needed to get "who" has that maximum salary. But just finding one player isn't interesting, so let's look for all players with a salary greater than or equal to 5-oku yen in 2004.

mysql> select FirstName, LastName, Salary
    -> from Salaries left join Players using (PlayerID)
    -> where Salary >= 50000 and Year=2004
    -> order by Salary desc;
+-----------+----------+--------+
| FirstName | LastName | Salary |
+-----------+----------+--------+
| Roberto   | Petagine |  72000 |
| Tuffy     | Rhodes   |  55000 |
| Norihiro  | Nakamura |  50000 |
| Kazuhiro  | Sasaki   |  50000 |
+-----------+----------+--------+
4 rows in set (0.01 sec)

This table joins two tables together, the Salaries table which is where the conditions are being set, and the Players table to get the player's name. More often then not, if there is a PlayerID in a table, this is what one will want to do to make the result something readable to a human.

People don't tend to want to know that the PlayerIDs are 1154, 905, 55, and 393 respectively. But having those PlayerIDs is the key to drilling down to more information, and is exactly how the Teams and Players sections of this site work. For example, having Rhodes' PlayerID, I can write a link to his player page like this:

http://www.japanesebaseball.com/players/player.jsp?PlayerID=905

If you look at the URLs under the Teams and Players sections, you'll see exactly what keys are being passed around. Change them a bit by hand and you'll see different results - like appending "&Year=2000" (without the quotes) to Rhodes' page will show him as playing for the "OKB" (Osaka Kintetsu Buffaloes).

This database suffers from essentially three major problems:

1. Incompleteness - Even though I've got all of players who have played at ichi-gun entered for batting and pitching data since 2002, some data has not been easy to gather. Namely, salary information that was not in the annual player guides at time of printing. For that reason, I can't say for sure that the above four players were the only 5-oku yen players last season.


2. Poor Design - As I mentioned, the design hasn't changed much since I started the database in 1995. I was just starting to learn about relational databases back then and didn't yet know about "best practices" such as having a single, unrelated (to data), unique key for every record. I'm working to rectify its shortcomings by integrating it into the new Baseball-Databank database which I hope to have complete this year.


3. Lack of Review - I catch a number of errors every year when I enter the new players, updating teams and salaries. Some people have pointed out errors for birthplaces of family/friends in the past. And I did get a number of corrections from one of those "game disk" makers about problems with my data import of the season totals this past year. But for the most part, there is nobody who is really checking that I've entered the data properly (and it's hard proofing your own work). I hope to have my Player Meikan fully bilingual soon so that more Japanese natives will review it, as they'd be in a better position to catch errors, having native access to primary sources.

Did this answer the question, or did I get too technical? My "day job" is web enabling databases (mainframe databases, relational, and native XML databases), and I enjoy it so much that maintaining this database is something I do for "fun."

I'd been meaning to document the database more, but as mentioned above, I'm working on porting it over to the Baseball-Databank database, merging all of my information with MLB data. After that, the data will be primarily maintained there, phasing this database out. If there's something in particular you'd like to do with it, please feel free to ask and I'll see what I can do.