Entities, Keys and Relationships

Once you know your requirements, you turn the real-world things in your system (entities) into tables or collections. Every table needs a primary key for identity, plus foreign keys (or logical references) that connect it to other tables. Getting this right early makes the rest of the design (APIs, indexes, sharding) fall into place.

Entities and keys#

A social media example:

Choosing primary keys: prefer system-generated IDs (auto-increment, UUIDs, or Snowflake-style IDs) over business data like an email address, which can change. In a distributed system, UUIDs or Snowflake IDs let many servers generate unique IDs without a central bottleneck.

The three relationship types#

Tables connect to each other in one of three ways.

Constraints: enforcing correctness#

Constraints stop bad data from getting into the database:

• NOT NULL: a required field like posts.user_id can't be empty.
• UNIQUE: no duplicate emails or usernames.
• CHECK: values must make sense, e.g. price > 0.
• Foreign keys (referential integrity): no orphaned rows, such as a comment pointing to a post_id that doesn't exist.

CREATE TABLE comments (
  id         BIGINT PRIMARY KEY,
  post_id    BIGINT NOT NULL REFERENCES posts(id),
  user_id    BIGINT NOT NULL REFERENCES users(id),
  content    TEXT   NOT NULL,
  created_at TIMESTAMP NOT NULL DEFAULT now()
);

A UNIQUE constraint on users.email is worth keeping even at scale. If several auth servers race to create the same account, the database is the final guardrail.

Integrity at scale: hard checks vs soft checks#

On a single database, let the database enforce the rules. At very large scale, often with data spread across shards or services, you have to choose:

The application layer (also called the service layer) is your backend code, sitting between clients and storage. A soft check is validation that code does instead of the database. For example, before saving a post, the post service confirms the user exists, often by checking a cache like Redis first, and then calls GET /users/123 only if it isn't cached.

Why teams accept soft checks:

1. Decoupling. A foreign key from posts to users can't span two services. With a soft check, if the user service is briefly down, the post service can decide what to do, for example save the post and flag it for verification.
2. Performance. At billions of rows, checking other tables on every insert adds real latency.
3. Richer rules. Databases are good at "does this ID exist?". Code is better at "can this user post more than five times a minute?"

The price is cleanup: soft deletes (mark rows as deleted rather than removing them) and background jobs that find and remove orphans, like comments on deleted posts.

Race conditions#

A race condition happens when the result depends on the timing of concurrent operations. The classic case: two ATMs read a $100 balance at the same moment, both allow an$80 withdrawal, and both write back $20.$160 left the account, but the balance only dropped by $80.

Soft checks have the same problem. Two signups ask "is the username alex taken?" at the same millisecond, both hear "no", and both insert it. There are two standard fixes.

Optimistic concurrency control (OCC)#

Assume conflicts are rare. Don't lock anything; check for a conflict at save time using a version column:

-- Read: id = 123, balance = 100, version = 5
UPDATE accounts
SET balance = 20, version = 6
WHERE id = 123 AND version = 5;
-- 0 rows updated → someone else changed it first:
-- re-read and retry

• Pros: no locks held, high throughput, works across shards, no deadlocks.
• Cons: under heavy contention ("hot" rows), many requests fail and retry, wasting work.

Pessimistic locking#

Assume conflicts will happen, and take a lock before you start. Across many servers this is often a distributed lock in Redis:

# r is a redis.Redis client; token is a random string
# unique to this holder
acquired = r.set("lock:account:123", token, nx=True, px=5000)

• nx=True (Redis NX): only set if the key doesn't exist, so there is one winner.
• px=5000 (Redis PX 5000): auto-expire after 5 s in case the holder crashes.

Holding the lock, you read, compute, write, and then release it (only if the token is still yours). Others wait and retry.

• Pros: no conflicting write reaches the database.
• Cons: everyone else waits, which lowers throughput, and you must handle holders that crash.

See Distributed Lock for more on locking across servers.

Deadlocks#

A deadlock is a circular wait. Transaction 1 locks row A and then wants B. Transaction 2 locks B and then wants A. Neither can move.

A deadlock needs all four Coffman conditions at once: mutual exclusion (locks are exclusive), hold and wait, no preemption (a lock can't be taken away), and circular wait.

In distributed locks, the common version is the zombie lock: a server takes a lock and crashes, and everyone waits forever. That's why a distributed lock should always have a TTL.

How to prevent or recover:

1. Lock in a consistent order. For example, always lock the lower account id first. That breaks the circular wait.
2. Rely on deadlock detection. Databases like PostgreSQL detect cycles and abort one transaction. Your app should catch that error and retry with exponential backoff.
3. Use timeouts. Never wait for a lock forever. Give up after, say, 500 ms and return an error.

Key takeaways#

• Every table needs a stable primary key. Prefer generated IDs (UUID or Snowflake-style) over business data.
• 1:N is a foreign key on the "many" side, N:M needs a join table, and 1:1 is usually merged into one table.
• Let the database enforce constraints when you can. At large scale, some checks move into application code, at the cost of possible races and cleanup jobs.
• Use optimistic concurrency (version column) for low contention and locks for hot, high-stakes data.
• Prevent deadlocks with consistent lock ordering, timeouts and TTLs on distributed locks.