Page 07: Creating our first database table

Before soon we will need some data from the database to work with. The database we have is not very interesting since it's empty. To have something to work with in the next pages we will have to create some tables. The first one is regions.

database table : regions

A table that is connected to a lot of other tables is the regions-table. Since it won't bring in any values from other tables it's a good table to start with.

The regions-table will contain data on countries and regions and will later be used to set members nationality, regions for a specific title, switching navigation region on front-end, set release areas for list items and so forth. Needless to say it's a very important table. Create it with the following SQL-syntax:

CREATE TABLE `regions` (
`ID` INT( 11 ) UNSIGNED NOT NULL AUTO_INCREMENT PRIMARY KEY COMMENT 'primary key',
`regionID` VARCHAR( 3 ) NOT NULL COMMENT 'ISO 3166-1 alpha-3 country code',
`regionName` VARCHAR( 100 ) NOT NULL COMMENT 'english region name',
`officialName` VARCHAR( 100 ) NULL COMMENT 'english official name of the country',
`tvSystem` VARCHAR( 20 ) NULL COMMENT 'TV Broadcast System (Analogue)',
`consoleRegion` TINYINT( 1 ) UNSIGNED NOT NULL DEFAULT '0' COMMENT 'Boolean. Is it a console region?',
`titleRegion` TINYINT( 1 ) UNSIGNED NOT NULL DEFAULT '0' COMMENT 'Boolean. Is it a title region?',
`countryRegion` TINYINT( 1 ) UNSIGNED NOT NULL DEFAULT '0' COMMENT 'Boolean. Is it a country?',
`dateAdded` INT( 11 ) UNSIGNED NOT NULL COMMENT 'timestamp when added to db'
) ENGINE = innodb COMMENT = 'contain region data';

Let's discuss the various fields in the table:

• ID : The primary key of the table. This integer will be stored in a multiple of other tables as a reference to these values.
• regionID : This is the three character country code as specified by ISO 3166-1 alpha-3 plus a few others. You can find a list of the ISO 3166-1 alpha-3 country codes at Wikipedia. Use it to populate your database later. The flag-images will be named after the regionID as well.
• regionName : This is the english name of the region. Will later be used as an alt- and title- text when hovering a flag image.
• officialName : This is the english official name of the region.
• tvSystem : This field will store the name of the tv-system used in that particular region. This doesn't necessarily coinside with the console region lockouts but it will give a hint. Since this information is kind of overkill I will be kind enough to give you a SQL-import file that will completely populate you table with all region data.
• consoleRegion : This is a Boolean value that is set to true ("1") if the region is a console region. By console regions means a stronghold region where different versions of a console is released. For example USA is a console region since it's the stronghold of NTSC-consoles. Other such regions are Japan, Europe and Brazil. You decide which regions you want to support.
• titleRegion : This is also a Boolean value. This time it is used to set if a region is a title region. Title regions are countries that often hget the game titles translated to their own language. France for example is a title region.
• countryRegion : This is another Boolean value. Regions with this field set to true will appear in the nationality lists. Since we want to create regions that isn't independent countries we want a way to separate those from the real countries.
• dateAdded : This is a UNIX timestamp of the time when the row was inserted into the database.

Before we move on I would like to point out some crucial things about the naming and structuring of the database. There are a few conventions that needs to be followed, otherwise the database will conflict with some upcoming scripts with the result that the CMS won't work at all. Here are some rules that must be followed in order for the scripts to work properly:

• Dates: All dates will be stored as timestamps rather than strings. The scripts will contain conversion functions to display the dates properly later on. The naming of database table fields that contain dates MUST start with lowercase "date". For example "dateAdded", "dateModified", "dateReleased" and "dateBirth".
• Booleans: Booleans are values that can be either true or false. Fields that will contain Boolean information needs to be an interger field, preferably TINYINT and MUST have a maximum length of 1. Boolean fields will later automatically be identified based on the facts that they contain integers with the maximum length of 1. Values stored will be either 1 (for true) or 0 (for false).

Now that we have created a table we have something to work with. The table is still empty, but it's there. Now we can create the files needed to Create new rows in the table. So let's get to it:

Next Up: Initializing CRUD

<<-- INDEX
<<-- PAGE 06 PAGE 08 -->>