Data schemas

A database schema is a logical structure that represents the layout of the database. It includes its tables, fields, relationships, constraints, and other attributes. It defines how the data is organized and the rules governing its storage and retrieval.

Analytics uses a Start type of data schema.

Star schemas

A Star Schema is a type of data warehouse schema that consists of one or more fact tables referencing any number of dimension tables. It's name is derived from its structure that resembles a star, with the fact table at the center and the dimension tables surrounding it like the points of a star.

A Star Schema includes:

  • Fact Table: This is the central table in the schema. It typically contains quantitative data or measures (e.g., sales amount, quantity sold, etc.). Fact tables usually have a composite primary key made up of foreign keys referencing the dimension tables. Each row in the fact table represents a specific event or transaction.

  • Dimension Tables: These are the tables that surround the fact table. Dimension tables contain descriptive attributes that provide context to the measures in the fact table. For example, in a sales data warehouse, dimension tables might include tables for time (e.g., date, month, year), products (e.g., product ID, product category), customers (e.g., customer ID, customer demographics), etc. Dimension tables are typically denormalized to improve query performance.

The Star Schema is simple, ease of understanding, and efficient querying performance. It allows for straightforward querying and analysis of data by joining the fact table with one or more dimension tables. Additionally, the structure of the Star Schema lends itself well to online analytical processing (OLAP) applications and data visualization tools, making it a popular choice for data warehousing implementations.

Differences between a data schema and data dictionary

A data schema and a data dictionary are both needed for data management, but they serve different purposes and are related as follows:

Purpose Data Schema Data Dictionary
Definition

A data schema defines the structure and organization of data within a database or data warehouse. It specifies the tables, columns, relationships, constraints, and other attributes that govern how data is stored and accessed.

A data dictionary provides detailed documentation about the data elements, attributes, and structures defined in the data schema. It serves as a reference guide that describes the meaning, purpose, usage, and characteristics of each data element.

Relationships

The data schema defines the logical and physical structure of the database or data warehouse. It specifies how data is organized into tables, the relationships between tables, and the rules governing data integrity and consistency.

The data dictionary complements the data schema by providing additional context and documentation about the elements defined in the schema. It helps users understand the structure, meaning, and usage of the data elements, facilitating data management, analysis, and interpretation.
Interdependence

The data schema benefits from the documentation provided by the data dictionary, which helps users understand and navigate the data structures more effectively. The data dictionary serves as a valuable resource for developers, analysts, and other stakeholders working with the data schema.

A data dictionary relies on the data schema to identify the data elements and structures that need to be documented. It references the tables, columns, keys, indexes, and other components defined in the schema.

A data schema defines the structure and organization of data, a data dictionary provides documentation and context about the elements defined in the schema. Together, they form a comprehensive framework for managing and understanding data within a database or data warehouse.