Denormalized data stores copies of related values in more than one table so each copy can serve a different access pattern. In ScyllaDB, schema design starts with required queries: data must be stored with the partition key needed for the query, rather than joined from separate tables at read time. section 3.2.1 section 2.2.2
Use denormalization when the same information must be read by different attributes. A table partitioned by author supports loading an author’s articles, but it cannot efficiently load top-scoring articles unless the author partition key is included. Storing a separately partitioned copy avoids scanning every partition across the cluster. section 3.2.1 section 3.4
Figure 3.6 Schema design balances between normalization and denormalization.
Schema design balances the convenience of normalized relationships against copies shaped for fast, direct queries. section 3.2.1
A practical denormalized design follows these steps:
- List the reads and writes the application requires; the nouns targeted by those queries often become tables. section 3.2.2 section 3.4
- Create a table, and therefore a partition key, for each distinct way data must be read. section 3.2.2
- Keep duplicated rows synchronized when writing, because each denormalized companion table needs its own insert. section 3.2.1
- Review read behavior after modeling: very small partitions can require many queries when a page needs data from multiple partitions. section 5.1.2
Article summaries by author, date, and score
A restaurant-review application keeps miniaturized article copies in three tables: article_summaries_by_author, article_summaries_by_date, and article_summaries_by_score. They share an article ID, title, author name, score, and image, but use different partition keys. The date-based table powers recent articles on the home page; the author- and score-based tables support their corresponding views. Including an author name in a summary can also avoid an additional author lookup for every displayed article. section 5.3.2 section 5.1.2
Denormalization trades extra storage and write maintenance for query speed, scalability, and fault tolerance. ScyllaDB can later assist with denormalized models through materialized views and indexes, with trade-offs between query speed, storage, and maintenance effort. section 3.2.1 section 7.0
