Future Media Standards & Guidelines

Database Design and Development Standards v1.00

1. Introduction

1.1. This document describes the standards to be used when designing and developing live database (backed) applications on bbc.co.uk.

1.2. bbc.co.uk database applications SHOULD normally use Oracle or MySQL. This is because a supported shared development, test, and operational infrastructure exists for both these technologies. Standards for both these RDBMS (relational database management systems) are covered in this document.

1.3. Other resources

Use this document alongside the optional (Oracle) Database Design Template.

The Database Design Template provides a structure for documenting your application. Apply the information given in both documents to ensure that you adopt a consistent and rigorous approach in the design and development of your database applications.

1.4. Audience

The audience for this document will be database designers, developers, or technical leads. It may also include developers performing design or development code reviews, or QA staff reviewing design documentation or code.

1.5. Scope

How to perform database design and how to write database code are outside the scope of this document.

Installation/configuration issues that have implications for performance are included, but most deployment information can be found in other documents. See the optional template Database Configuration and Deployment Guide.

1.6. Responsibilities

The BBC will provide all database hosting and management services within a centrally managed environment. Day to day database administration (including backups) will be performed by the BBC's database administration team. See Infrastructure/Configuration.

Database application developers are responsible for producing database schema (object) creation scripts, code, schema design, and code documentation. Further details of required deliverables and preferred formats can be found in Documentation and Work instructions.

Top of page

2. Architecture and Design

2.1. Users

2.1.1. Oracle

2.1.1.1. Schemas and users

2.1.1.1.1. In Oracle, operating system user authentication SHOULD NOT be used for database application users.

2.2. Tables

2.2.1. Table names

2.2.1.1. Normally, use only alphanumeric characters plus the underscore (_) character for table names.

2.2.1.2. Table names should not normally start with a digit.

2.2.1.3. It is recommended that table names are restricted to 25 characters or less.

2.2.1.1. MySQL

Table names are case sensitive under UNIX (or Linux). To avoid confusion, define/declare table names in upper case in any database creation SQL scripts.

2.2.1.2. Oracle

Oracle table names are not case sensitive or platform sensitive. For consistency with MySQL, also define them in upper case.

2.2.2. Column names

2.2.2.1. Normally, use only alphanumeric characters plus the underscore (_) character for column names.

2.2.2.2. Column names should not normally start with a digit.

2.2.2.3. It is recommended that you restrict column names to 25 characters or less.

2.2.2.4. No SQL language reserved words should be used as column names.

2.3. Indexes

2.3.1. Index/constraints and performance

2.3.1.1. Identify primary, unique, and foreign keys (to enforce important or critical data integrity) as part of the initial design process.

2.3.1.2. To guard against slow queries degrading performance on the shared database infrastructure, the most commonly performed SQL queries should be analysed with an explain plan. This should use sufficient data to generate representative query execution plans.

2.3.1.3. Indexes should be used where appropriate, use explain/explain_plan to demonstrate. A list of the SQL queries and their indexes used should also be submitted.

2.4. Tablespaces

2.4.1. BLOB/CLOB storage

If you are using significant amounts of BLOB/CLOB storage, or access to BLOB/CLOB columns is quite high volume, a separate tablespace should be requested for BLOB/CLOB storage. This should be requested from Siemens Internet Operations.

2.4.2. Index Creation

It should be requested/defined in schema creation scripts that indexes be created in a tablespace different from the main table data tablespace.

2.5. Interfaces

2.5.1. All database APIs must define error handling, and success/failure notification.

2.5.2. When accessing database from Java code, JDBC should be used.

2.5.3. When accessing databases from perl code, DBI, and DBD::Oracle (for Oracle), orDBD::MySQL (for MySQL) should normally be used

2.5.4. When accessing databases from C++, MySQL++ (MySQL Connector/C++) (for MySQL), or OCCI (for Oracle) should be used

2.5.5. ODBC may also be used to connect to Oracle or MySQL databases if desired/necessary

2.5.6. Use connection pooling where possible/appropriate to reduce connection set up/tear down overhead for connections from client or middle tier code to server databases.

2.6. RDBMS specific features

