IVR Tutorial 2: The Database

This sample make heavy use of database, which we assume you are familiar with already. To learn more, please take a look at MySQL database and its admin tools like phpMyAdmin. You can also setup a local test environment on your computer by downloading and installing a pre-packaged installer like XAMPP, which are usually much easier to setup.

In this sample, we use the following three database tables: user_profile, room_profile, and room_schedule. The SQL scripts shown here are for MySQL database.

The table: user_profile is used to store user information, such as phone number and email address. Use the following statement to create this 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)
);

The table: 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)
);

The table: room_schedule is used to store room reservation information, such as meeting room and time

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
);

Once the tables are created, you can populate it with some initial data. Run the following SQL statement and make sure to replace the user_profile data with your own information.

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', 'you@yourcompany.com'),
(101, '16501112222', 'me@mycompany.com');