Skip to content
Search

Database

lastmod 2026-07-20created 2014-01-1189 edits

Subfields of database

A database is a collection of shared data that stores the integrated information of multiple application systems so that it can be operated on.

Conceptually, a database is a set of information managed in an integrated way so that many people can share and use it. It is a collection of one or more logically related pieces of data whose contents are highly structured to make searching and updating efficient. In other words, it is an aggregate of data in which several data files are systematically integrated to eliminate duplicate data items, and the data is structured and stored.

Among database technologies, besides traditional RDBMS technology, new technologies have recently been emerging, such as NoSQL technology, Redis (which backs up from memory to disk), membase (which supports memory caching), and graph databases.

Category

Tools

  • TermDBMS - TUI for querying/editing DBs
  • gobang - Cross-platform TUI DB management tool
  • Visidata - Working with spreadsheet data in the terminal
  • Debezium - Open source for streaming DB changes
  • Slashbase - Open-source web IDE for collaborative DB development
  • Beekeeper Studio - Open-source SQL editor and DB manager
  • DbGate - Open-source cross-platform (no)SQL DB client
  • DBeaver - DB management tool based on Eclipse
  • tbls - CI-friendly DB documentation tool
  • DrawDB - Open-source online database diagram editor
  • WhoDB - Lightweight yet powerful database management tool
  • Liam - Open-source tool that automatically generates ERDs from a DB
  • sqlit - TUI-based SQL client for working with DBs quickly and intuitively in the terminal
  • Tabularis - Cross-platform DB management application for developers
  • StackRender - Database schema design and generation tool
  • DrawDB - Open-source online database diagram editor

in Memory

Programming

Korean: 데이터 레이크

Configuration management

  • Flyway
  • Bytebase - Open-source web-based DB schema change and version control tool

ETC

  • SpaceTimeDB - Combines the DB and the server into one
  • Nao - Cursor for data
  • Oxy - Open-source framework for building SQL agents and data automation
  • UnisonDB - Log-based real-time database
  • datahike - Versioned, high-performance, distributed Datalog engine
  • EVA (Entity-Attribute-Value)

Projects

List of database projects.

  • DuckDB: Embeddable OLAP DB
  • EdgeDB: NoSQL + RDB
  • FoundationDB: Apple's NoSQL DB
  • HarperDB: Structured + unstructured data in a single DB
  • KeyDB: Fast Redis-compatible KeyValue DB
  • M3DB: Uber's distributed time series DB for storing metrics
  • RediSQL: Query Redis KeyValue data with SQL
  • RQLite: SQLite as a multi-node distributed DB
  • UmbraDB: In-memory analytical DB that can replace Postgres
  • TileDB: Multidimensional data analysis
  • Materialize: Real-time SQL streaming DB
  • Prisma: Modern DB client
  • Tremor: Event processing system for unstructured data
  • Debezium: Distributed platform for streaming DB changes (Change Data Capture)
  • QuestDB: Fast DB optimized for time series data
  • TimescaleDB: Time series DB
  • InfluxDB: Time series DB
  • NoisePage: Open-source RDBMS that operates and optimizes itself automatically
  • HStreamDB: Open-source real-time streaming database for IoT
  • Grafana Mimir - Open-source ultra-fast time series DB

Offline client DBs

Cloud Database Cheat Sheet

Summarizes the DBs of AWS, Azure, Google, and open-source/third-party vendors in a single table by type

  • Structured
    • Relational : RDS, SQL DB, Cloud SQL, Oracle/PostgreSQL/MySQL/SQL Server
    • Columnar : Redshift, Synapse Analytics, BigQuery, Snowflake/ClickHouse
  • Semi Structured
    • Key Value : DynamoDB, Cosmos DB, BigTable , Redis/Scylla
    • In-Memory : ElastiCache, Azure Cache for Redis, MemoryStore, Redis/Memcached
    • Wide Column : Keyspaces, Cosmos DB, BigTable, Cassandra/Scylla
    • Time Series : Timestream, Time Series Insights, Bigtable/BigQuery, Influx, OpenTSDB
    • Immutable Ledger : Quantum Ledger DB, Confidential Ledger, CloudSpanner, HyperLedger Fabric
    • Geospatial : Keyspaces, Cosmos DB, BigTable, PostGIS/geomesa
    • Graph : Neptune, Cosmos DB, CloudSpanner, OrientDB/Dgraph
    • Document : DocumentDB, Cosmos DB, Firestore, MongoDB/Couchbase
    • Text Search : OpenSearch, Cognitive Search, CloudSearch, Elastic Search/Elassandra
  • Unstructured
    • Blob : S3, Blob Storage, Cloud Storage, Ceph/OpenIO

Terminology

A summary of frequently confused terms such as Entity, Table, Tuple, and Column.

File system

Database modeling

Relational database

File

Entity

Table

Record

Tuple

Row

Key

Identifier (unique value)

Primary key (unique)

Field

Attribute

Column