Select data types appropriate to the target RDBMS. Designs should be as RDBMS agnostic as possible: for example, minimize use of sets and abstract data types, unless these are key to the design. It is accepted that that there will inevitably be some RDBMS specific data typing.

Top of page

3. Storage

3.1. Oracle

Tablespace allocation for all (Oracle) schema objects MUST be specified in all delivered schema creation scripts. This allows table, index and optionally, large object data to potentially be stored separately, potentially improving application and overall system performance.

3.2. MySQL

MySQL offers a number of storage types. The only ones that should be used are:

  • MyIsam - for general (especially read only) data.
  • InnoDB - for transactional data, or data with important referential integrity. [It is not possible at the time of writing (release 4.0.15) to assign particular tables or indexes to specific data files/tablespaces - a feature that can be useful for performance reasons.]

3.3. Encryption and Checksums

3.3.1. Encryption

Encryption MUST be used for storing user passwords. Support for this is available for both MySQL and Oracle.

3.3.2. Checksums

MD5 should normally be used as the algorithm for producing a checksum if it is required to check the integrity of data at source/destination.

3.4. Replication

Any replication requirements should be discussed with the Database Architecture group and Siemens Internet Operations.

Top of page

4. Development

4.1. Data load

4.1.1. Any database data loading processes MUST create a summary log, an error file (in the event of any errors, and a bad file for records that failed to load.)

4.1.2. Consideration should be given to recovering loads that fail mid way through, e.g. COMMIT points.

4.2. Coding standards

Minimal database coding standards are enforced, but database code should always aim to be clear, concise, consistent, and commented.

4.3. (perl)

See the (Server-side) Applicaton Development Standards Restricted access - post NDA only.

4.4. Security

4.4.1. Database users should only be granted sufficient privileges to enable them to perform the tasks that they need to.

4.4.2. When connecting from web applications, where possible always connect to the database as the same user. This minimizes the chances of leaving a security hole against a particular user, and enables connection pooling techniques to be more readily taken advantage of to improve performance.

4.4.3. Plain text passwords should never be displayed in client side code.

4.4.4. Plain text passwords should not be stored on public facing servers.

4.5. Date/Times (GMT)

4.5.1. Date/Times within the database should normally be stored as GMT (Greenwich Mean Time). Any conversion required should be done at the client end of the application.

4.5.2. When converting date/times from internal to string format, the format: "YYYY-MM-DD HH24:MI:SS" should be used if there is a requirement to sort resultant string values 'chronologically'.

4.6. Error handling – abstracted

The error handling strategy should be clearly defined for all database APIs.

4.7. Headers

4.7.1. All scripts/modules should contain a header and footer including a description of the main purpose of the script/module, the name of the module, the date last updated, the author (last modifier), and the version.

4.7.2. All procedures and functions should contain a header and footer including a description of purpose, parameters, exceptions thrown, and possible return codes.

4.8. Cursor handling

All cursor resources should be released after use.

4.9. Managing connections

4.9.1. When NOT using connection pooling, connections should always be closed after use. When using connection pooling, connections should be released back to the pool after use.

4.9.2. All database resources associated with a used connection should be explicitly released before the connection is closed or released back to the connection pool. Resources MUST be released correctly when error or exception conditions occur.

4.9.3. Connections should be cached in mod_perl.

4.10. Abstraction of interface to database

4.10.1. Connection details (server, username, password) should be stored in configuration files and not hardcoded in code. This enables database connection code to be deployed to test and operational environments without any need for code amendment (and/or recompilation).

4.10.2. Application code should make use of common (database abstraction layer) connection code to access the database. This enables application code to be insulated from changes to underlying database connection information, and for amendments to be made (e.g. to introduce/implement connection pooling, software load balancing etc.)

4.11. Packages and stored procedures

For reasons of performance, modularity, and information hiding, (Oracle) stored procedures should always be packaged (see Oracle PL/SQL documentation for further information).

4.12. Maintenance

4.12.1. All schema changes MUST be made via a script, and not via an ad hoc change

4.12.2. All schema change scripts must insert a row into a BBCI_DB_CHANGE_LOG table to enable the patch status of the database to be determined.

