Introduction 2
- Jason Andrew
On This Page:
9.3.2 All changes from earlier versions of the document are signposted by 
, or 
to reflect whether there is an addition/correction, or a removal of detail.
Caution
This specification is subject to change by ServicePower without notice and users should not rely on its contents.
Note: this document provides brief descriptions of how the various tables are used by ServiceScheduling. This information is not definitive. For a full description of the ServiceScheduling functionality, refer to the appropriate Functional Specification. The Functional Specification should be followed in the event of any discrepancy between that document and this.
Some date and time values are set to the current time, where this is a column a convienient approach is to set the column default value to sysdate (Oracle) or getdate() (SQL Server). Such columns are denoted in the following table descriptions as having a default value of sysdate. For SQL Server databases this should be read as the column having a default value of getdate().
About This Document
| Purpose | This is the specification of the database schema for ServiceScheduling applications. |
| Readership | To be read by Database Administrators who need to understand the nature of the ServiceScheduling database in order to manage the database in the context of a particular installation. |
| Assumptions | The ServiceScheduling database specification can be changed without notice between major releases and thus no assumptions can be made about the structure or content of the database from one release to the next.The ServiceScheduling database is not a supported interface. |
| Timezones | Many of the database fields are hold date/time data. In all cases the time zone used for this data is specified. Where no time zone is available for a given field then it should be assumed that it will use the timezone of the DBMS server machine. |
| Constraints | constraint names are not listed in this document. They are, however, reported by the DBMS as part of the error message if a constraint is violated. Information about the constraints may also be obtained from the data dictionary tables user_constraints and user_cons_columns. |
Scope
This document is the specification of the ServiceScheduling database schema for ServiceScheduling 9.3 version. It provides a description of each table and of the columns within each table. Primary, unique and foreign keys are listed for each table. Details of indexes, sequences, triggers and packages are also provided.
Database Management System (DBMS): This is a generic specification which is valid for each DBMS that this version of ServiceScheduling supports (i.e. Oracle and Microsoft SQL Server).
Naming Conventions
| table names | have a prefix sp<nnn>_, where <nnn> is a 3 digit number. |
| Foreign key constraints | are named using the convention sp<nnn>_fk_sp<mmm> where sp<nnn> is the table name prefix of the dependent table and sp<mmm> is the table name prefix of the referenced table. Where there is more than one foreign key constraint involving the same tables, the additional constraints are named sp<nnn>_fk2_sp<mmm>, sp<nnn>_fk3_sp<mmm>, etc |
| Primary key constraints | are named sp<nnn>_pk; unique key constraints are named sp<nnn>_uk |
| Check constraints | are named sp<nnn>ch<colname>, where <colname> is the name of the constrained column. |
| Not Null integrity constraints | are not explicitly named, but take the DBMS generated default constraint names |
Data Types
The data types used in the table descriptions are Oracle data types which have the following meanings in SQL Server:
Oracle Data Type | SQL Server Data Type | Comment |
CHAR(<size> CHAR> | NCHAR | All NCHAR columns should have a collation of Latin1_General_BIN. |
DATE | DATETIME |
|
NUMBER(n) | INT if n < 10 DECIMAL if n >= 10 | Some columns are later converted to BIGINT or INT by DBInstall as appropriate for the purpose. |
VARCHAR2(<size> CHAR) | NVARCHAR(<size>) | All NVARCHAR columns should have a collation of Latin1_General_BIN. |
LONG RAW | IMAGE |
|
| FLOAT | FLOAT | |
| BLOB | VARBINARY(MAX) | |
| CLOB | NVARCHAR(4000) | All NVARCHAR columns should have a collation of Latin1_General_BIN. |
Disclaimer of Warranty
ServicePower makes no representations or warranties, either expressed or implied, by or with respect to anything in this document, and shall not be liable for any implied warranties of merchantability or fitness for a particular purpose or for any indirect, special or consequential damages.
Confidentiality and Copyright
This document contains information confidential to ServicePower Technologies plc. This information is patented under European Patent Number 97301142.2-2201 (Appointment booking and scheduling system). The information is supplied by ServicePower Technologies plc on the understanding that it will not be used in any way which is or may be detrimental to the commercial interests of ServicePower Technologies plc. In particular, the contents may not be disclosed in part or in whole to any other party without the prior written consent of ServicePower Technologies plc.
Copyright in this document remains vested in ServicePower Technologies plc and no copies may be made of this document or any part thereof except with the agreement of the company.
Copyright © 2015, ServicePower plc
All rights reserved.
GOVERNMENT RIGHTS LEGEND: Use, duplication or disclosure by the U.S. Government is subject to restrictions set forth in the applicable ServicePower license agreement and as provided in DFARS 227.7202-1(a) and 227.7202-3(a) (1995), DFARS 252.227-7013(c)(1)(ii) (Oct 1988), FAR 12.212(a) (1995), FAR 52.227-19, or FAR 52.227-14, as applicable.
"ServicePower" and ServicePower’s products are trademarks of ServicePower Technologies plc. References to other companies and their products use trademarks owned by the respective companies and are for reference purpose only.