A database schema describes the logical dependencies between database objects, such as relations, i.e. tables, or views. It defines how the relationships between attributes and tables are regulated. The relational query language SQL is used for this purpose.

What is a relational Database Schema?

With large amounts of data, stored in databases or the data warehouse quickly becomes confusing and queries are not only complicated but also take a relatively long time. Therefore, intelligent ways are needed to create tables so that memory can be saved and queries can take place more quickly.

In order to exploit the full potential of databases, their structure is described and optimized in the database schema. It also ensures that all data users can independently find out where the required information is located and which query can be used to access it. In addition to the relationships between the entities, such as tables or views, the database processes are also stored in the schema.

What are the Types of Database Schemas?

In the application, one basically distinguishes two different types of database schemas. The difference between these types lies mainly in how practical they are already predisposed to:

1. The logical schema defines the relationship between tables and other entities. It also determines the so-called integrity conditions. There are several integrity conditions that lead to consistent data. For example, one specifies that no column in the table should contain null values.
2. The physical schema, on the other hand, is more concerned with the practical storage of the data and describes with which indices the data should be physically stored on the storage medium.

The database schema is a design of a database that describes the logical and physical structure of the data. It defines how the data is organized and how the relationships between the various elements are established. The schema helps ensure that data is stored, organized, and retrieved in a consistent manner. It also provides a clear understanding of how the data is used in an organization.

What are the elements of a database schema?

The main elements of a database schema are:

Tables: They are the most important objects in a database schema and contain the actual data in rows and columns. Tables represent the entity or concept to which the data refer, and each column represents an attribute of that entity.

• Columns: These are the fields that make up a table, and they define the type of data that can be stored in each column. Each column is assigned a name and a data type, such as text, numeric, or date.
• Primary keys: These are unique identifiers used to ensure the uniqueness of each record in a table. Primary keys can be single columns or combinations of columns and are used as a basis for creating relationships between tables.
• Foreign keys: They are used to create relationships between tables and ensure referential integrity. A foreign key is a column or combination of columns in one table that references the primary key of another table.
• Indexes: These are used to improve the performance of database queries by creating a data structure that allows quick lookups of specific values in a table. Indexes can be created for one or more columns in a table.
• Constraints: These are rules enforced by the database to ensure data integrity. Constraints can be used to enforce rules such as data type, uniqueness, and referential integrity.
• Views: These are virtual tables created by combining data from one or more tables in a database. Views are used to simplify complex queries and provide users with a simplified view of the data.

Overall, a database schema is a blueprint for organizing data in a database and includes the structure of the tables, the relationships between them, and the rules that govern the

What is the difference between a Database Schema and a Database Instance?

The terms database instance and the database schema are often used as synonyms, but they describe different properties. The database instance is a copy of a database at a certain point in time, which itself also contains data. Thus, there can be different database instances at different points in time.

The database schema, on the other hand, is rather static and is very difficult to change again once it has been put into operation. Changing existing tables in which information has already been stored is very difficult to implement since the corresponding pipelines that populate the database must also be changed.

What are the Database Schemas used in practice?

In practice, two relational database schemas, in particular, have become established, which are used depending on the specific application.

What is the Star Scheme?

The first approach to this is the star schema, which includes star-shaped table structures. A distinction is made between facts and dimensions:

• The facts are key figures or measured values that are to be analyzed or illustrated. They form the center of the analysis and are located in the central fact table. In addition to the key figures, this also consists of the keys that refer to the surrounding dimensions. In the business environment, facts are, for example, the sales quantity, the turnover, or the incoming orders.
• The dimensions, on the other hand, are the properties of the facts and can be used to visualize the key figures. The different levels of detail of the dimensions are then stored in them and thus storage space can be saved since the details only have to be stored once in the dimension table. Dimensions in the corporate environment are, for example, customer information, the date of the order or product information.

The star schema deliberately omits normalization, which is normally an important concept in database theory. The third normal form is namely violated with a star schema. On the other hand, the structure is particularly efficient and provides fast answers even for complex queries.

What is the Snowflake scheme?

The so-called snowflake scheme is a further expansion stage of the star scheme with the goal of completely normalizing the tables and thereby circumventing the disadvantages of the star scheme to a certain extent. The structure of snowflake results, in short, from the fact that the dimension tables are broken down and classified even further. The fact table, however, remains unchanged.

In our example, this could lead to the dimension table with the delivery addresses being further classified into country, state, and city. This normalizes the tables and the third normal form is also fulfilled. However, this is at the expense of further branches. These are particularly disadvantageous in the case of a later query since these must be reassembled with complex joins.

The further branching thus leads to the fact that the data is stored less redundantly and thus the amount of data is reduced again in comparison to the star schema. However, this is at the expense of performance, since the dimension tables have to be merged again during the query, which is often very time-consuming.

Star Scheme vs. Snowflake Scheme

The Star schema and the Snowflake schema are relatively similar in structure and are often compared with each other for this reason. In fact, the choice of a suitable database schema depends mainly on the concrete application.

In short, the goal of the star schema is to provide a good basis for frequent queries and still reduce the amount of data. This is created by splitting into fact and dimension tables. This allows many redundancies to be removed and the first two normal forms to be satisfied. The number of tables remains relatively small and thus queries with few joins and fast response times are possible. However, complete normalization of the database cannot be performed and some redundancies remain.

The snowflake schema, on the other hand, is a further development of the star schema with the aim of bringing about a normalization of the database. The fact table is retained and the dimension tables are further classified and divided into additional relations. Although this eliminates the remaining redundancies of the star schema, it makes queries slower and more time-consuming, since the dimension tables must first be merged again.

What are best practices for creating a database schema?

Designing a database schema is an important task that can have a significant impact on the performance, usability, and scalability of an application. Below are some best practices to consider when creating a database schema:

• Keep it simple: One of the most important principles when creating database schemas is to keep them simple. Simplicity helps make the schema easy to understand, maintain, and change. Complex schema designs can lead to problems, such as slower performance and data inconsistencies.
• Use descriptive names: Naming conventions play a critical role in the understandability of the database schema. Therefore, make sure you use descriptive names that accurately reflect the purpose of the tables and columns.
• Choose the right data types: Choose the right data types for each column to optimize storage and performance. For example, using the integer data type for columns that store large numbers can improve storage efficiency and reduce processing time.
• Normalize the data: Normalization is the process of organizing the database schema to minimize data redundancy and ensure data consistency. It can help avoid problems such as data inconsistencies and update anomalies.
• Optimize query performance: Design the database schema with query performance in mind. Use indexing to speed up data retrieval, and avoid using wildcard searches or queries that return a large number of rows.
• Use constraints: Constraints help ensure data integrity and can prevent data inconsistencies. For example, defining a foreign key constraint can ensure that data in one table is always linked to data in another table.
• Document the schema: Finally, it is important to document the database schema so that it is easy for others to understand and maintain. Documentation of the schema should include a detailed description of each table, column, and relationship, as well as any assumptions and constraints.

By following these best practices, you can create a database schema that is easy to understand and maintain, and whose performance can be optimized.

This is what you should take with you

• A database schema is the logical description of the relationships between different database objects, such as tables or views.
• A distinction is made between the logical and the physical database schema.
• In practice, database schemas are mainly used in connection with relational databases. The star schema or snowflake schema is the most commonly used.

Other Articles on the Topic of Database Schemas

A detailed article about the database schema can also be found at Lucidchart. This served as a basis and source for this article.