4.12.3. Any database change script that is backed out should normally be backed out with a separate database change script that inserts an additional row into the change log.

4.12.4. Change Log table

Every database (MySQL)/schema (Oracle) should include a BBCI_DB_CHANGE_LOG table to record amendments made to the schema.

The BBCI_DB_CHANGE_LOG table, should have the following columns/datatypes :

  • DCL_ID NUMBER(6)
  • DCL_script_name VARCHAR(50)
  • DCL_script_rundate DATE (/TIMESTAMP)
  • DCL_user VARCHAR(30)
  • DCL_change description VARCHAR(255)
  • DCL_VERSION VARCHAR(30)

where:

DCL_ID is a unique identifier for the change log entry (can be auto generated) or obtained from a centrally held document which provides a document log of changes.

DCL_SCRIPT_NAME is the filename of the SQL schema creation/upgrade script.

DCL_SCRIPT_RUNDATE is the date/time at which the script was applied to the database (obtained at install time).

DCL_USER is the name of the user who applies the change (obtained at install time).

DCL_CHANGE_DESCRIPTION is a brief summary of the changes introduced by the script.

DCL_VERSION is the version of the schema/database after the script has been applied. This should tally with design documents/ERD model diagrams, or whatever versioning scheme is used for the database on your project.

Top of page

5. Documentation

5.1. Deliverables

5.1.1. Volumetrics/sizing information is vital to enable operational staff to determine the appropriate hardware platform for any new database application deployment. For every database to be delivered, you MUST complete the BBC Database Application Checklist to support capacity planning as discussed. This should ideally be completed as early in the project as is feasible – in case the database being delivered cannot be supported on the existing BBC database infrastructure. Figures relating to volumetrics and sizing must be included in the BBC Database Application Checklist for EACH table/entity planned.

5.1.2. You MUST provide a physical schema design diagram to the BBC Database Architecture Team for any delivered/deployed database (schema creation script and code). Preferred formats are: Visio; (Quest) QDesigner, or JPEG image. For reasons of maintainability, Visio (or QDesigner or Erwin) are the preferred formats for the capture or production of schema diagrams.

5.1.3. You MAY optionally complete a database design, and specifications of specific interfaces to the database may to further document the delivered database schema and code.

5.1.4. You MUST provide evidence of application/SQL performance testing when delivering database application code. Generated execution plans may be included with the supplied database documentation.

5.2. Table, column comments

5.2.1. Every table should have a comment/description of its purpose included in the (optional database design document, and schema creation script (to ensure this is available in the database data dictionary).

5.2.2. Every column of every table should also have a column comment in the optional database design document (where available/completed) explaining its purpose, default value, etc.

5.3. Database Documentation location

Project database documentation should be supplied to your BBC Technical Account Manager who MUST submit to the BBC Database Architecture Team, for this to be stored on the Infrastructure Database Project Documentation Page [Internal BBC Document internal BBC doc – gain access via your Technical Account Manager].

5.4. Documentation naming standards

Every project involving database design should produce the following documents. It is suggested that documents be named as below with the relevant (abbreviated) project name included as appropriate:

Document nameDocument (description)
<Abbreviated_Project_Name>_DB_CHECKLISTDatabase application checklist
<Abbreviated_Project_Name>_DB_SCHEMA(_version)Database schema diagram
<Abbreviated_Project_Name>_DB_DESIGN(optional) Database Design
<Abbreviated_Project_Name>_DB_IF_00n(optional) Database Interface Specifications (001 - 00n for n DB interfaces)
<Abbreviated_Project_Name>_DB_PERFORMANCEDatabase performance testing results

5.5. Contacts

Please email the Database Group contact on the Infrastructure Database Group page [Internal BBC Document internal BBC doc – gain access via your Technical Account Manager] with any questions regarding database standards, guidelines, or documentation.

5.6. Pldoc

Oracle packaged procedure documentation MAY be generated using the PLDOC utility. This will generate Javadoc style documentation. If PLDOC is used, package specification files should include a header (@headcom) comment block, and a comment block for each function/procedure detailing parameters (@param), (all possible) returned values (@return), and exceptions thrown (if any).

Top of page

6. Work Instructions

6.1. Delivery to operations

6.1.1. (Commented) database schema creation scripts should be supplied as text files.

6.1.2. (Commented) database package code should be supplied as text files. Package specifications and bodies may be submitted in separate files if preferred. A separate file for each individual package is perfectly acceptable.

6.1.3. perl modules containing database code should be delivered in accordance with the (Server-side) Application Development Restricted access – Post-NDA only.

6.1.4. Database code should be delivered to your BBC Technical Account Manager who will arrange for it to be submitted to the Siemens Internet Operations, via ToDo request form Internal BBC Document internal BBC doc – gain access via your Technical Account Manager], or to BBC-Broadcast Interactive Operations, via the web request form [Internal BBC Document internal BBC doc – gain access via your Technical Account Manager].

