IVR Example Lesson 1. Create Database

In this step we will create a database to store room and user information in. If you want to use you own database, just note that the table names will not be the same as in the tutorial. The following instructions, also, only apply to phpMyAdmin. However, the steps should be similar of other database managers.

Create New Database

Open phpMyAdmin. Select the Databases tab. If the Databases tab does not appear, click the home button on the left. Create a new database called room_reservation. The default 'Collation' will be OK for the character set.

After creating the new database, select it in the left pane.

Set Up Tables

In this sample, we use three database tables: user_profile, room_profile, and room_schedule. You can manually create the tables or use the SQL scripts provided. To insert SQL scripts, click the SQL tab to open the input window. The tables are described below:

user_profile is used to store user information, such as phone numbers and email addresses. Copy the following code into the SQL text window and select Run to create the following table:

CREATE TABLE IF NOT EXISTS user_profile (
user_id int(11) NOT NULL AUTO_INCREMENT,
phone_number varchar(32) NOT NULL,
email_addr varchar(64) NOT NULL,
PRIMARY KEY (user_id)
);

room_profile is used to store room information, such as room number and room size.

CREATE TABLE IF NOT EXISTS room_profile (
room_id smallint(6) NOT NULL,
room_size smallint(6) NOT NULL,
PRIMARY KEY (room_id)
);

room_schedule is used to store room reservation information, such as meeting rooms and times.

CREATE TABLE IF NOT EXISTS room_schedule (
room_id smallint(6) NOT NULL,
user_id int(11) NOT NULL,
reserve_status enum('reserved','waiting') NOT NULL,
start_time datetime NOT NULL,
end_time datetime NOT NULL
);

Add Initial Data

Once the tables are created, you can populate them with some initial data. Replace the user_profile data with your own information. Then, run the following SQL statement.

INSERT INTO room_profile (room_id, room_size) VALUES
(200, 10),
(300, 20);

INSERT INTO user_profile (user_id, phone_number, email_addr) VALUES
(100, '14080001111', '[email protected]'),v (101, '16501112222', '[email protected]');

---