6 Database Models

A database model defines how data is arranged before indexes, query plans, and storage engines do their work. It shapes which relationships are natural, which updates are cheap, and where application complexity ends up. These six models solve different classes of problems rather than competing for one universal winner.

1. Flat model

The flat model stores records in a single table or file, with one row per item and one column per attribute. CSV files, spreadsheets, and many simple exports use this shape because it is easy to inspect and exchange.

Its weakness is duplicated data and weak relationship handling. If the same customer appears in many rows, every correction has to be repeated. Flat models are fine for small reference sets, imports, and intermediate pipeline output. They are a poor fit for multi-user systems with frequent updates.

2. Hierarchical model

A hierarchical model organises records as a tree. Each child has one parent, so traversal is predictable: start at the root and walk down a known path. This works well when the domain is genuinely tree shaped, such as file system style catalogues or strict organisational structures.

The model becomes awkward when reality is not a tree. Shared children, cross-links, and many-to-many relationships force duplication or side channels. Hierarchical designs are efficient when the path is known in advance, but rigid when the structure evolves.

3. Relational model

The relational model stores data in tables linked by keys. Primary keys identify rows, foreign keys express relationships, and SQL lets the system join, filter, group, and update that data declaratively.

Its real strength is the combination of flexible querying, integrity constraints, and transactions. A relational system can enforce rules such as "an order must reference a real customer" while still supporting complex queries. The tradeoff is schema discipline. Poor indexing, careless joins, or unnecessary normalisation can make the model expensive to operate.

4. Network model

The network model allows a record to have multiple parents, creating a graph rather than a strict tree. It was designed for domains where relationships are central and a record may belong to several connected sets at once.

This handles many-to-many links more naturally than a hierarchical model, but older network systems often tied applications tightly to pointer-based navigation. That could be fast, but it made change hard. Modern graph databases inherit some of the same motivation while offering better query languages and looser coupling.

5. Star schema

A star schema is an analytical model used in data warehouses. It places a fact table in the centre and surrounds it with denormalised dimension tables such as date, product, region, or customer segment.

This is tuned for read-heavy aggregation. Analysts can sum revenue or count events with few joins, and columnar engines can scan the fact table efficiently. The cost is redundancy inside dimensions and weak support for transactional workflows. A star schema is excellent for reporting and a bad choice for operational order processing.

6. Snowflake model

The snowflake model normalises the dimensions of a star schema into additional related tables. A product dimension might split brand, category, and supplier into separate structures to reduce duplication and improve governance.

That extra structure lowers redundancy but increases join cost and makes the warehouse harder to understand. Snowflake designs make sense when dimension data is large, shared, or updated from several sources. If the workload is mainly dashboard queries over stable dimensions, a plain star schema is usually simpler.

The practical lesson is straightforward: choose the model that matches your relationship shape, consistency needs, and query pattern. Most data problems start to look clearer once the model matches the questions the system actually needs to answer.