logo for information-management-architect.com

Database Design Document

Improve architecture and design process with David Bowman’s information management guidelines for database design document

This site is designed for information technology professionals and information management consultants who want to learn more about data warehouse and business intelligence architecture.

It provides information management guidelines for database architecture.

What is a Database Design Document?

The objective of an architecture specification is to define the architecture for the entire data warehouse and business intelligence solution including:
  • Database architecture and infrastructure
  • Database sizing and performance expectations
  • Identification of the extract, transform and load process
  • Recommendations for the sizing and configuration of hardware
  • Access control, backup and recovery guidelines.
Architecture Guidelines

Data Architecture
  • Should complete a physical data model for data warehouse storage;
  • Should create the physical data model by translating the logical data model into a physical data model;
  • Should define partitioning strategy;
  • Should include process metadata columns as required;
  • Should include audit columns as required;
  • Should identify and document reference table codes and descriptions;
  • Should provide table definitions;
  • Should provide column definitions;
  • Should include volumetrics;
  • Should document the processes and procedures required to meet data retention requirement including processes to archive all data including landing area, staging area, data warehouse and data marts, access archived data and restore archived data;
  • Should document the processes and procedures required to meet backup and recovery requirements;
  • Should document strategy to recover data in event of a system-wide failure including the approach that will be used including planning, processes and procedures;
  • Should define the approach that will be used to replicate data to meet redundancy requirements;
  • Should define the approach that will be used to provide an alternate method of accessing data accessing data in case of a system failure; and
  • Should specify any partitioning strategies that will be used in the database design to help meet performance requirements and archiving requirements.
Information Delivery Architecture
  • Should complete a dimensional data model for reporting and analytics;
  • Should create facts and dimensions based on the requirements specification;
  • Should add snow-flake dimensions if appropriate;
  • Should add physical attributes such as table-space names and index names;
  • Should define partitioning strategy;
  • Should include process metadata columns as required;
  • Should include audit columns as required;
  • Should identify and document reference table codes and descriptions;
  • Should be the subject of an internal quality assurance review;
  • Should define the strategy for handling ad hoc queries;
  • Should define the specifications for each report that needs to be converted;
  • Should define specifications for all audit reporting; and
  • Should define specifications for all reconciliation reporting.
Summary...

The objective of an architecture specification is to define the architecture for the entire data warehouse and business intelligence solution including:
  • Database architecture and infrastructure
  • Database sizing and performance expectations
  • Identification of the extract, transform and load process
  • Recommendations for the sizing and configuration of hardware
  • Access control, backup and recovery guidelines.
This site provided information management guidelines for database architecture.