Data Modeling
19 items tagged with "data-modeling"
Anti-Patterns11
God Table
A single table accumulating dozens or hundreds of unrelated columns for many concepts, becoming a contention and maintenance bottleneck.
Entity-Attribute-Value (EAV) Abuse
Storing arbitrary attributes as rows of name/value pairs to fake a schemaless model, sacrificing type safety, integrity, and query performance.
One True Lookup Table (OTLT)
Cramming every reference list into one generic lookup table keyed by category, defeating foreign keys and mixing unrelated domains.
Premature Denormalization
Duplicating data across tables for speed before any measured need, creating update anomalies and integrity bugs to solve a problem you may not have.
Over-Normalization
Splitting data into so many tables that every read requires a sprawl of joins, hurting performance and readability with no integrity benefit.
Storing Everything as JSON Blobs
Dumping structured data into opaque JSON text columns by default, abandoning constraints, indexing, and queryability the database would provide.
Natural Primary Keys Misuse
Using mutable, business-meaningful values like email or SSN as primary keys, so a real-world change cascades breakage through every referencing row.
Soft Delete Everywhere
Adding an is_deleted flag to every table by default, so all queries must filter it, integrity decays, and tables bloat with dead rows.
Timestamp Without Time Zone
Storing instants in local time without zone information, so values are ambiguous across regions and DST shifts, corrupting ordering and reporting.
Floating-Point for Money
Storing monetary amounts in binary floating point, so rounding errors accumulate and totals fail to reconcile to the cent.
Schemaless Sprawl
Treating a schemaless store as license to skip data design, so documents drift into inconsistent shapes that no consumer can reliably read.
Tutorials3
How to design an effective MongoDB document schema
Model data for MongoDB by choosing embedding vs referencing based on access patterns, then index and validate the result.
How to build transformation models and tests with dbt
Structure dbt staging and mart models, add tests and documentation, and run a build that materializes them in a warehouse.
How to design Elasticsearch index mappings and analyzers
Define explicit Elasticsearch mappings, configure analyzers for text search, and index documents for fast, accurate queries.
FAQs4
What is the difference between SQL and NoSQL databases?
SQL (relational) databases store data in tables with fixed schemas and use SQL for queries, offering strong consistency and powerful joins; examples i...
What is the difference between normalization and denormalization?
Normalization organizes data to eliminate redundancy by splitting it into related tables, which reduces anomalies and keeps a single source of truth f...
What is the difference between a primary key and a foreign key?
A primary key uniquely identifies each row in a table and cannot be null or duplicated; it is the column (or set of columns) the database uses to addr...
What is data modeling?
Data modeling is the practice of defining how data is structured, related, and stored to meet an application's or analytics needs. It usually progress...