oracle database design template

New Media and Technology

New Media (Oracle) Database Design Template

Document AuthorisationName: Mike Hilton

Position: New Media Database Architect

Signature:

Date: 29th April 2003

Document referencesVersion: 1.0

Date: 29th April 2003

BBC New Media and Technology New Media (Oracle) Database Design Template.

1 Document Control

1.1 Work carried out by:Name Department ExtMike Hilton Infrastructure Cluster 73988

1.2 ReviewersName Position Ext

1.3 Document HistoryVersion Date Author Description1.0 29/04/03 Mike Hilton First issue.

Any comments or queries about this document should be addressed to Mike Hilton

CI Ref. :Version : Issue 1.0Date : 29/04/03

of 27


Contents

1 DOCUMENT CONTROL................................................................................21.1 Work carried out by:.............................................................................21.2 Reviewers..............................................................................................21.3 Document History.................................................................................2

2 INTRODUCTION............................................................................................52.1 Scope, Approach and Methods............................................................52.2 How to Review.......................................................................................52.3 Related Documents...............................................................................5

3 SUBSYSTEM/APPLICATION OVERVIEW...................................................63.1 Architecture...........................................................................................6

3.1.1 Hardware Architecture...............................................................................................63.1.2 Software Architecture................................................................................................63.1.3 Interfaces...................................................................................................................63.1.4 Datastores................................................................................................................. 6

4 DATABASE DESIGN DECISIONS................................................................64.1 Assumptions.........................................................................................74.2 Standards Deviations...........................................................................74.3 Entity Mapping......................................................................................7

4.3.1 Mapping rules............................................................................................................74.3.2 Entities and Attributes Not Implemented...................................................................74.3.3 Non-trivial Mapping....................................................................................................74.3.4 Additional Objects......................................................................................................84.3.5 Key mappings............................................................................................................84.3.6 Other Deviations........................................................................................................9

4.4 Denormalisation....................................................................................94.4.1 Performance Improvement........................................................................................94.4.2 Functional Support..................................................................................................10

4.5 Journaling and History.......................................................................104.6 Business Rules...................................................................................104.7 Implied Functionality..........................................................................10

5 INTERFACES AND DEPENDENCIES........................................................115.1 Interfaces.............................................................................................11

5.1.1 Interface <Name/Id>................................................................................................115.2 Dependencies......................................................................................11

6 REPORTING AND MIS................................................................................126.1 Requirements......................................................................................126.2 Design issues......................................................................................12

7 DATA ACCESS...........................................................................................127.1 Role Definitions...................................................................................127.2 Users....................................................................................................127.3 Table Access Patterns........................................................................13

8 PHYSICAL IMPLEMENTATION CONSIDERATIONS................................138.1 Storage of Large Objects...................................................................138.2 Usage of Queuing...............................................................................13


of 27


8.2.1 Object Types...........................................................................................................138.2.2 Queue Tables..........................................................................................................148.2.3 Advanced Queues...................................................................................................148.2.4 Consumers..............................................................................................................148.2.5 Producers................................................................................................................14

8.3 Partitioning..........................................................................................148.4 Usage of other RDBMS Specific Features........................................15

9 NON-FUNCTIONAL DESIGN......................................................................159.1 Security Design...................................................................................159.2 Availability and Resilience Design....................................................159.3 Scalability............................................................................................159.4 Platform Management.........................................................................159.5 Performance Design...........................................................................159.6 Error Processing.................................................................................169.7 Backups and Recovery policy...........................................................169.8 Archiving..............................................................................................16

10 ASSUMPTIONS AND ISSUES.................................................................1610.1 Design Assumptions..........................................................................1610.2 Outstanding Issues.............................................................................16

11 APPENDIX A - TABLE RELATION DIAGRAMS...................................1712 APPENDIX B – MODULE LIST................................................................1813 APPENDIX C - TABLE DEFINITIONS...................................................1914 APPENDIX D - VIEW DEFINITIONS.......................................................2015 APPENDIX E - SNAPSHOT DEFINITIONS...........................................2116 APPENDIX F - TRIGGER DEFINITIONS................................................2217 APPENDIX G - ENTITY TO TABLE IMPLEMENTATION......................2318 APPENDIX H - ROLE DEFINITIONS......................................................2419 APPENDIX I - DATABASE DESIGN CHECKLIST................................25

