GNM_RCS_Schema_Design_TR

• • 1. Purpose

This document describes how the database schema that underpins RCS and SLM has been designed. It does not detail every object, just the general design approach.

• 1. Scope

This document does not list all objects in the schema design as it would lead to a huge and unwieldy document.

The sections hereafter provide an insight into how the application is developed.

This document does not describe the underlying database management as that is managed by GNM IT.

• 1. .Schema

     1. Oracle users

        1. TLIB

The TLIB user was the first to be created, when the Text Library application was developed. A schema exists under this user which stores the data for the Text Library i.e. details of content published by GNM and also by other newspaper publishers.

• 1. RCS

The RCS user can see (via grants and synonyms) all the TLIB data, and it also owns its own schema of tables for the RCS application (including SLM)

• 1. ZAR

The ZAR schema does not own any data itself. It is a gatekeeper to Oracle AR – it allows Oracle AR to send data messages to RCS/SLM through PLSQL packages ZAR has execute privilege on.

This design ensures Oracle AR only has access to a restricted set of packaged procedures.

• 1. ZAP

The ZAR schema does not own any data itself. It is a gatekeeper to Oracle AP – it allows Oracle AP to send data messages to RCS/SLM through PLSQL packages ZAP has execute privilege on.

This design ensures Oracle AP only has access to a restricted set of packaged procedures.

• 1. ZBFG

The ZBFG schema does not own any data itself. It is a gatekeeper to an Oracle schema for bespoke billing software and data.

• 1. XXINTG

The XXINTG schema in Oracle HR is connected tofor the purposes of querying Oracle HR staff and casuals data.

• 1. Application users

Every user of the RCS application is created as an Oracle USER. These users do not own any database objects of their own, but have access to objects owned by TLIB and RCS through ROLES granted to them.

Database users are dropped when users leave GNM, or no longer need access to the system.

.

• 1. Tablespaces

Table are created in separate tablespaces to indexes for historic reasons.

• 1. TLIB data

Tables owned by the TLIB user exist in the TLIB_TABLE tablespace, and indexes in TLIB_INDEX. For historic reasons some data segments also exist in TLIB_TABLE2 and TLIB_INDEX2.

• 1. RCS data

Tables owned by the RCS user exist in the RCS_TABLE tablespace, and indexes in RCS_INDEX.

• 1. Other tablespaces

The other tablespaces such as SYSTEM and those created as TEMPORARY or for other tools that have been installed are out of scope of this document, and the responsibility of the DBA function.

• 1. .Security

     1. Passwords

All user passwords are unique and held in the database using the DES3 encryption software provided in the Oracle database DBMS_OBFUSCATION_TOOLKIT package.

A password is setup when the user is created, and they user can change it at will thereafter, or when forced to in accordance with GNMs password policy.

GNMs password policy has been implemented in full – enforcing passwords to be changed frequently, avoid reuse, ensure they are suitably strong, and locked after persistent failure to enter it correctly.

• 1. Roles

Database roles have been used for four main purposes:

• 1. Database connection

Each user is granted the CONNECT role.

As they logon to the application the session is altered to ensure the CURRENT schema is RCS. This ensures they can see all the RCS data and software without requiring SYNONYMS, making the system easier to maintain as it evolves. This is also marginally faster than using synonyms.

• 1. Data privileges

Read access to tables in the TLIB schema is granted to the TLIB_READ role and write access to TLIB_WRITE.

Read access to tables in the RCS schema is granted to the RCS_READ role and write access to RCS_WRITE.

Most RCS users are granted the roles TLIB_READ, RCS_READ and RCS_WRITE; read only users only have access to the _READ roles.

• 1. Database PLSQL privileges

EXECUTE privilege is granted to TLIB_EXECUTE on PLSQL objects owned by TLIB that RCS application users need to execute.

EXECUTE privilege is granted to RCS_EXECUTE on PLSQL objects owned by RCS that RCS application users need to execute.

All users are granted the roles TLIB_EXECUTE and RCS_EXECUTE.

• 1. Application menu privileges

The menu system in RCS is a simple bar menu with drop down submenus. This is built using a standard Oracle menu form, and uses Oracle standard role-based security. Each menu item is accessible to one or more database roles.

Each user is granted one of these roles when the user is setup.

• 1. .Tables

The application comprises many standard relational tables.

Index organized tables have not been used very much at all, as these do not integrate well with Oracle Forms.