6.1.5.Communications with the operational database administrators regarding all scripts required to install/update the schema should be via your BBC Technical Account Manager, as mentioned above.

6.1.6. Currently, a 3 day notice period is required/recommended by operations to process database deployment/configurations.

6.1.7. All associated documentation should be delivered along with schema and code deliverables.

Top of page

7. Infrastructure/Configuration

7.1. Server instances

7.1.1. There are shared internal and external facing MySQL development server instances and databases. See MySQL Scratch Development Database Usage [Internal BBC Document internal BBC doc – gain access via your Technical Account Manager] and MySQL Scratch Dev3 Database Usage [Internal BBC Document internal BBC doc – gain access via your Technical Account Manager] for details.

7.1.2. The operational DBA teams should be contacted regarding the creation of new database instances/schemas - please contact your BBC Technical Account Manager, or see BBC Site Requests - Database setup [Internal BBC document internal BBC doc – gain access via your Technical Account Manager] for Siemens Business Services managed BBC database server support requests, and/or for BBC Broadcast managed BBC database [Internal BBC document internal BBC doc – gain access via your Technical Account Manager] (development and content production) servers.

7.2. Archiving

7.2.1. Consideration must be given to archiving strategy during the database design phase. Details of the approach to be taken must be stated in the project Database Application Checklist and/or (optional) Database Design documents.

7.2.2. Generally, unless there are specific legal requirements, database data should be archived and stored for no more than 6-12 months from the point in time of its active life expiration (or its archive date).

7.3. Backups

On operational databases, assume that full backups will be made weekly, and incremental backups made daily. Any specific backup/recovery requirements should be discussed with Siemens Internet Operations or the BBC Database Architecture Team.

7.4. Scheduled Downtime

7.4.1. Regular Downtime

There is currently no regular downtime on operational Oracle databases (hot backups are being performed).

7.4.2. Planned Downtime

Internet ops aim to inform customers 48-hours before commencing any maintenance work that requires the database to be off-line. Normally, customers will be informed by an e-mail sent to the notification address supplied on the inception of a project.

Top of page

8. Legalities

8.1. Data Protection Act

8.1.1. The Data Protection Act, 1984 makes many requirements of those who hold data on individuals. The data must:

  • Be obtained and processed fairly and lawfully.
  • Be held only for lawful purposes which are described in the register entry.
  • Be used or disclosed only for those or compatible purposes.
  • Be adequate, relevant and not excessive in relation to the purpose for which they are held.
  • Be accurate and, where necessary, kept up to date.
  • Be held no longer than is necessary for the purpose for which they are held.
  • Be able to allow individuals to access information held about them and where appropriate correct or erase it.
  • Be surrounded by proper security.

8.1.2. If you are holding data on individuals you MUST provide a mechanism whereby they can view, correct or erase their records. You may charge a reasonable amount for this service.

8.1.3. For more information see the Information Security and Privacy Standards.

Top of page

9. References

Top of page

BBC © 2014 The BBC is not responsible for the content of external sites. Read more.

This page is best viewed in an up-to-date web browser with style sheets (CSS) enabled. While you will be able to view the content of this page in your current browser, you will not be able to get the full visual experience. Please consider upgrading your browser software or enabling style sheets (CSS) if you are able to do so.