19.1 Checklist................................................................................................25


of 27


2 Introduction This Database Design provides the basis for the <Subsystem/Application Name> database design. It defines the database that will support the <Subsystem/Application Name> Data Model. It describes both the logical and physical definition, non-functional issues, and the database interfaces; storage aspects are defined in the physical database design sections. The design is created with expected data volumes, functional and non-functional usage of the tables, and performance considerations and requirements in mind. . The following topics are covered in this document:

assumptions and decisions on database design entity-mapping table, column and view definitions primary, unique and foreign key definitions column and row level validation rules (check constraints) rules for populating specific columns (sequences, derivations, denormalised

columns, journaling) interfaces and dependencies with other components data access description

2.1 Scope, Approach and MethodsThe Database Design for the <Subsystem/Application Name> is composed of definitions for database objects derived by mapping entities to tables, attributes to columns, unique identifiers to unique keys and relationships to foreign keys. During design, these initial definitions are enhanced to support the functionality described in the functional specification / use cases and defined in the primary and supporting modules of the application High Level Design.

2.2 How to ReviewPlease use the following criteria when reviewing the content of this Database Design (a fuller checklist can be found in appendix H):

Is the structure correct? Are the contents appropriate for the audience? Is the order logical? Are the contents complete? Is the table model complete in reflecting the logical database model? Is the model and module list complete in supporting the functionality described in

the functional specification / use cases ?

2.3 Related DocumentsThis specification refers to the following documents:


of 27


3 Subsystem/Application Overview Mandatory sectionThis section provides a brief overview of the subsystem. MUST be consistent with the high level design (if any exists), or can refer directly to the high level design document if this exists.

If the HLD is incorrect then this should be flagged to the Lead Designer.

Components within each area should be labelled using decimal notation. These tags must be used in both diagrams and text when referencing subsystems and components.

3.1 Architecture

3.1.1 Hardware ArchitectureThis section provides an overview of the hardware architecture. Briefly identify the hardware and present a diagram showing how the components are connected.

3.1.2 Software ArchitectureThis section must list the components within the subsystem/application and must include a component diagram.

Include a diagram of all components within the subsystem/application and all external systems. Include components, datastores and interfaces within the subsystem/application as well as interfaces between internal components and external systems.

Label all external interfaces consistently with those used in the high level design. Label internal interfaces for reference. If direction is indicated on an interface, make it clear whether it denotes the direction of initiation or the main direction of dataflow.

3.1.3 InterfacesBriefly identify the interfaces to external systems - each database interface will be described in more detail below, and documented in an external interface specification.

3.1.4 DatastoresBriefly describe all datastores including databases, file systems and media data stores.

4 Database Design Decisions This section contains the decisions that were made when designing the database for <Subsystem/Application Name>. Problems, alternative solutions and motivated choices are listed below. The section also lists any design assumptions that had to be made. In case the assumptions are results of ambiguities or lack of details, they will need verifying by the analyst team.


of 27


4.1 AssumptionsList any assumptions made due to lack of information (eg. in the functional specifications or data model).

4.2 Standards DeviationsList any deviations from the standards and guideline set out for BBC New Media Projects. See [???] for naming standards.

4.3 Entity MappingThis section states the mapping rules and lists tables and columns that either:

do not originate from a single entity, are not implemented at all, or are otherwise treated in a non-trivial manner.

4.3.1 Mapping rulesWhen mapping entities to tables, the following rules were applied:

Entities are mapped onto tables in a one to one manner. Attributes are mapped to columns in a one to one manner. One-to-many relationships are mapped to foreign keys. Many-to-many relationships are implemented using a keys-only table.

(List additional rules used).

4.3.2 Entities and Attributes Not ImplementedThe following table lists entities and attributes that are not implemented as tables and columns.

