VoiceXML Developer Series: A Tour Through VoiceXML, Part X
In this edition of the VoiceXML Developer, we will create the Access database for Frank's Pizza Palace and record the audio prompts based upon the dialog flow that we created in the last article.
Creating a data model
The first thing we need to do next is design a data model for our Pizza Palace Access database. We can determine the tables and columns that need to be created by analyzing the call flow diagrams we created in the last article. The requirements are fairly simple. We need to be able to lookup a customer's address by phone number and we need to be able to save pizza orders. There is one additional requirement. If a customer does not have a customer record, we must record their address. This address will be stored as a .wav file on the Web server. New customer records should be marked as new and reviewed on a regular basis so that someone can listen to the recordings and type the corresponding address. Until a new customer record has a text address, we can play the address recording for the customer and have them confirm that as their address as opposed to synthesizing the text address.
For our application, we will need a customer table, which will contain their phone number, address, city, state, and zip. It will also contain the file name of the recorded address that the customer uttered when their record was created.
We will also need an order table, which will contain the order date and time, the pizza size, the type of crust, and the toppings. We should also add a boolean flag that indicates whether or not the pizza has been delivered. The diagram below contains a data model representation of our Pizza Palace database.
The Phone column of the Customers table has been set as the primary key of the table. The PizzaOrder table contains a foreign key constraint that references the Phone column of the Customers table. This will ensure that a pizza order cannot contain a phone number that does not also exist in the Customers table. The AddressAudioFile contains the filename of the address utterance, which is used to manually input the Address field. The PizzaDeliveredFlag is can be Yes or No. This field is set to Yes manually when the order has been delivered to the customer.
Now that we have our design, it's time to create the database. I created an Access database file named pizza_palace.mdb and used the design view to create the tables. In the design view for the Customers table (shown below), I specified all fields as being of type Text whose size was 50 except for the Phone field whose size I set to 15.
Page 1 of 2