Database
Types of Database
1. Relational Databases (e.g., MySQL, Oracle, SQL Server): – Uses structured tables to store data. – Offers data integrity and complex querying capabilities. – Known for ACID compliance, ensuring reliable transactions. – Includes features like foreign keys and security control, making them ideal for applications needing consistent data relationships.
2. Document Databases (e.g., CouchDB, MongoDB): – Stores data as JSON documents, providing flexible schemas that can adapt to varying structures. – Popular for semi-structured or unstructured data. – Commonly used in content management and automated sharding for scalability.
3. In-Memory Databases (e.g., Apache Geode, Hazelcast): – Focuses on real-time data processing with low-latency and high-speed transactions. – Frequently used in scenarios like gaming applications and high-frequency trading where speed is critical.
4. Graph Databases (e.g., Neo4j, OrientDB): – Best for handling complex relationships and networks, such as social networks or knowledge graphs. – Features like pattern recognition and traversal make them suitable for analyzing connected data structures.
5. Time-Series Databases (e.g., Timescale, InfluxDB): – Optimized for temporal data, IoT data, and fast retrieval. – Ideal for applications requiring data compression and trend analysis over time, such as monitoring logs.
6. Spatial Databases (e.g., PostGIS, Oracle, Amazon Aurora): – Specializes in geographic data and location-based queries. – Commonly used for applications involving maps, GIS, and geospatial data analysis, including earth sciences.
ACID
Database Sharding
Database Schemas
What Is a Database Schema?
Simply put, a database schema is a formal description of the structure or organization of a particular database (DB). The term database schema is most commonly used for relational databases, which organize information in tables and use the SQL query language. Non-relational (or “NoSQL”) databases come in several different formats and don't have a “schema” in the same way that relational databases do (although they do have an underlying structure).
Related Reading: SQL vs. NoSQL: 5 Critical Differences
There are two fundamental components of any database schema:
- Physical database schema: The physical database schema describes how you physically store data in a storage system and the form of storage used (files, key-value pairs, indices, etc.).
- Logical database schema: The logical database schema describes the logical constraints applied to data and defines fields, tables, relations, views, integrity constraints, etc. These requirements provide useful information for programmers to apply to the physical design of a database. The rules or constraints defined in this logical model determine how data in different tables relate to one another.
The definition of physical tables in the schema comes from the logical data model. Entities become tables, entity attributes become table fields, etc.
6 Types of Database Schemas
Learn more about the six most common database schema types below:
- Flat model: A flat model database schema organizes data in a single, two-dimensional display—think of a Microsoft Excel spreadsheet or a CSV file. This schema is best for simple tables and databases without complex relationships between different entities.
- Hierarchical model: Database schemas in a hierarchical model have a “tree-like” structure, with child nodes branching out from a root data node. This schema is ideal for storing nested data—for example, family trees or biological taxonomies.
- Network model: The network model, like the hierarchical model, treats data as nodes connected to one other; however, it allows for more complex connections, such as many-to-many relationships and cycles. This schema can model the movement of goods and materials between locations or the workflows required to accomplish a particular task.
- Relational model: As discussed above, this model organizes data in a series of tables, rows, and columns, creating relationships between different entities. The next section and the rest of this guide will focus on the relational model.
- Star schema: The star schema is an evolution of the relational model that organizes data into facts and dimensions. Fact data is numerical (for example, the number of sales of a product), while dimensional data is descriptive (for example, a product’s price, color, weight, etc.).
- Snowflake schema: The snowflake schema is a further abstraction on top of the star schema. It contains a fact table that connects to a dimensional table, expanding the descriptiveness possible within a database. As you might have guessed, the snowflake schema gets its name from the intricate patterns of a snowflake, where smaller structures radiate off of the central arms of the flake.