• 1. Table names

Tables are given meaningful plural names, with each word separated by an underscore, and the names are not case sensitive. For example

CURRENCIES

CONTENT_PROVIDERS

ORDER_ROYALTY_FEES

ORDER_ROYALTY_FEES_AUDIT

Most of the tables that are only used by the Syndication Licensing Module (SLM) within RCS are prefixed with SYND_ as this was requested when the system was originally designed.

• 1. Table aliases

To help with the naming of other schema objects each table is given a four-letter alias. These aliases are a logical concept i.e. they are NOT created as synonyms on the database.

The construction of the alias follows a set of rules:

• • For tables with four or more words in their name the alias comprises the first letter of the first four words;

  • For three-word names the first letter of each word is taken and the 2nd letter from the 3rd word;

  • For two-word names the first letter of each word is taken and the 2nd and 3rd letter of the 2nd word;

  • For single-word names the alias is the first 4 letters of the table name.

Taking the example table names from the previous section:

Exceptions to the above rules occur as follows:

• • To avoid Oracle reserved words e.g. table USERS has an alias USRS to avoid using the word USER which is a reserved word.

  • To avoid duplication e.g. CONTRIBUTOR_TYPES and CONTENT_TYPES cannot both have the alias CTYP.

• 1. Comments

Every table has a database comment created for it which succinctly describes its contents.

• 1. Columns

     1. Names

Where a unique numeric identifier is required the column is called ID, and defined as NUMBER(10,0).

Any foreign key that refers to an ID column is named <Alias>_ID e.g. CURR_ID

Where a unique alphanumeric reference is generated it is called REFERENCE and is defined as VARCHAR2(6). References are created that avoid letters and numbers that may be confused e.g. O and 0, and have a prefix letter that provides the context. E.g. C is the prefix for COMMISSIONS, A for AGREEMENTS, S for CUSTOMERS (as C already used), R for syndication Requests and so on

Any foreign key that refers to a REFERENCE column is named <Alias>_REFERENCE

Where a simple alphabetic code is used to uniquely identify data it is called CODE

Any foreign key that refers to a CODE column is named <Alias>_CODE

Columns that store a date value (no time) are suffixed _DATE e.g. PUBLISHED_DATE

Columns that store a date and time value are suffixed _DATETIME e.g. CREATED_DATETIME

Columns that store a user’s Oracle username are suffixed _BY e.g. APPROVED_BY

Columns that store a Y or an N and suffixed _YN e.g. SPIKED_YN

All other columns are given meaningful names, and the size of columns of a particular name should be quite consistent across the schema. For example FEE columns tend to be NUMBER(15,2), DESCRIPTION columns VARCHAR2(4000), and so on.

• 1. Comments

Every column in every table has a database comment created for it which succinctly describes the data stored.

• 1. .Constraints

     1. Primary & unique keys

Every table has a primary key named <Alias>_PK e.g. CURR_PK

• 1. Unique keys

Some tables additionally have a unique key name <Alias>_UK e.g. CURR_UK

• 1. Foreign keys

Referential integrity is maintained through the use of foreign key constraints. Their names follow this convention <Alias>_<FKAlias>_FK

For example the COMMISSIONS table has a column CURR_ID that identifies the ID of the relevant CURRENCY. The foreign key that maintains this relationship is therefore called COMM_CURR_FK.

Foreign key constraints are created as DEFERRABLE and ENABLED. Some are defined to ON DELETE CASCADE, and some aren’t, as appropriate.

• 1. Check constraints

Check constraints are used to maintain the integrity of data within a record e.g. to ensure only a Y or an N is ever entered into a Y/N column. Their names follow this convention <Alias>_<Column(s)>_CK

For example the check constraint on the SPIKED_YN column in the COMMISSIONS table is called COMM_SPIKED_YN_CK.

Oracle, by default, creates constraints to manage not null columns, naming these SYS_Cnnnnnnn.

• 1. .Indexes

     1. Primary & unique key indexes

Primary and unique indexes are created as part of the constraint definition, and therefore the index always has the same name as the constraint.

• 1. Foreign key constraint indexes

All foreign key constraints are supported by an index of the same name. This improves query times and reduces the scope of DML locks, both of which hugely improve system performance.

• 1. Other indexes

Due to the vast amounts of ways in which data is accessed in RCS and SLM a great number of other indexes have been created on the tables. These indexes’ names aim to describe the data indexed, as follows <Alias>_<Columns abbrev/Purpose>

