Relational Databases: A Quick Rundown

Relational databases are databases that know of other tables. You absolutely need to use a relational database to make a MMO game. The reason for this is that MMO games quickly get complicated and if you are always trying to loop through files to find a record, your game will suffer in performance and I guarantee that you will lose track of what data you are trying to store and retrieve.

With that said, relational databases use a language called T-SQL to operate on databases. T-SQL allows you to create tables in your databases, and to insert, update, and delete records, just to mention the most common functions. With these four options you can get started on creating the Kiddy Cartel game, although these four operations will not make you an expert in relational databases.

To create a table using T-SQL you need to use the CREATE TABLE function. The CREATE TABLE function takes the column definitions as parameters and a table name. The easiest way to explain how to create a table is to show you. Open up a command prompt and navigate to the mysqlin directory. Once you are in that directory, type the following:

mysql

This should start the mySQL command prompt. Once you are in the mySQL command prompt you need to connect to a database, so type the following:

connect kiddycartel

This will connect you to the Kiddy Cartel database. Now that you are in the database you can add the necessary tables to the database. You will need to enter the following lines into the command prompt to create the neighborhoods table:

CREATE TABLE neighborhoods
(
PlayerName              CHAR(64) NOT NULL PRIMARY KEY,
NeighborhoodName        CHAR(64) NOT NULL,
Password                CHAR(64) NOT NULL,

Bullies                 INT  DEFAULT 0,
PaperBoys               INT  DEFAULT 0,

LawnMowers              INT  DEFAULT 1,
LemonadeStands          INT  DEFAULT 0,
Bakeries                INT  DEFAULT 0,

Money                   INT  DEFAULT 300,
Lemonade                INT  DEFAULT 100,
Cookies                 INT  DEFAULT 50,

WaterBalloons           INT  DEFAULT 0,
PlasticBats             INT  DEFAULT 0,
SlingShots              INT  DEFAULT 0,

LastTurnCredited        DATETIME NOT NULL,
Turns                   INT  DEFAULT 50
);

This will create a 16-column table called neighborhoods. The first column in the database stores the player name and is called PlayerName. You specify the data type of the column after you define the name of the column. The PRIMARY KEY keywords tell mySQL that this is a unique column and that is what should be used to identify the records in this table.

Now that you have created the database and a table, you can insert records into the table. To insert a record into the table you will use the INSERT INTO function.

INSERT INTO neighborhoods(PlayerName, NeighborhoodName, Password, LastTurnCredited)VALUES(‘MyPlayer’, ‘MyTown’, ‘mmorocks’, GetDate())

This will insert a record that has the values MyPlayer as the player name, MyTown as the neighborhood name, mmorocks as the password, and the current date and time for the last turn credited column. The rest of the columns will contain the default values specified in the table definition.

Let’s say you want to update the MyPlayer record and change its password. To do that you will need to use the UPDATE function. The UPDATE function takes the table name and then the column you want to update. For example:

UPDATE neighborhoods SET Password = ‘newPassword’ WHERE PlayerName = ‘MyPlayer’

This will update the record associated with MyPlayer. Deleting a record is just as simple. You use the DELETE FROM function. The DELETE FROM function takes a table name as its main parameter.

DELETE FROM neighborhoods WHERE PlayerName = ‘MyPlayer’

The line above will delete the MyPlayer record. If you did not specify a WHERE clause in the DELETE FROM function it would delete all the records in the specified table. So make sure you always specify a WHERE clause unless you don’t mind losing all of your data.

That is a very quick rundown of some basic T-SQL functions. By no means is this a complete how-to on relational databases. But now that you have a general idea about what T-SQL is, and now that you have a database created for Kiddy Cartel, let’s take a more in-depth look at the specifications for Kiddy Cartel.