Designing

Database Design in SQLite

Database design forms the foundation of any successful SQLite implementation. It’s the process of organizing data efficiently and logically, ensuring your application can store and retrieve information effectively. Good design principles apply whether you’re building a small personal project or a large-scale application.

Benefits of Proper Database Design

A well-designed SQLite database offers several key advantages:

Performance Optimization

• Faster query execution through proper indexing and normalization
• Reduced storage requirements by eliminating redundant data
• Efficient data retrieval patterns

Data Integrity

• Consistent and accurate data storage
• Prevention of data anomalies
• Enforcement of business rules through constraints

Maintainability

• Easier updates and modifications
• Simplified debugging and troubleshooting
• Reduced technical debt

Fundamental Database Components

Tables
Tables are the primary storage structures in SQLite, representing distinct entities in your system. Each table should focus on storing information about a specific type of object or concept.

CREATE TABLE users (
    user_id INTEGER PRIMARY KEY,
    username TEXT NOT NULL,
    email TEXT UNIQUE,
    created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP
);

Columns and Data Types
Columns define the attributes of your entities, each with a specific data type that determines what kind of information it can store.

CREATE TABLE products (
    product_id INTEGER PRIMARY KEY,
    name TEXT NOT NULL,
    price DECIMAL(10,2),
    description TEXT,
    in_stock BOOLEAN DEFAULT TRUE
);

Database Design Process

1. Requirements Analysis
Begin by documenting all data requirements:

• What information needs to be stored?
• How will the data be used?
• Who will access the data?

2. Entity Identification
Identify the main objects or concepts in your system:

-- Example entities for a bookstore
CREATE TABLE books (
    book_id INTEGER PRIMARY KEY,
    title TEXT NOT NULL,
    isbn TEXT UNIQUE
);

CREATE TABLE authors (
    author_id INTEGER PRIMARY KEY,
    name TEXT NOT NULL
);

3. Relationship Mapping
Establish connections between entities:

CREATE TABLE book_authors (
    book_id INTEGER,
    author_id INTEGER,
    FOREIGN KEY (book_id) REFERENCES books(book_id),
    FOREIGN KEY (author_id) REFERENCES authors(author_id),
    PRIMARY KEY (book_id, author_id)
);

Common Design Patterns and Best Practices

Normalization
Organize data to minimize redundancy:

-- Bad Design (Unnormalized)
CREATE TABLE orders (
    order_id INTEGER PRIMARY KEY,
    customer_name TEXT,
    customer_email TEXT,
    customer_address TEXT
);

-- Good Design (Normalized)
CREATE TABLE customers (
    customer_id INTEGER PRIMARY KEY,
    name TEXT,
    email TEXT,
    address TEXT
);

CREATE TABLE orders (
    order_id INTEGER PRIMARY KEY,
    customer_id INTEGER,
    FOREIGN KEY (customer_id) REFERENCES customers(customer_id)
);

Indexing Strategy
Implement appropriate indexes for performance:

-- Create indexes for frequently queried columns
CREATE INDEX idx_users_email ON users(email);
CREATE INDEX idx_products_name ON products(name);

SQLite-Specific Considerations

Type Affinity
SQLite uses dynamic typing with type affinity:

CREATE TABLE examples (
    id INTEGER PRIMARY KEY,
    flexible_column TEXT    -- Can store various data types
);

Constraints and Triggers
Implement business rules using constraints:

CREATE TABLE inventory (
    product_id INTEGER PRIMARY KEY,
    quantity INTEGER CHECK (quantity >= 0),
    last_updated TIMESTAMP DEFAULT CURRENT_TIMESTAMP
);

CREATE TRIGGER update_timestamp
AFTER UPDATE ON inventory
BEGIN
    UPDATE inventory SET last_updated = CURRENT_TIMESTAMP
    WHERE product_id = NEW.product_id;
END;

Advanced Design Concepts

Versioning and Migration
Plan for database schema evolution:

CREATE TABLE schema_version (
    version INTEGER PRIMARY KEY,
    applied_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP
);

Audit Trails
Track data changes:

CREATE TABLE audit_log (
    log_id INTEGER PRIMARY KEY,
    table_name TEXT,
    record_id INTEGER,
    action TEXT,
    changed_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP
);

Learning Resources and Tools

Database Design Tools

• DB Browser for SQLite
• SQLite Expert Professional
• DBeaver