For example the COMMISSIONS table has these additional indexes, amongst many others:

• • COMM_FULFILLED_CSV2_FRMT_AGREE

  • This is a NORMAL index that contains the FULFILLED_YN, CSVA_ID_SEG2, FRMT_CODE and AGREED_DATE columns

  • COMM_SINGLE_ASSIGNMNT_CHASER

  • This is a FUNCTION-BASED NORMAL index which supports the process that identifies records of data for which a chaser email needs to be sent for a single assignment contract.

• 1. Index types

Most indexes are NORMAL, whilst a few are BITMAP, as appropriate.

FUNCTION-BASED NORMAL indexes have been used extensively to manage queues within the system.

Index organised tables have not been used due to integration issues with Oracle Forms. One exception is an IRS Forms 1099 reporting requirement, which involves no Oracle Forms and so an IOT is used.

• 1. .Sequences

A separate sequence exists for every table that has a unique ID column or a unique REFERENCE column (that requires a REFERENCE to be generated from a sequence).

Sequences are named <Alias>_ID e.g. CPRO_ID, CURR_ID and ORFE_ID

In Oracle 11g sequences can be accessed in PLSQL, but for historic reasons and because Oracle Forms does not have 11g PLSQL the next available value from each sequence is provided for in a packaged function in the relevant object package. (These “object” packages are described in a subsequent section.)

• 1. .Table triggers

Database trigger are used for a variety of functions, including:

• • default values – setting column value not supplied by the inserting software;

  • auditing – writing to audit columns and audit tables;

  • denormalisation – to copy changed to data to where it has been denormalised;

  • automated processing – to process content on arrival in RCS

• 1. Names

Table triggers’ names always start with the table alias, followed two characters that define when they fire, and then one, two or three more characters identifying which events they fire on.

<Alias>_<T><L>_<E>

Where:

<T> can be B (Before) or A (After)

<L> can be R (Row) or S (Statement)

<E> can be any combination of I (Insert), U (Update), or D (Delete), in that order

An additional suffix may be added to the name to describe what action is being performed.

Example 1 COMM_BR_I_AUDIT

A Before Row trigger firing on Insert to the COMMISIONS table, which writes audit data

Example 2 COMM_BS_IU_CURRENCY

COMM_AR_IU_CURRENCY

COMM_AS_IU_CURRENCY

A suite of 3 triggers on the COMMISSIONS table that write back to the CURRENCIES table when a currency is used for the first time.

• 1. Efficiency

The aim is that triggers only fire when necessary, thereby improving performance. Triggers are defined with an UPDATE OF list of columns if they are only meant to fire when a restricted list of columns change.

A WHEN clause is used whenever it is possible to restrict trigger firing purely on the basis of NEW and OLD data values.

• 1. Mutating table / Constraining table solutions

Original solution

Prior to the upgrade the Oracle 11g the trigger-based solution to these issues was as follows:

When a row level trigger writes data to another table that is related by a foreign key, it can lead to a mutating table error or constraining table error. To avoid this, but retain the commit unit integrity, the related table must be written to on a statement level trigger. The requires a set of 3 triggers and a package as follows:

• • Package with an array defined as package data, which is used to remember the row or rows to process;

  • Before statement trigger instantiates the package, so the array is ready to be written to;

  • Before/After record trigger writes a new row to the array in the package for each record processed;

  • After Statement trigger loops through the array and writes to the related table for each row encountered

This is a standard solution for pre-11g Oracle databases, and is well documented on the Oracle forums, AskTom etc.

Oracle 11g solution

Oracle 11g introduced the concept of “compound” triggers, where the three triggers described in the original solution above can be combined in a single program unit. Compound triggers are also felt to be better as the order they fire in can be defined.

All occurences of the original solution have been replaced with compound triggers in the RCS schema, but some occurrences remain in the TLIB schema.

• 1. .Views

     1. Purpose

Database views exist in the RCS schema as follows:

• • To make it possible to base an Oracle Forms block on multiple tables in an efficient way.

  • To provide lists of data that are useful across the application, where the definition of the list can be maintained (modified and tuned) in one place.

• 1. Names

View names follow the same naming conventions as table names.

View names are not prefixed with V, VW or VIEW as the purpose of a view is to logically describe the data provided.

