14.1 Database Tables
- Billy Sewell (Unlicensed)
ServiceScheduling holds promise sets in the following tables:
- Time Bands in
sp259_time_bands - Band Types in
sp258_band_types - Promise Sets in
sp257_band_lists - Promise Set Descriptions in
sp256_promise_set_descriptions
The following sections describe these tables.
Note that these tables are described in Oracle terminology – for a translation to SQL Server parlance see below.
Oracle Data Type | SQL Server Data Type | Comment |
|
|
|
|
|
|
|
| Or |
|
|
|
sp256_promise_set_descriptions
This table is used to specify promise set (aka band list) names, and an optional ‘number required’ default for appointment offering
Table Definition
list_name varchar2(2) not null description varchar2(15) not null num_req number(3) field_id varchar2(100) not null
Keys
primary key (list_name)
Column Descriptions
| Column | Description |
|---|---|
list_name | List identifier.
|
description | Free text description. |
num_req | Number required when appointments (promises) are requested using this promise set. |
This column does not support wide (i.e. multibyte) characters.sp257_band_lists
This table defines one or more ordered lists of time bands to be used in appointment offering. A set of entries with the same list_name is known as a Promise Set.
Table Definition
list_name varchar2(2) not null band_name varchar2(4) not null sequence number(2) not null
Keys
primary key (list_name, band_name) foreign key (band_name) references sp259_time_bands (band_name) foreign key (list_name) references sp256_promise_set_descriptions (list_name)
Column Descriptions
| Column | Description |
|---|---|
list_name | List identifier. Names the Promise Set.
|
band_name | Time band identifier. ‘Name’ in the table in section 1.1.
|
sequence | Not used internally but available for ordering the time bands for display. |
sp258_band_types
This table is used to define time band types – e.g. two hour time bands, half day time bands etc. The band_type should only be a single character.
Table Definition
band_type varchar2(2) not null description varchar2(15) not null sequence number(2) not null field_id varchar2(100) not null
Keys
primary key (band_type) unique (description)
Column Descriptions
| Column | Description |
|---|---|
band_type | Band type identifier.
|
description | Band type description. |
sequence | Sequence number; used internally to order the time bands within the list for deletion of duplicates; the lowest sequence number is kept. |
sp259_time_bands
This table defines the start and end time of each named time band. The band_type should only be a single character.
Table Definition
band_name varchar2(4) not null description varchar2(15) not null start_time date not null end_time date not null band_type varchar2(2) not null field_id varchar2(100) no null
Keys
primary key (band_name) unique (description) foreign key (band_type) references sp258_band_types (band_type)
Column Descriptions
| Column | Description |
|---|---|
band_name | Time band identifier.
|
description | Time band description. |
start_time | Start time. |
end_time | End time. |
band_type | Time band type.
|
Sequence
The sequence field of sp258_band_types does not order the list of Time Bands returned to the SMS in an offer. It is used internally by ServiceOptimizer when deciding which of a pair of duplicate offers to delete; higher sequence numbers are deleted first. For this reason, higher sequence numbers should be allocated to longer Time Bands.