Design Verification
Regular validation of your design:

• Test with sample data
• Verify query performance
• Check constraint effectiveness
• Evaluate data integrity

Good database design is iterative and should evolve with your application’s needs while maintaining data integrity and performance.

Best Practices for Naming Tables and Columns in SQLite

A clear and consistent naming convention helps ensure that databases remain understandable even when multiple developers are working with them. A few key points include:

Use lowercase or snake_case for all identifiers
Table and column names typically use lowercase letters, with words separated by underscores. For example, customers, order_items, or first_name can be more readable than Customers or FirstName.

Tables often named in plural
Tables usually store multiple records of a particular entity. So “customers” rather than “customer” is common.

Be descriptive, not cryptic
Consistent and descriptive names keep the schema self-explanatory. For example, created_at instead of cr_at.

Use clear suffixes or prefixes for primary keys
If you have a table named orders, you might name its primary key order_id. This ensures that any foreign key referencing it (such as in an order_items table) is also named order_id, reducing confusion when you do joins.

Avoid ambiguous or conflicting names
Choose column names that make the data type or intended purpose obvious. For example, fahrenheit rather than temperature if you’re storing Fahrenheit values.

Example

CREATE TABLE customers (
    customer_id INTEGER PRIMARY KEY,
    first_name  TEXT NOT NULL,
    last_name   TEXT NOT NULL,
    email       TEXT NOT NULL,
    created_at  TEXT NOT NULL
);

In this example, the table name is plural (customers), the primary key is customer_id, and each column name is in lowercase with underscores separating words.

Single Tables vs. Multiple Related Tables

When designing your database schema, you may wonder whether to consolidate data in a single table or distribute it across multiple tables. This choice depends on data relationships, normalization, and performance considerations.

Reasons to use multiple tables

• Logical Separation of Entities: If you have distinctly different data (e.g., customers, orders, products), separate tables keep data organized and reduce redundancy.
• Ease of Maintenance: Updating one table’s scheme will not affect another table’s data and queries.
• Data Integrity: Foreign keys can enforce relationships across tables, ensuring consistent references.

Reasons to use a single table

• Simple or Small Data Model: If you have a small dataset or all your data has nearly identical attributes, a single table can simplify queries.
• Faster with Minimal Joins: A single-table design can avoid join overhead for certain read operations when the data is not deeply structured.

In practice, relational modeling traditionally favors multiple normalized tables to reduce redundant data. Start with separate entities placed in their own tables, then create relationships via foreign keys. If performance testing reveals bottlenecks, denormalizing or merging tables selectively may help.

Example

Single-table approach (not typical for large, varied data):

CREATE TABLE items (
    item_id     INTEGER PRIMARY KEY,
    item_type   TEXT,
    title       TEXT,
    description TEXT,
    created_at  TEXT
);

Here, rows might store completely different item types (like “blog,” “news,” or “event”) using a single table with a type column.

Multi-table approach (more flexible):

CREATE TABLE blog_posts (
    blog_id     INTEGER PRIMARY KEY,
    title       TEXT,
    content     TEXT,
    created_at  TEXT
);

CREATE TABLE events (
    event_id    INTEGER PRIMARY KEY,
    title       TEXT,
    location    TEXT,
    start_time  TEXT,
    end_time    TEXT
);

Each table has columns suited for the specific data type while maintaining clarity and scalability.

Maximum Number of Columns in a SQLite Table

By default, SQLite enforces an upper bound of 2000 columns in a single table. This can be increased up to 32767 at compile time, although in practice, having thousands of columns usually indicates a design issue. Large numbers of columns often cause performance problems and violate basic normalization principles.

If you need more columns beyond that default boundary, you would have to recompile SQLite with a higher SQLITE_MAX_COLUMN value. However, it’s strongly recommended to rethink the schema design before resorting to extremely wide tables.

Example

-- This example demonstrates a table with many columns,
-- but typically you'd keep the number of columns smaller.
CREATE TABLE big_table (
    col1 INTEGER,
    col2 TEXT,
    col3 TEXT,
    ...
    col2000 TEXT
);

In most real scenarios, if you are nearing thousands of columns, it’s better to split into multiple tables with well-defined relationships.

Using Views Instead of Tables

A view is a virtual table defined by a SELECT query. It can simplify data retrieval, hide complexity, and enhance security by masking sensitive columns. Key points include:

Advantages of views

