Building the Back End: Part 1 - Realtime Web Apps: With HTML5 WebSocket, PHP, and jQuery - page 183

Building the Database
Before we can go any farther in the app, the database needs to be built. The two remaining controllers—Question and
Room—both store data and will thus need models.
We already discussed how the database would be structured back in Chapter 5, so we'll jump right into the code
here. In phpMyAdmin, the terminal, or whatever your preferred method of executing MySQL queries happens to be,
run the following commands:
CREATE TABLE IF NOT EXISTS 'presenters' (
'id' int(11) NOT NULL AUTO_INCREMENT,
'name' varchar(255) COLLATE utf8_unicode_ci NOT NULL,
'email' varchar(255) COLLATE utf8_unicode_ci NOT NULL,
PRIMARY KEY ('id'),
UNIQUE KEY 'email' ('email')
) ENGINE=MyISAM DEFAULT CHARSET=utf8 COLLATE=utf8_unicode_ci;
CREATE TABLE IF NOT EXISTS 'questions' (
'id' int(11) NOT NULL AUTO_INCREMENT,
'room_id' int(11) NOT NULL,
'question' text COLLATE utf8_unicode_ci NOT NULL,
'is_answered' tinyint(1) NOT NULL DEFAULT '0',
PRIMARY KEY ('id'),
KEY 'room_id' ('room_id')
) ENGINE=MyISAM DEFAULT CHARSET=utf8 COLLATE=utf8_unicode_ci;
CREATE TABLE IF NOT EXISTS 'question_votes' (
'question_id' int(11) NOT NULL,
'vote_count' int(11) NOT NULL,
PRIMARY KEY ('question_id')
) ENGINE=MyISAM DEFAULT CHARSET=utf8 COLLATE=utf8_unicode_ci;
CREATE TABLE IF NOT EXISTS 'rooms' (
'id' int(11) NOT NULL AUTO_INCREMENT,
'name' varchar(255) COLLATE utf8_unicode_ci NOT NULL,
'is_active' tinyint(1) NOT NULL DEFAULT '1',
PRIMARY KEY ('id')
) ENGINE=MyISAM DEFAULT CHARSET=utf8 COLLATE=utf8_unicode_ci;
CREATE TABLE IF NOT EXISTS 'room_owners' (
'room_id' int(11) NOT NULL,
'presenter_id' int(11) NOT NULL,
KEY 'room_id' ('room_id','presenter_id')
) ENGINE=MyISAM DEFAULT CHARSET=utf8 COLLATE=utf8_unicode_ci;
This code creates the database tables necessary for the app to run. If you view the database in phpMyAdmin,
you'll see the newly created tables (see Figure 8-4 ).