Database + Data Model Basics

Example of an entity and key fields for user

• Entity: User

  • Key fields: userId, name, email, createdAt

Five main database types (very generally speaking)

• Relational Database - store rows in tables with a fixed schema and enforces the relationship between them. Choose one when your data has genuine relationships you query across, or when you need multiple things to change together correctly, such as moving money between accounts.

• NoSQL DB - relax the schema and give up cross-record transactions to scale horizontally more easily. Four general shapes:

  • Key-value store - such as Redis and DynamoDB, for simple lookups by a single key. Fast and easy to partition.

  • Document Store - such as MongoDB, where each record is a self-contained document and records need not look alike

  • Wide-column stores - such as Cassandra and H Base, built for very heavy writes and reading ranges of rows by key

  • Graph database - Such as Neptune, for data whose value is connections, like a social graph

What database would I use when I need to have a flexible schema, like a smartphone needs fields for screen size and battery life, while a book needs an author and page count.

Document DB - A document database stores each product as a self-contained JSON file without needing empty columns for missing data.

What database would I use when I need when I want something for content management

Document DB - To effectively manage content, you must be able to collect and aggregate content from a variety of sources, and then deliver it to the customer. Due to their flexible schema, document databases are perfect for collecting and storing any type of data. You can use them to create and incorporate new types of content, including user-generated content, such as images, comments, and videos.

Imagine you are building a tracking system for a logistics company with a fleet of 100,000 delivery trucks. Every truck has dozens of IoT sensors sending updates every second. This includes GPS coordinates, engine temperature, speed, fuel levels, tire pressure, and cabin temperature. What database would you choose?

Wide-column database - You would use a wide-column database when you need to handle massive amounts of data (petabyte-scale) with exceptionally high write speeds, a flexible schema, and predictable queries.

other examples of when to use wide-column databases

• IoT Data: Wide column databases are well-suited for Internet of Things (IoT) applications where different devices might generate varied data formats.

• Time-Series Data: They are used for logging, monitoring, or storing time-series data that often require fast, large-scale reads and writes.

• Large-Scale Analytics: Companies often use wide column databases for applications like recommendation systems, user profile storage, and distributed real-time analytics.

Key characteristics of wide-column databases

Column-Family Based Structure:

• Unlike relational databases that have a fixed schema with defined tables and columns, wide column databases store data in column families, which are collections of related columns.

• Each row can have a different number of columns, meaning there’s no requirement for each row to follow a rigid structure.

Flexible Schema:

• Wide column databases allow a flexible schema, which means you can easily add new columns without affecting existing rows.

• This is ideal for applications that require evolving structures or need to accommodate different data formats.

Row and Column Storage:

• Data is organized by rows and columns, but the focus is on storing columns together rather than rows, which makes wide column databases more efficient for read-heavy workloads.

• A column family might look like a table, but each row can have its own unique set of columns, which gives the “wide” nature.

How wide-column databases work

How It Works

• A row in a wide column database has a unique row key.

• Each row is composed of multiple columns grouped into column families.

• The column families serve as containers for the actual columns of data, and each row can store different sets of columns.

In a data model, include:

• Entity (table)

• columns

• Indicate Primary Key (PK)

• ID (UUID)

• (if needed) Indicate Foreign Key (FK)

What is a primary key

A primary key is a column or columns in a database table with values that uniquely identify each row or record.

What is a foreign key?

A foreign key is a set of attributes in a table that refers to the primary key of another table, linking these two tables