• Security: Restrict direct access to underlying tables and show only the columns or rows you want.
• Simplicity: Present a simpler virtual table when you have complex joins regularly.
• Consistency: Changes in the underlying tables are reflected automatically in the view.

Disadvantages of views

• Performance Overhead: Each time you query a view, the database runs the underlying query. For large or complex calculations, this can slow performance.
• Possible Read-Only: Depending on the database and the complexity of the view, some views may not support direct inserts, updates, or deletes.

Example

-- Suppose you have a 'customers' table and an 'orders' table,
-- and you want a single view displaying customer name and 
-- their total orders.

CREATE TABLE customers (
    customer_id INTEGER PRIMARY KEY,
    full_name   TEXT NOT NULL
);

CREATE TABLE orders (
    order_id    INTEGER PRIMARY KEY,
    customer_id INTEGER,
    amount      REAL,
    FOREIGN KEY(customer_id) REFERENCES customers(customer_id)
);

CREATE VIEW customer_order_summary AS
SELECT c.full_name,
       COUNT(o.order_id) AS total_orders,
       SUM(o.amount) AS total_spent
FROM customers c
LEFT JOIN orders o
ON c.customer_id = o.customer_id
GROUP BY c.customer_id;

You can now do:

SELECT * FROM customer_order_summary;

for easy reporting without building the joins manually each time.

Designing a Schema for Hierarchical Data

Hierarchical data requires special consideration because of parent-child relationships. Common patterns include:

Adjacency List Model
Each row stores a parent_id referencing another row’s primary key. This is straightforward to implement but can require multiple self-joins or recursive queries for deep hierarchies.

CREATE TABLE categories (
    category_id   INTEGER PRIMARY KEY,
    name          TEXT,
    parent_id     INTEGER,
    FOREIGN KEY(parent_id) REFERENCES categories(category_id)
);

Use a recursive WITH clause (common table expression) to query hierarchical structures.

Nested Set Model
Assign each node a left_value and right_value. You can retrieve entire subtrees efficiently with range queries, but inserting or moving nodes involves updating multiple rows.

CREATE TABLE categories (
    category_id INTEGER PRIMARY KEY,
    name        TEXT,
    lft         INTEGER,
    rgt         INTEGER
);

Path Enumeration
Store the full path for each node (e.g., '/electronics/phones/smartphones'). This is easy to query with string matching, but moving or renaming a node requires updating all descendants.

CREATE TABLE categories (
    category_id INTEGER PRIMARY KEY,
    name        TEXT,
    path        TEXT
);

Closure Table (not shown in code here) is another method that explicitly stores all ancestor-descendant pairs, making certain queries simpler. The best model depends on how frequently you update the hierarchy, how deeply nested it is, and what types of queries you perform most often.

Example (Recursive Query Using Adjacency List)

WITH RECURSIVE category_hierarchy AS (
    SELECT category_id, name, parent_id, 0 as level
    FROM categories
    WHERE parent_id IS NULL

    UNION ALL

    SELECT c.category_id, c.name, c.parent_id, ch.level + 1
    FROM categories c
    JOIN category_hierarchy ch
    ON c.parent_id = ch.category_id
)
SELECT * FROM category_hierarchy ORDER BY level;

This query can list all categories at every level of the hierarchy, with a level column indicating the depth.

The normal forms in SQLite and when to use them

Normal forms are guidelines to structure relational databases more efficiently. SQLite supports the same conceptual normal forms as other relational systems. They start with First Normal Form (1NF), which requires atomic (indivisible) values in each column. Second Normal Form (2NF) extends 1NF by eliminating partial dependencies, ensuring every non-key column depends on the entire primary key. Third Normal Form (3NF) further eliminates transitive dependencies, meaning non-key columns should not depend on other non-key columns.

Using normal forms helps avoid anomalies like duplicate data, insertion difficulties, and update or deletion cascading issues. As datasets grow and relationships become more complex, higher levels of normalization can reduce redundancy, minimize inconsistencies, and simplify maintenance. However, more normalization can increase the number of tables and the complexity of joins.

Example
Consider a table of orders where each row has customer details, product details, and order data. In 1NF, each column holds one value (e.g., one product per row). To move to 2NF or 3NF, place customer details in a separate Customers table keyed by a unique identifier, and store product details likewise in a separate Products table. Then reference them in the Orders table using their IDs. This way, changes in a customer’s address only need to be updated in one place.

