Dictation Data Dictionary

Dictation Data Dictionary

From TeleFlow

Jump to: navigation, search

This document describes the database tables that are required for the IVR system to function. Each table is in MySQL.

The Database Dictionary is sorted alphabetically by table name.


Contents

Table Descriptions

A detailed description of each table follows. For convenience, each table begins on a new page.

The bracketed () sections are table or table.field references.

Each dictation client (dictate_client) is one of the host’s customers, and is likely a healthcare company/clinic/doctors office/etc with one or more physicians (dictate_physician) working for it.

Calls are received and logged (call_log) by the system only if the DNIS is a host DNIS associated with a host's dictation client (dictate_client.client_dnis). A physician will call the system and proceed to dictate files (dictate_file).


Call_log

Primary Key:  call_log_id
Foreign Keys: 
Indexes: 

Call log, indicating calls that have come in, when, how long they lasted, etc.

#     Field Name     Type     Len     Description    
1 call_log_id int 10 Unique call log identifier, auto-incremented, primary key
2 call_start_date varchar 10 Date the call started.
3 call_start_time varchar 8 Time the call started on call_start_date.
4 call_end_date varchar 10 Date the call ended.
5 call_end_time varchar 8 Time the call ended on call_end_date.
6 call_duration int 6 Call duration in seconds.
7 call_port varchar 4 Telephony port the call was taken on.
8 dictate_client_id int 10 Dictation client’s ID for caller.
9 dictate_physician_id int 10 Physician ID of caller. 0 if undetermined/unknown.
10 dnis varchar 20 Called number to reach the system.
11 ani varchar 20 Caller ID of caller. Empty if unknown.


Dictate_client

Primary Key:  dictate_client_id
Foreign Keys:  None
Indexes: 

The host's dictation client. Usually, a client is a clinic/hospital/etc. Which client to run the phone service “on behalf of” is determined by the DNIS the caller uses to reach the dictation system.

#     Field Name     Type     Len     Description    
1 dictate_client_id int 11 Unique dictation client identifier, auto-incremented, primary key
2 client_name varchar 30 Dictation client/company/clinic/etc name.
3 client_dnis varchar 20 DNIS/Called Number used to reach the system by physicians working for this dictation client.
4 client_create_date varchar 10 Date client record was created.
5 client_create_time varchar 8 Time client record was created on client_create_date.
6 client_needs_record varchar 1 Y/N: Does a recording need to be done by/for the client the next time someone calls the client_dnis?
7 client_name_file varchar 50 Name of the file containing the client’s recorded name. (Empty if using TTS only)
8 client_requires_mrn varchar 1 Y/N: Does the client require physicians enter an MRN when dictating? If “Y”, physicians will be asked to enter a number (of up to 10 digits) followed by the pound key.
9 client_report_digits int 1 0-3 supported. If >=1, a physician is required to enter the total number of report digits.

Special: For Web: The DNIS that goes in this table will also need to be added (at the same time one is set up in this table) to the appropriate tfvoiceoffice database table. I have to determine exactly how this will work with the existing systems in place, and provide an exact set of instructions for this. I believe it will amount to a single INSERT statement on the appropriate table.


Dictate_file

Primary Key:  dictate_file_id
Foreign Keys:  dictate_client_id; dictate_patient_id; filepath_id
Indexes:  

The dictated file detail

#     Field Name     Type     Len     Description    
1 file_id Int 10 Unique dictation file identifier, auto-incremented, primary key
2 file_conf_num varchar 6 The dictated file’s confirmation number. These are not (completely) unique. They “cycle” from 000000 - 999999. Each file is assigned this number in the order in which it was started.
3 dictate_client_id int 10 Foreign key: Links to dictate_client.dictate_client_id. This links the dictated file to the client the physician works for.
4 dictate_patient_id int 10 Foreign key: Links to dictate_physician.dictate_physician_id. This links the dictated file to the physician who dictated it.
5 physician_number varchar 10 The physician number dictating the file. Physicians have an “ID” that they are aware of, which is unique per client. (The internal “dictate_patient_id” is OUR primary key, and wouldn’t be used for display online)
6 file_name varchar 100 The name of the dictated file.
7 file_date varchar 10 The date the file dictated was completed, in YYYY/MM/DD.
8 file_time varchar 8 The time the file dictated was completed, in HH:MM:SS.
9 file_port varchar 5 The IVR port number on which the file was dictated.
10 file_sent int 1 0/1: The file has not / has been sent for transcription.
12 file_status_transcription varchar 10 Status code for transfer of recording to transcription FTP location (Pending, Uploaded, Retry, Deleted)
13 file_status_web varchar 10 Status code for transfer of recording to administration web page (Pending, Uploaded, Retry, Deleted)
14 filepath_id int 10 Foreign key: Links to filepath.filepath_id. Linking to filepath allows you to determine where a file is located on disk.
15 call_dnis varchar 20 The DNIS on which the call was taken and this file was dictated.
16 call_ani varchar 20 The ANI of the caller dictating.
17 call_log_id int 10 Foreign key: Links to call_log.call_log_id. This can be used to link dictations to the call log related to them. (So, if you wanted to show what happened in a call, you could show the call log, and then each dictation for the call)
18 file_dir varchar
19 mrn int 10
20 report_type int 10 Optional report type to differential recording for post-processing



Dictate_physician

Primary Key:  dictate_physician_id
Foreign Keys:  dictate_client_id
Indexes: 

Dictation physicians, the people calling the system to dictate.

#     Field Name     Type     Len     Description    
1 dictate_physician_id int 10 Unique dictation physician identifier, auto-incremented, primary key
2 dictate_client_id int 10 Foreign key: Links to dictate_client.dictate_client_id. Links to the dictation client this physician is employed by.
3 physician_last_name varchar 30 Physician’s last name.
4 physician_first_name varchar 30 Physician’s first name.
5 physician_number int 10 Currently only allowed to be 4-digits. This is a unique physician number for the client.
6 physician_needs_record varchar 1 Y/N: Does a recording need to be done by/for the physician the next time he/she calls the system?
7 physician_name_file varchar 50 Name of the file containing the physician’s recorded name. (Empty if using TTS only)


Filepath

Primary Key:  filepath_id
Foreign Keys: 
Indexes: 

Locations used to store dictation files. (These are changed periodically – every so many 1000 dictations – to prevent directories getting to full and causing processes to slow down as a result)

#     Field Name     Type     Len     Description    
1 filepath_id int 11 Incremented, Primary Key used by the ‘Call’ table to denote storage location of files
2 dir_loc varchar 60 Directory location, eg: C:\host_dictate\recordings\volume001
3 volume_num int 11 Numeric representation for the volume number.
4 status varchar 20 Status used for archiving, valid values are:
ACTIVE File path is the current path being written to.
CLOSED Old file path – no longer writing files here.
5 max_volume_filecount int 10 The maximum size – in total files - that the volume can grow to.
6 create_date varchar 10 The date the volume was created/started, format “yyyy/mm/dd”
7 create_time varchar 8 The time the volume was created/started on create_date, format “hh:mm:ss”
8 close_date varchar 10 The date the volume was closed, format “yyyy/mm/dd”
9 close_time varchar 8 The time the volume was closed on close_date, format “hh:mm:ss”