Cardinality
When duplication is ‘low’, cardinality is said to be ‘high’.
When duplication is ‘high’, cardinality is said to be ‘low’.
Cardinality is a metric indicating the degree of duplication of a particular column across all rows.
Shard, Sharding
In databases, a shard refers to a method of splitting a large database into several smaller pieces for storage.

Isolation level

Data modeling

Data modeling is the task of building a logical data model from given concepts, and it generally includes reducing this to a physical database model and reflecting it in the database of a specific information system according to the customer's requirements. In the latter sense, it is often also called database modeling.

Database model

A database model is the structure or format of a database described in a formal language supported by a database management system. In other words, a database model is an application of a data model used in conjunction with a database management system.

The underlying schema is generally stored in a data dictionary. Although this schema is defined in a textual query language, the term is also used to refer to a graphical depiction of the database structure.

Indexing

  • Single Field Indexes : The most basic index type
  • Compound Indexes : Composite indexes, widely used in RDBMSs
  • Multikey Indexes : Multikey indexes that add an entry to the index if even one value in an array matches
  • Geospatial Indexes and Queries : Location-based indexes and queries
  • Text Indexes : Indexing is possible on string content too
  • Hashed Index : Hash-type indexes can also be used instead of BTree indexes

Primary Key recommendations (Integer vs UUID)

The Supabase official documentation explains that using either a UUID type or an auto-increment numeric identity column as the primary key is common. However, you should choose depending on the situation:

  • When UUID is a good choice
    • Distributed systems: Suitable for distributed databases that must guarantee uniqueness across different systems
    • Security: Better for security because predictable identifiers are not exposed externally
    • Data merging: Prevents key collisions when merging data from multiple databases
    • Microservice architecture: Each service can generate IDs independently
    • Integration with Supabase Auth: Since Supabase's authentication system uses UUIDs, user-related tables typically reference the UUID of the auth.users table
  • When Integer is a good choice
    • Performance: Better storage efficiency (UUID is 16 bytes, integer is 4 bytes) and indexing performance
    • Simplicity: Easier to read and more convenient to debug
    • Sequentiality: When chronological sorting is needed
    • Single database: When not in a distributed environment

Normalization

A technique for preventing anomalies in a database.

Database normalization mainly has two goals.

  • Eliminate unnecessary data (Data Redundancy). (e.g. remove duplication as much as possible)
  • Store data "logically".

There are three kinds of anomalies here, briefly explained as follows.

Modification Anomaly
Data inconsistency caused by unintended data being updated when updating part of duplicated data
Insertion Anomaly
Data inconsistency caused by unintended data being inserted when inserting new data
Deletion Anomaly
Data inconsistency caused by unintended data also being deleted when deleting data

Tables that are not properly normalized can have various problems on update/insert/delete. Normalization is the attempt to solve these by logically changing the table structure.

The Normalization Rule can be divided into first, second, and third normalization, BCNF, and fourth and fifth normalization; reportedly, in practice it is not common to go as far as fourth or fifth normalization.

1NF

In first normal form, each row must have only one value per column.

For example, a single text field must not contain two or more pieces of data, as in "Banana, Apple".

This is described as the column having an Atomic Value.

2NF

Second normalization can be seen as the real start of normalization.

Second normal form means that every column in the table satisfies full functional dependency.

What this means is that there must be no column that depends on only some of the primary key columns (partial dependency).

3NF

Third normal form means there is no transitive functional dependency among attributes other than the primary key.

Put plainly, no column other than the primary key can determine any other column.

BCNF

BCNF (Boyce and Codd Normal Form) can be seen as a slightly stronger version of third normal form.

It can resolve anomalies that third normal form cannot.

BCNF is a normal form that satisfies third normal form and in which every determinant belongs to the set of candidate keys.

7 databases for 2025

Introduces 7 DBs worth paying attention to for solving a variety of problems. This is not a "best DBs" list but a set of tools that offer new and useful perspectives. In 2025, try investing a week in each of these DBs (7 DBs in 7 Weeks).

Seems to be written around databases, like the title of Bruce Tate's book "7 Languages in 7 Weeks"

Favorite site

Tools

Topic

Guide

  • You might as well timestamp it - Use timestamps instead of booleans in your DB. For example, use published_at rather than is_published, because _at holds information about when it was done. Little loss, big gain.
  • SQLforDevs.com - Next-Level Database Techniques for Developers Ebook - Next-Level Database Techniques for developers [free e-book / 50-page PDF]
    • A database cookbook for developers recording lesser-known modern DB tips
    • 4 sections: Data Manipulation, Querying Data, Schema, Indexes
    • Subscribe to the author's database tips email newsletter "SQL For Devs" to receive the PDF by email

References

  1. Database_design_story_01_12.pdf ↩

  2. Database_design_story_13_28.pdf ↩

  3. SQLite_vs_MySQL_vs_PostgreSQL_-A_Comparison_Of_Relational_Database_Management_Systems-_DigitalOcean.pdf ↩

author Zer0 sitemap