Identifying and eliminating data redundancy in SQLite

Data redundancy occurs when the same information is stored in multiple places. This can lead to inconsistencies during updates or deletions if one copy changes and another remains outdated. To spot redundancy, look for columns or sets of data that repeat across rows or tables. Evaluate where the repeated data logically belongs and whether it can be moved to a dedicated table.

Eliminating redundancy typically involves moving repetitive data to another table and linking it back with a foreign key. This refactoring keeps each entity stored only once, improving data integrity and simplifying updates.

Example
If an application logs user actions and also includes detailed user profiles in the same log table, the user profile fields repeat for each action. Instead, create a separate Users table with user_id as the key. The log table stores the user_id reference, while all user information is kept in Users. This design removes duplicated user data in each log entry.

Determining when denormalization is beneficial

Denormalization is the intentional introduction of some redundancy to speed up data retrieval or simplify certain query patterns. Although normalization offers consistency and fewer data anomalies, denormalization can be helpful when database reads are more common than writes, or when complex joins are causing performance issues. It can reduce query complexity and improve read performance by storing frequently accessed data or aggregated data in a single table or column.

Still, denormalization must be carefully planned. If you introduce multiple copies of data, you take on the responsibility of keeping them consistent across your database.

Example
Suppose a reporting application needs to retrieve the monthly sales amount for a product many times, but computing it from itemized sales rows is expensive. Storing a denormalized monthly_sales column in your Products or SalesSummary table can speed up returns. You must update it whenever a new order arrives, but the read performance overhead decreases.

Converting an existing database to Third Normal Form (3NF)

To convert an existing database into 3NF, you typically perform a series of decomposition steps:

1. Identify primary keys in each table.
2. Eliminate repeating groups and ensure atomic columns (1NF).
3. Remove partial dependencies (2NF) by creating separate tables where an attribute depends on only part of a composite key.
4. Remove transitive dependencies (3NF) by ensuring non-key fields do not rely on other non-key fields.

As part of this process, you might rename columns, create linking (junction) tables, and drop or merge tables that only contained duplicate data. After these changes, test the schema by running your typical queries and inserts to ensure they still work properly and do not cause logical errors.

Example

1. Start with a single table Orders that contains customer_name, customer_address, product_id, product_name, and so on.
2. Move customer_name and customer_address to a new Customers table keyed by customer_id.
3. Move product_name (and other product data) to a Products table keyed by product_id.
4. Link Orders to Customers and Products via foreign keys.
5. Check for any columns that rely on others indirectly. If you see shipping_cost depends on product_weight in Orders, consider if shipping_cost belongs in a table of shipping rates or must be recomputed.
   This final schema avoids partial or transitive dependencies and is closer to 3NF.

Trade-offs between normalized and denormalized designs

A highly normalized schema avoids redundant data and protects against inconsistencies during changes. It also limits anomalies and can streamline data storage. However, it can require multiple joins to retrieve related information, impacting performance when data grows in size or complexity.

A denormalized strategy keeps some duplicated or aggregated data in the same table, which can reduce the need for joins and speed up select queries. But it makes updates more complicated, since you must carefully synchronize multiple copies of the same data.

Example

Choosing a balance depends on query patterns, update frequency, and performance requirements.

Available data types in SQLite

SQLite uses a dynamic type system that does not strictly enforce specific data types on columns but employs a concept called type affinity. Officially, SQLite distinguishes between five fundamental storage classes: INTEGER, REAL, TEXT, BLOB, and NULL. However, many traditional data type definitions (like VARCHAR or FLOAT) are recognized in SQLite as type affinities rather than rigid rules. This approach makes SQLite flexible because it can store any type of data in any column, but it will try to convert or interpret values based on the column’s assigned affinity.

Detailed explanation:

• INTEGER: Intended for whole numbers. Typically stored using up to 8 bytes.
• REAL: Used for floating-point numbers. Usually stored as an 8-byte IEEE 754 floating-point value.
• TEXT: Any kind of textual data. SQLite stores these as strings using SQLite’s chosen encoding.
• BLOB: A sequence of bytes, stored exactly as given. This is suitable for images, files, or encrypted data.
• NULL: A marker for missing or undefined data.

Example:

-- Creating a table with various column types
CREATE TABLE sample_data (
  id INTEGER PRIMARY KEY,
  username TEXT,
  score REAL,
  profile_picture BLOB
);

