Dictation Data Dictionary

From TeleFlow

---

Revision as of 00:19, 18 March 2008

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 1 Table Descriptions 1.1 Call_log 1.2 Dictate_client 1.3 Dictate_file 1.4 Dictate_physician 1.5 Filepath

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.
11	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.
12	call_dnis	varchar	20	The DNIS on which the call was taken and this file was dictated.
13	call_ani	varchar	20	The ANI of the caller dictating.
14	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)

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”