Entity/Attribute Description Reason for not implementing

4.3.3 Non-trivial MappingThe following table lists all tables that are not derived from an entity in a one-to-one fashion. This includes arc and subtype mapping.

Table/Column Mapped Purpose Reason for CI Ref. :Version : Issue 1.0Date : 29/04/03

of 27


from deviation

4.3.4 Additional ObjectsThe following table lists database objects (tables or columns) that where not derived from an entity, but were added to the database design for the purpose listed below. This includes intersection tables used for mapping many-to-many relationships.

Table/column Description Purpose

4.3.5 Key mappings

4.3.5.1 Sequences in KeysThe following tables have primary keys created from sequences:

Table Primary key column Sequence

The following tables have a surrogate primary key columns instead of a composite primary key consisting of a foreign key column plus a ‘sequence within fk’-column:

Table Comments


of 27


The following tables do have a composite primary key consisting of a foreign key column plus a ‘sequence within fk’-column:

Table (Primary) foreign key columns

Sequence

4.3.6 Other DeviationsOther deviations from a one-to-one mapping of entity and attribute names to table and column names and any foreign key naming deviations are listed below.

Entity/Attribute/Relation Table/Column/Foreign Key Column

Reason for deviating

4.4 DenormalisationTo improve performance or otherwise support specific functionality, redundancy is sometimes added to the design. Two types of redundancy are distinguished, performance denormalisation and functional denormalisation. The first type is aimed at improving performance, the second is needed to support the proposed functionality of the system.

4.4.1 Performance ImprovementTo maintain redundant or denormalised data needed to improve performance, the following objects were modified:

DenormalisedTable/Column

Source table or entity Rules and methods for maintaining

integrity


of 27


4.4.2 Functional SupportTo maintain redundant or denormalised data needed to support the proposed functionality of Error: Reference source not found the following objects were modified:

DenormalisedTable/Column

Source table or entity

Rules and methods for maintaining integrity

4.5 Journaling and HistoryTo accommodate for journaling of data manipulation and to keep/archive/delete historic data, the following additions were made:

Object Description Journaling Issues

4.6 Business RulesList here any rules modelled in the data model, specified for the entities in the data model or in the functional specification that have not been implemented as table/column constraints/column-defaults. Specify whether they are implemented as triggers or if they will have to be implemented by the modules using the tables.

4.7 Implied Functionality The model contains supporting modules that perform functions that may be trivial to the users but are considered to be important functionality by the developers. Decisions regarding database objects involved in such implied functionality are discussed below. (Examples: Batch Handling and Printing, Authorisation, User Interface).


of 27


5 Interfaces and Dependencies

5.1 InterfacesDetailed APIs for interfaces can be described here or left to individual module interface specifications.

5.1.1 Interface <Name/Id>

5.1.1.1 PurposeDescribe the purpose of the interface

5.1.1.2 CharacteristicsSummarise the interface characteristics, consistent with the High Level Design.

5.1.1.3 Interface ArchitectureDescribe the interface architecture.

5.1.1.4 API and Error ConditionsOptionally describe the API and error conditions for the interface. This detail can be left to a separate module interface specification.

5.1.1.5 Usage ExamplesOptional section to describe an example of using the interface.

5.1.1.6 SecurityMandatory section to describe the protocols, user authentication, encryption, signing and control of access (at the interface entry point). (State N/A if there is nothing to describe here).

5.2 DependenciesList here any dependencies for the <Subsystem / Application Name> schema. One type of dependencies can be foreign keys across schemas. List foreign key dependencies here:

Table and column in <subsystem / application> schema

Schema the table/ column refers to

Table Comments (eg. Sharing data or just sharing definitions)


of 27


6 Reporting and MIS

6.1 RequirementsDescribe any reporting and / or MIS requirements.

6.2 Design issuesDescribe the design to support reporting and / or MIS requirements.