Prefixes and suffixes are also avoided to make it easy to replace a view with say a MATERIALIZED view of the same name, or even a managed table, without having to change the name used or the software that accesses it.

Examples view names include:

CONTRIBUTING_INDIVIDUALS lists a subset of all CONTENT_PROVIDERS

COMMISSION_LETTERS lists all commission letters that are due to be sent

RIGHTS_HISTORY lists rights profiles relating to contributor contributions

• 1. Materialized views

Materialized views are avoided in the current architecture. This is because the refresh of these views causes a lot of redo to be written to the redo logs, which then get sent to the standby database server where they are then applied. This has proved to be very inefficient, as c=vast amounts of redo can be written when very little or no data has changed. Consequently materialized views have been designed out of the system with a single remaining exception at the time of writing being the TOWNS materialized view.

• 1. .Synonyms

Synonyms are not generally used to provide access to database objects – public synonyms are too open, and private synonyms would require a lot of management in a system that is continually evolving. Instead when users login they ALTER SESSION SET CURRENT_SCHEMA=RCS.

However, a relatively small number of synonyms are still required, as follows:

• 1. Inter schema synonyms

RCS and TLIB each need access to a subset of each other’s objects, and so each has private synonyms that translate to the other’s objects. This ensures that the application never requires the object owner to be coded into the software when any object is referenced in the other schema.

• 1. Database link synonyms

Every object accessed over a database link (e.g. objects in Oracle AP) has a synonym in RCS.

This ensures that only the RCS software only needs to reference the synonym, rather that the full path of the remote object including database link name.

This also makes it easy to have different database links on the development databases, as only the synonym needs to be redefined i.e. no software changes are required.

• 1. Reserved word synonym

A synonym EMAIL exists for the package MAIL so that Oracle Forms can see the bespoke MAIL package on the database. Without this synonym Oracle Forms cannot see the MAIL package, possibly because MAIL is a reserved word – it is a destination property of a report object.

• 1. .Packages

Database packages are used in four ways:

• • firstly to provide a layer of object-specific software;

  • secondly to provide a layer of business functions;

  • thirdly as a wrapper for other database software; and

  • lastly to implement mutating table and constraining table trigger-based solutions.

• 1. Object packages

A separate package of code exists for every table in the application. The purpose is to store all cursors and software that relate to a table in one place, avoiding duplication of cursors, and making it easy for a developer to find and reuse code.

Most packages contain, but are not limited to, a standard set of procedures and functions:

Where possible, packages don’t declare package data as package state makes the hot release of packages more difficult.

Where package data is required during the execution of a procedure or function, but not required to persist beyond that execution, PRAGMA SERIALLY_REUSABLE is declared.

Where a packaged function or procedure cannot be executed more than once concurrently then a session lock is taken and later released using the DBMS_LOCK package.

• 1. Functional packages

Another layer of packages exists which perform specific business functions, and usually do not relate to a specific table. For example

Function packages frequently call object packages and sometimes call other function packages, again avoiding the duplication of software.

• 1. Wrapper packages

On a few occasions standard Oracle packages have been given wrapper packages in RCS

• 1. Caching

Packages whose names are suffixed _CACHE exist purely to take advantage of function result caching in Oracle 11g. These packages contain one or more functions whose specification includes RESULT_CACHE.

Unfortunately Oracle Forms uses an older version of PLSQL, and therefore cannot call functions that are specified using syntax specific to 11g, so these functions are accessed in forms via a wrapper function in another PLSQL 10 compliant package.

• 1. Email/file creation

Large numbers of emails are sent from RCS. This process involves RCS generating files of meta data that software (known as fip) from a 3rd party supplier (called Fingerpost) interprets – it then sends the emails via the Notes servers.

RCS has a package MAILAPI which acts as a wrapper for the standard database package UTL_FILE, for this specific purpose. Another package MAIL calls this wrapper package, and is itself called from across the system.

The aim here is to simplify the software needed to generate emails, and also to wrap UTL_FILE so that it could one day be replaced with UTL_SMTP without large amounts of code needing to be changed.

• 1. Mutating table and constraining table solutions

As described in the section for Table Triggers packages are also used to remember the Primary key data of rows that are being processed in one table, so that an After Statement trigger can later work through remembered records and write to another table.

For further details cross reference the Table Triggers section.

• 1. Standalone procedures & functions

A small number of procedures and functions exist. Most of these exist for the purposes of reusable data fixes.

End of Document