Data modelling and choosing a database

Data modelling in an interview means three things: the main entities and their key fields, the access patterns (the queries and writes, with their frequency), and the store plus primary key, partition key and indexes that serve those patterns.

Access patterns come first. 'Get the last 50 messages of a chat, newest first' at 200,000 reads per second leads to a very different model than 'find all orders for a merchant between two dates with filters'. The first fits a wide-column store partitioned by chat_id; the second fits a relational database with a composite index.

Most real systems use more than one store: a relational database for the source of truth (orders, payments, users), a cache for hot reads, object storage for blobs, a search index for text search and maybe a wide-column or key-value store for very high write volume. The skill is putting each piece of data in the right place and keeping them in sync.

1. 1List 3 to 6 core entities with key fields: User(id, phone, name), Order(id, user_id, restaurant_id, status, amount, created_at). Mark the IDs and foreign keys.
2. 2Write the access patterns as queries with rough rates: 'get order by id: 5k/s', 'list a user's orders newest first: 2k/s', 'update order status: 1k/s'. These decide everything else.
3. 3Default to a relational database (Postgres or MySQL) when you need transactions, constraints (unique seat per show), joins or flexible queries, and the write rate fits one primary or a few shards.
4. 4Choose a wide-column or key-value store (Cassandra, ScyllaDB, DynamoDB) when writes are very high, queries are known and key-based, and you can model one table per query.
5. 5Choose specialised stores for specialised queries: Elasticsearch or OpenSearch for full-text search, Redis for counters and leaderboards, a time-series or OLAP store for metrics and analytics, a vector index (pgvector or a vector database) for similarity search, object storage for files.
6. 6Pick the partition key so load and data spread evenly and each query hits one partition: chat_id for messages, user_id for a user's orders. Avoid keys like date or country that concentrate traffic.
7. 7Pick the sort or clustering key to match the read order: (chat_id, message_id DESC) gives newest messages first with no sorting at read time.
8. 8Add secondary indexes only for real queries, and remember each one slows writes. In NoSQL, prefer a second denormalised table written alongside the first.
9. 9Explain how derived copies stay in sync: change data capture or an outbox to update the search index and cache, and what staleness is acceptable.

-- Cassandra (CQL): one table per access pattern
CREATE TABLE messages_by_chat (
  chat_id     bigint,
  bucket      int,        -- e.g. yyyymm, caps partition size
  message_id  bigint,     -- time-sortable (Snowflake-style)
  sender_id   bigint,
  body        text,
  PRIMARY KEY ((chat_id, bucket), message_id)
) WITH CLUSTERING ORDER BY (message_id DESC);

-- Latest 50 messages: a single-partition range read
SELECT * FROM messages_by_chat
 WHERE chat_id = ? AND bucket = ? LIMIT 50;