7 Data Access This section describes aspects on how data defined in this design is used. The section defines which roles are needed and highlights any information that are of particular importance for the physical database implementation (e.g. Tables subject to high insert or delete activity, tables with certain archiving rules). (see also: Database Design Guidelines).

7.1 Role DefinitionsThe following roles are defined as ‘Groups’. The definition of the roles’ privileges are found in appendix H.

Role-name Purpose

7.2 UsersThe following users are recognised as being required.

User name Purpose

A description should be provided as to the anticipated strategy for managing users, along with estimates of user volumetrics


of 27


7.3 Table Access PatternsBelow is a list of particularly performance-critical functions and their table usage. Other volumetric information needed for the physical database design is documented in Appendix C – Table Definitions.

Function Peak Frequency Tables used

Also list any tables that can be classified as one of the following:-high-volume read only-high-volume insert-high-volume updates

8 Physical Implementation Considerations

8.1 Storage of Large Objects

Large objects with maximum size of 20mb will normally be stored as BLOBS, whereas other large object will be stored as BFILE.

BLOB-columns:<list table.column-names>

BFILE-columns:<list DIRECTORY alias name, estimated size and which table and columns the directory covers>

8.2 Usage of QueuingDefine and describe the use for queues(i.e. asynchronous messaging techniques) and how they will be used. Justify why they have been used. Include a description of which functionality the queue implements and what queuing technology is chosen to implement it (e.g. Oracle Advanced Queue, JMS).

8.2.1 Object Types

Payload Abstract Data Type Definition


of 27


8.2.2 Queue Tables

Queue Table

Payload

Sort List

Multiple Consumers

MessageGrouping

Comment AutoCommit

8.2.3 Advanced Queues

Queue Name

TableName

QueueType

MaxRetries

RetryDelay

RetentionTime

Dependency Tracking

Comment

Auto Commit

8.2.4 Consumers

Queue Name

Consumer List <list all recipients of the queue>

8.2.5 Producers

Queue Name

Producer List < list all message originators (eg. function, trigger – see module list Appendix)>

8.3 PartitioningAny partitioned tables should be described as follows:

Partition table

Index equi-partitioned (Y/N)

Partition column

Partition value

Partition Name

Partition size

Comments (reason for partitioning: eg. Performance, archiving)


of 27


Any partitioned indexes (other than described above) should be described as follows:

Table Name

Index Name

Partition column

Partition value

Partition Name

Partition size

Comments (reason for partitioning: eg. Performance, archiving)

8.4 Usage of other RDBMS Specific FeaturesList here the use of any specific features used that are not documented in the design tool used. A justification is needed whenever such functionality is employed.

9 Non-Functional Design This section provides an overview of the non-functional design for the database.

9.1 Security DesignDescribe how authentication, integrity and confidentiality requirements are supported within the database and interfaces to it. This will include summarising the interface and database security requirementr.

9.2 Availability and Resilience DesignDescribe how the database design supports the subsystem / component availability and resilience requirements.

9.3 ScalabilityIdentify how the database design supports scalability requirements.

9.4 Platform ManagementIdentify how the database design supports the monitoring and management facilities provided by the operational Platform Management subsystem.

9.5 Performance DesignAreas of the database(/subsystem) design which need to be highly performant should be identified in this section. Describe how the database has been designed for performance.


of 27


9.6 Error ProcessingExplain the general error processing strategy adopted by the design, and how it is supported within the database design / to be implemented in the database code developed.

9.7 Backups and Recovery policyDescribe the backup and recovery policy to be used.

9.8 ArchivingDescribe the archiving policy to be used.

10 Assumptions and Issues Mandatory section

10.1Design AssumptionsDocument any assumptions made during design, e.g. assumed constraints, assumptions about other systems or where requirements analysis was unclear. See also: section 4.1 above (Database Design assumptions)

(State ‘none’ if appropriate).

10.2Outstanding Issues

Note that at this stage, these should general have been converted either into design statements within the main body of the document or into Design assumptions listed in previous section.


of 27


11 Appendix A - Table Relation Diagrams The diagram(s) in Appendix A show the tables for the <Subsystem / Application Name> schema and their relations. The relations are implemented as foreign key constraints. Two separate diagrams are shown:

(i) Logical Design

(ii) Physical Design

Not all relations between the diagrams are shown (for clarity). This information is available in the Table Design reports included with this document.

LegendDrawing conventions used in the Table Relation Diagrams are defined in the design tool used.

Describe any deviations below.


of 27


12 Appendix B – Module List List any modules related to (- to be implemented to support) the database design - including:

modules added to effect denormalisation for performance or functional reasons

journalling or historic data maintenance

batch processing/daemons e.g. to “garbage collect” database/file systems

handling of user authentication/authorization

generic user interface handlers/controls

non-standard messaging

tool integration modules

backup and recovery

Describe any modules

All other required database functional and non-functional requirements

Module Reference Module Name Purpose


of 27


13 Appendix C - Table Definitions

The report shows: Table/View Alias and Display Title Table/View Description Column Summary Comments Primary, Unique and Foreign Key (Estimated) Volumetrics (e.g. initial rows, 6 mths, 2

yr)


of 27


14 Appendix D - View Definitions


of 27


15 Appendix E - Snapshot Definitions


of 27


16 Appendix F - Trigger Definitions


of 27


17 Appendix G - Entity to Table Implementation


of 27


18 Appendix H - Role Definitions

Detail all roles, and the privileges they will have on the schema objects (defined above).


of 27


19 Appendix I - Database Design Checklist This list serves as a guide for checking the quality of the Logical Database Design. By answering the questions and taking the appropriate actions the Logical Database Design will be completed and corrected.

19.1Checklist Naming Conventions

Check as prescribed by Design Standards.

Graphical RepresentationIs a graphical representation available?Is it consistent with the other documentation?Can it be used by the build team?In what ways does the logical model differ from the conceptual schema?

Completeness of Specification in Design tool Which slots in the design tool screens have been left open?Why?

Choice of Database ObjectsHave particular database objects been avoided or not used?If so, then why? (Think of views, sequences, sequence number tables, indexes.)

Interpretation of Conceptual SchemaDo the logical and physical models cover all and only the conceptual schema?Have all non trivial translations been adequately documented?Can this interpretation easily be used in a Generator environment?

Table DefinitionCheck as prescribed by Design Standards.How have super- and subtypes been handled?Is the table usage valid with respect to the CRUD matrix. (Note that a table may be modified through a view.)Has the life cycle of all tables been covered completely by the modules?

Column DefinitionCheck as prescribed by Design Standards.Have domain definitions been used correctly?Have the datatypes been defined according to the standards?Are system generated keys used?Is the column usage valid with respect to the CRUD matrix? (Note that a table may be modified through a view.)Is the life cycle covered completely by the modules?Have datatypes been chosen adequately?How will status columns be handled?


of 27


Constraint DefinitionPay attention to constraints that only hold for subtypes, or that hold under special conditions.Check if there is no overlap between constraints.Have all necessary constraints (PK, FK, CHECK) been defined on views?

Definition of RelationshipsCheck as prescribed by Design StandardsInvestigate the following:

the design of arcs non transferable relationships recursive relationships time-dependencies

(Which time is used, commit time, screen time? What operations may be performed on time-dependent data, under what conditions?)

Definition of Other Database ObjectsHave other objects (views, indexes, domains, sequences) been defined completely and consistently?Is there some unusual distribution or other property of the data that makes the use of these objects (un)necessary?

Special ProblemsHave special measures been taken to deal with the following:

journaling (What data will be journaled: user, session id, object, operation or module, and how?Will the journaled data be used for “rollback to X” or “roll forward from X” purposes ?)

high performance demands(response time for reports, screens, overall response time, partial)

heavy or peak usage, high throughput complex mix of hardware robustness denormalisation non-frozen data model, out of phase

development of other systems long or raw columns (in a network

environment) concurrency problems distributed data interface with external systems (database /

non-database) expiration dates


of 27


handling of NLS support (for example multi-lingual, multi-currency systems, different date or number notations)


of 27