Even if you specify certain types (e.g., VARCHAR(50)), SQLite will interpret them under its type affinity rules. This means you can still insert values that might differ from what you would expect with a strict SQL database system.

How SQLite’s type affinity system works

In SQLite, each column is assigned a preferred type category (affinity), and SQLite will automatically attempt to convert a value stored in that column to match its affinity. The affinities are TEXT, NUMERIC, INTEGER, REAL, and BLOB. SQLite uses a set of rules based on the column’s declared type to decide which affinity applies.

Detailed explanation:

1. Column Declaration: When you create a table, SQLite inspects the data type string for keywords (“INT”, “CHAR”, “TEXT”, “BLOB”, “REAL”, “FLOA”, etc.).
2. Affinity Assignment: It then assigns one of the five possible affinities. For example, any declared type containing “INT” will be assigned INTEGER affinity.
3. Value Storage: When you insert or update data in a column, SQLite tries to convert the value to match that affinity, but this conversion may not always happen if the data cannot be sensibly converted (e.g., attempting to convert a non-numeric string to a REAL column will just store the string if it cannot be parsed).
4. Storage Classes: Under the hood, the actual storage class used for each value can be different from the column affinity if that makes more sense for the value being stored.

Example:

-- Creating a table to highlight type affinity
CREATE TABLE affinity_test (
  text_col TEXT,         -- Enforced as TEXT affinity
  int_col INTEGER,       -- Enforced as INTEGER affinity
  float_col REAL,        -- Enforced as REAL affinity
  mixed_col VARCHAR(20)  -- Interpreted as TEXT affinity by SQLite
);

-- Attempt to insert data
INSERT INTO affinity_test (text_col, int_col, float_col, mixed_col)
VALUES ('Hello world', 42, 3.14, '12345');

In the above example, mixed_col declares VARCHAR(20), but SQLite treats it as TEXT. If you insert a numeric value into mixed_col, SQLite won’t convert it into a number unless it’s being used in a numeric context. This behavior can be convenient, but it also requires careful handling if you expect strict data typing.

When to use TEXT vs BLOB for storing binary data

TEXT is typically used for strings (including JSON, XML, or any textual representation), while BLOB is a byte-for-byte storage class used to store data exactly as it is. If you have an actual binary file (e.g., an image or a PDF), BLOB is generally the safer choice. TEXT should be used when the data is inherently textual or if you want SQLite to handle character encoding for you.

Detailed explanation:

• Binary Integrity: BLOB preserves every byte of the underlying data. This is critical if the content cannot be interpreted as valid text.
• Encoding: TEXT data is stored with a specific encoding (UTF-8, UTF-16, etc.), which can introduce transformations if the data is not actually text.
• Searching and Manipulation: If you frequently search or manipulate the data using text-based operations, storing it as TEXT is advantageous. For instance, if you’re storing JSON, you might benefit from text operations.
• Performance and Storage: BLOB can sometimes be more efficient for actual binary data, as there’s no overhead of text encoding.

Example:

-- Table with both text and binary columns
CREATE TABLE files (
  file_id INTEGER PRIMARY KEY,
  file_name TEXT,
  file_contents BLOB
);

-- Inserting an image as a BLOB
INSERT INTO files (file_name, file_contents)
VALUES ('photo.png', X'89504E470D0A1A0A...');

-- Example of storing JSON text (if desired, though BLOB would also work)
CREATE TABLE config_data (
  config_id INTEGER PRIMARY KEY,
  config_json TEXT
);

INSERT INTO config_data (config_json)
VALUES ('{"theme": "dark", "autoSave": true}');

In the above examples, images or raw binary data should be stored in the BLOB column to avoid unwanted data transformations. JSON or other text-based data can be stored in the TEXT column for direct readability and easy manipulation.

Differences between INTEGER and REAL in SQLite

SQLite stores INTEGER as whole numeric values, often in an 8-byte format, whereas REAL stores floating-point numbers using an 8-byte IEEE 754 representation. INTEGER affinity columns are best for counting, IDs, or any data that must remain integral. REAL affinity columns are suitable when you need decimal values or fractional precision.

Detailed explanation:

• INTEGER: Ensures no fractional part. In SQLite, it can represent values up to a maximum 64-bit integer range.
• REAL: Allows fractional values, including decimal and scientific notation. But be mindful of floating-point inaccuracies that can appear due to binary representation.
• Conversion: If you insert a floating-point number into an INTEGER column, the fractional part is typically truncated (not rounded) during conversion.
• Performance: Both INTEGER and REAL generally perform well. However, repeated floating-point arithmetic in a REAL column can introduce rounding errors, so design your schema and queries with that in mind.

Example:

-- Using INTEGER for counts or IDs
CREATE TABLE user_stats (
  user_id INTEGER PRIMARY KEY,
  post_count INTEGER
);

-- Using REAL for fractional values
CREATE TABLE measurements (
  measurement_id INTEGER PRIMARY KEY,
  temperature REAL,    -- might store values like 36.6, 98.24
  humidity REAL        -- might store values like 50.0, 49.7
);

If you know your dataset will never need fractional parts, INTEGER columns keep the data more precise. For columns representing monetary amounts that require specific decimal handling, some developers store the amounts in INTEGER (e.g., in cents) to avoid floating-point rounding issues.

How SQLite stores dates and timestamps

SQLite does not have a native DATE or DATETIME storage class. Instead, it recommends that dates and times be stored in one of three ways: as TEXT, as REAL (Julian day number), or as INTEGER (Unix timestamp). The choice depends on how you prefer to query and manipulate the data.

Detailed explanation:

1. TEXT Format: You can store date/time as a text string such as “YYYY-MM-DD HH:MM:SS”. This approach is human-readable and can be used with SQLite’s built-in date/time functions if properly formatted.
2. REAL (Julian Day): A floating-point number denoting the Julian day, which is the continuous count of days since noon Universal Time on January 1, 4713 BC. This allows for direct computation and intervals.
3. INTEGER (Unix Timestamp): The number of seconds (or milliseconds) since the Unix epoch (January 1, 1970). This format is compact and efficient for calculations but is not as immediately readable as TEXT.

Example:

-- Using TEXT for date/time
CREATE TABLE events_text (
  event_id INTEGER PRIMARY KEY,
  event_time TEXT
);

INSERT INTO events_text (event_time)
VALUES ('2023-09-15 10:30:00');

-- Using INTEGER for date/time (Unix timestamps)
CREATE TABLE events_unix (
  event_id INTEGER PRIMARY KEY,
  event_timestamp INTEGER
);

INSERT INTO events_unix (event_timestamp)
VALUES (strftime('%s','now'));  -- current time as Unix epoch

If you need to run frequent comparisons or date arithmetic, formats like Unix timestamps or Julian days (REAL) might ease your calculations. If you prefer readability and built-in date/time functions, storing dates in the “YYYY-MM-DD …” format works well.

Maximum size limits for different data types

SQLite can store large amounts of data in each field, but there are practical and configurable limits. Generally, SQLite supports up to 1,000 columns in a table by default, and a single row can be up to about 2GB in size if not restricted by memory or other constraints. The maximum size of a TEXT or BLOB field can therefore be quite large, but practical considerations usually dictate more reasonable sizes.

Detailed explanation:

• Maximum Page Size: SQLite uses pages to store data, with a default page size of 4KB. Maximum page size can be increased to 65,536 bytes, which affects overall storage limits.
• STRING or BLOB Fields: By default, each TEXT or BLOB field can hold up to the maximum row size, minus overhead. This can theoretically approach 2GB, but it’s limited by your system’s memory and the maximum size of a database file.
• INTEGER and REAL Limits: An INTEGER is generally up to 8 bytes, so -2^63 to (2^63)-1. REAL also uses 8 bytes in IEEE 754 format.
• Configuring Limits: You can compile SQLite with different limits or change certain PRAGMA settings to adjust maximum values, though the defaults are usually sufficient.

Example:

-- Large text field example
CREATE TABLE large_text_test (
  doc_id INTEGER PRIMARY KEY,
  doc_content TEXT
);

-- Insert a large text (potentially megabytes in size)
INSERT INTO large_text_test (doc_content)
VALUES ('<very long text data here>');

-- Similarly for BLOB
CREATE TABLE large_blob_test (
  blob_id INTEGER PRIMARY KEY,
  blob_content BLOB
);

-- Insert a large binary object
INSERT INTO large_blob_test (blob_content)
VALUES (zeroblob(1000000));  -- Insert 1 million bytes of zeros

In practice, extremely large fields can negatively impact performance. If storing massive binary data in SQLite, consider file-based strategies or carefully manage database design to keep it efficient.