Pigeon Agents Development Demo Database - DatasmithSA/Pigeon-Voice-Training GitHub Wiki

In order to demonstrate the process of developing Pigeon Agents, we will create a demo database containing picking data which will act as the database of a WMS. There is a backup of this demo database available in the source code of this repository, if you prefer to restore it instead of manually creating the database. The backup was created for SQL Server 2012 Express. Contact Datasmith if you have any issues with restoring this database and would like a different backup or script that is compatible with your version of SQL Server.

The diagram below depicts the database schema for this database.

/images/demo/Pigeon-Voice-Demo-Database-Schema.png

  • User: this table will store all the users that can login via the Voice Application. The Pigeon login agent will be responsible for querying this table on a login request to check if a user with the given Username (Operator ID) and Password exists in this table.
    • UserId (uniqueidentifier): a unique surrogate key that is only used by the agents to identify records in this table.
    • Username (varchar): a unique identifier for a user in this table. It must correspond to the Operator ID set in Voice Console which will be sent with every request from the Voice Application on the device.
    • Password (varchar): the password for the user which will be captured on the Voice Application at the password prompt.
    • LoggedIn (bit): a boolean flag to that is set to true when the a user (operator) logs in and set to false and a user logs off. This of happens on the login and log off Pigeon Voice Agents.
    • DateCreated: the date and time when the user record was created.
  • PickList: this table will store header for a pick list. There will be pick items in a separate table associated to a pick list.
    • PickListId (unique identifier): a unique surrogate key that is only used by the agents to identify records in this table.
    • PickListNumber (int): a unique integer that is used to identify a pick list. Once the Voice Application downloads the pick list to the device, it will use this pick list number to query the items for the pick list.
    • Picked (bit): the boolean flag to indicate whether all items for this pick list have been picked. This flag will be set to true when the last item for the lis has been picked. Pick lists with this flag set to true will not be available to the Voice Application when trying to download a new pick list i.e. assignment.
    • DateCreated (datetime): the date and time when the pick list record was created.
  • PickItem: this table will hold all the items to be downloaded and picked by the Voice Application. Each item in this table references a pick list to which it belongs to.
    • PickItemId (uniqueidentifier): a unique surrogate key that is only used by the agents to identify records in this table.
    • PickListId (uniqueidentifier): the unique surrogate key of a pick list record, which this pick items belongs to.
    • Sku (varchar): the SKU of the item to be picked. Every type of product has a unique identifier, which is referred to as a SKU.
    • Location (varchar): the bin location of where the pick item indicating to the user/operator where the item is located in the warehouse.
    • CheckDigits (varchar): a random and unique number associated to a pick location. When the operator arrives at a location, the operator is required to speak out the check digits for this location in which will allow the Voice Application to verify that he is at the correct location in the warehouse i.e. it's used to validate that the operator has indeed walked to the location and is not simply going through the voice dialog without physically walking to the locations in the warehouse and picking.
    • QuantityToPick (int): the number of products to be picked at the given location.
    • Description (varchar): information field about the product to be picked at the given location. The operator has a "description" command available in the dialog to request information about the product, at which point the contents of this field will be spoken out to the operator.
    • QuantityPicked (int): the actual quantity that was picked by the operator. This field is sent as empty (0) to the device and the Voice Application will set the value based on the input from the operator.
    • PickStatusCode (char): a flag indicating the result of the pick i.e. it will be set to an 'R' if the complete quantity was picked and 'S' to indicate a short i.e. if the quantity picked was less than the required quantity. This field is sent as an empty character when downloaded by the device, and the Voice Application will set it's value.
    • PickedByUserId: the unique surrogate key of the user record indicating which user (operator) completed this pick.
    • PickedByUserName (varchar): the unique user name (Operator ID) of the user indicating which user (operator) completed the pick.
    • DateCreated (datetime): the date and time when the pick item record was created.