A unique identifier (ID) is a value that distinguishes one stored entity from every other entity of the same kind. An author ID identifies one author; an article ID identifies one article. This makes an ID a strong candidate for a primary key, because a primary key must uniquely identify a row. section 3.3.2 section 4.1.2
Choose an identifier with the query in mind. A title may be reused, while combining a date, author, and title can make a cumbersome identifier. A single ID is easier for a web server or URL to carry when reading one article, and it can also encode useful information. section 3.3.2
In a query-first schema, IDs commonly serve two related jobs:
- Identify the base entity: the articles and authors tables use id as both primary key and partition key. section 3.3.2
- Preserve identity in denormalized tables: summary tables pair article ID with author, date, or score, whose value becomes the partition key for the corresponding read query. section 3.3.2
- Provide fields needed across stored representations, including article ID, article summary ID, and author ID. section 4.1.2
Table 3.3 Each table has a primary key from which the partition key is extracted.
|
Table
|
Unordered primary key
|
|---|---|
articles
|
id
|
authors
|
id
|
article_summaries_by_author
|
id,
author_id
|
article_summaries_by_date
|
id,
date
|
article_summaries_by_score
|
id,
score
|
Compare the unordered primary keys: an entity ID can remain part of a row’s identity even when another field is needed to organize a summary table for a query. section 3.3.2
ScyllaDB supports generating unique identifiers, but generated IDs have trade-offs. Its common native scheme is the universally unique identifier (UUID): a seemingly random hexadecimal value that can provide uniqueness and ordering while obscuring human-readable meaning. IDs are one choice among the data types used to represent fields in a schema. section 3.3.2 section 4.4 section 4.0
