<?xml version="1.0" encoding="UTF-8"?>
<rss version="2.0" xmlns:atom="http://www.w3.org/2005/Atom" xmlns:dc="http://purl.org/dc/elements/1.1/">
  <channel>
    <title>DEV Community: Piyush Pradhan</title>
    <description>The latest articles on DEV Community by Piyush Pradhan (@piyushpradhan_314).</description>
    <link>https://dev.to/piyushpradhan_314</link>
    <image>
      <url>https://media2.dev.to/dynamic/image/width=90,height=90,fit=cover,gravity=auto,format=auto/https:%2F%2Fdev-to-uploads.s3.us-east-2.amazonaws.com%2Fuploads%2Fuser%2Fprofile_image%2F486183%2Ff0733cee-7206-451f-a1dc-2250bca83b4e.jpg</url>
      <title>DEV Community: Piyush Pradhan</title>
      <link>https://dev.to/piyushpradhan_314</link>
    </image>
    <atom:link rel="self" type="application/rss+xml" href="https://dev.to/feed/piyushpradhan_314"/>
    <language>en</language>
    <item>
      <title>From Autopsy to Time Machine: Replaying and Forking AI Agents with SigNoz</title>
      <dc:creator>Piyush Pradhan</dc:creator>
      <pubDate>Sat, 25 Jul 2026 22:16:44 +0000</pubDate>
      <link>https://dev.to/piyushpradhan_314/from-autopsy-to-time-machine-replaying-and-forking-ai-agents-with-signoz-kg7</link>
      <guid>https://dev.to/piyushpradhan_314/from-autopsy-to-time-machine-replaying-and-forking-ai-agents-with-signoz-kg7</guid>
      <description>&lt;p&gt;When an AI agent fails, the trace usually tells me &lt;em&gt;where&lt;/em&gt; it happened. That is&lt;br&gt;
useful, but it leaves the expensive question unanswered: if I change the bad&lt;br&gt;
tool result, prompt, or model setting, does the agent actually recover?&lt;/p&gt;

&lt;p&gt;I built &lt;strong&gt;Hindsight&lt;/strong&gt; to answer that question. It is a flight recorder for AI&lt;br&gt;
agents that records a run into SigNoz, reconstructs a checkpoint without live&lt;br&gt;
calls, and forks the real registered runtime from one chosen step. The original&lt;br&gt;
run stays immutable. The fork becomes a counterfactual experiment with its own&lt;br&gt;
trace, and Hindsight resolves the incident only after the evidence proves that&lt;br&gt;
the experiment removed the original failure.&lt;/p&gt;

&lt;p&gt;That sounds like a replay debugger. The difficult part was making every word in&lt;br&gt;
that description honest.&lt;/p&gt;

&lt;h2&gt;
  
  
  A trace is an autopsy
&lt;/h2&gt;

&lt;p&gt;Agent failures are rarely one bad function call. An agent chooses a tool,&lt;br&gt;
constructs arguments, consumes the result, updates its conversation, and makes&lt;br&gt;
another decision. A timeout or malformed response near the end of that chain&lt;br&gt;
may be caused by something several steps earlier.&lt;/p&gt;

&lt;p&gt;A normal trace preserves the timing and parent-child structure, but replay&lt;br&gt;
needs more. It needs the exact request and response payloads, tool call IDs,&lt;br&gt;
arguments, outputs, ordering, agent revision, and evidence that none of those&lt;br&gt;
records were truncated or changed.&lt;/p&gt;

&lt;p&gt;My first design mistake was treating replay as "run the agent again while&lt;br&gt;
mocking a few calls." That is not replay. If an unrecorded dependency quietly&lt;br&gt;
runs live, the result is neither reproducible nor safe. Hindsight therefore&lt;br&gt;
separates two operations:&lt;/p&gt;

&lt;ul&gt;
&lt;li&gt;
&lt;strong&gt;Replay&lt;/strong&gt; reads recorded responses only and performs zero provider or tool
calls.&lt;/li&gt;
&lt;li&gt;
&lt;strong&gt;Fork&lt;/strong&gt; reconstructs state before one step, applies one explicit mutation,
and resumes through a registered runtime.&lt;/li&gt;
&lt;/ul&gt;

&lt;p&gt;The distinction made the architecture simpler and gave each operation a clear&lt;br&gt;
trust boundary.&lt;/p&gt;

&lt;h2&gt;
  
  
  SigNoz as the evidence store
&lt;/h2&gt;

&lt;p&gt;Hindsight does not copy agent telemetry into a second analytics database.&lt;br&gt;
SigNoz is the system of record.&lt;/p&gt;

&lt;p&gt;Each run is an OpenTelemetry trace. The root span identifies the run, agent,&lt;br&gt;
agent revision, task, final outcome, total steps, token usage, cost, and capture&lt;br&gt;
policy. Child spans represent ordered LLM and tool steps. Fork spans include&lt;br&gt;
the original trace ID, branch point, incident ID, and mutation hash.&lt;/p&gt;

&lt;p&gt;&lt;a href="https://media2.dev.to/dynamic/image/width=800%2Cheight=%2Cfit=scale-down%2Cgravity=auto%2Cformat=auto/https%3A%2F%2Fdev-to-uploads.s3.us-east-2.amazonaws.com%2Fuploads%2Farticles%2F7rrjtlu53jlfsatyxf0s.png" class="article-body-image-wrapper"&gt;&lt;img src="https://media2.dev.to/dynamic/image/width=800%2Cheight=%2Cfit=scale-down%2Cgravity=auto%2Cformat=auto/https%3A%2F%2Fdev-to-uploads.s3.us-east-2.amazonaws.com%2Fuploads%2Farticles%2F7rrjtlu53jlfsatyxf0s.png" alt="A failed Hindsight run in SigNoz" width="800" height="450"&gt;&lt;/a&gt;&lt;/p&gt;

&lt;p&gt;The screenshot shows a controlled malformed-tool failure. The trace has three&lt;br&gt;
spans and two errors. The selected LLM span includes its model, token counts,&lt;br&gt;
cost, step index, schema version, and payload reference.&lt;/p&gt;

&lt;p&gt;Full messages and tool outputs do not belong in span attributes. Attribute&lt;br&gt;
size limits make large values unreliable, and huge spans become painful to&lt;br&gt;
query. Hindsight writes each payload as an OpenTelemetry log record correlated&lt;br&gt;
to the same trace and span. Every record contains a SHA-256 hash, byte count,&lt;br&gt;
redaction flag, truncation flag, schema version, and payload reference.&lt;/p&gt;

&lt;p&gt;&lt;a href="https://media2.dev.to/dynamic/image/width=800%2Cheight=%2Cfit=scale-down%2Cgravity=auto%2Cformat=auto/https%3A%2F%2Fdev-to-uploads.s3.us-east-2.amazonaws.com%2Fuploads%2Farticles%2Fgu32sr407m4xqvkq6vgu.png" class="article-body-image-wrapper"&gt;&lt;img src="https://media2.dev.to/dynamic/image/width=800%2Cheight=%2Cfit=scale-down%2Cgravity=auto%2Cformat=auto/https%3A%2F%2Fdev-to-uploads.s3.us-east-2.amazonaws.com%2Fuploads%2Farticles%2Fgu32sr407m4xqvkq6vgu.png" alt="Payload and failure logs correlated to the trace" width="800" height="450"&gt;&lt;/a&gt;&lt;/p&gt;

&lt;p&gt;The logs preserve the exact LLM request and response, the tool arguments and&lt;br&gt;
malformed output, and the &lt;code&gt;run_failed&lt;/code&gt; event. This gave me a useful rule:&lt;br&gt;
&lt;strong&gt;spans describe the path; correlated logs preserve the replay evidence.&lt;/strong&gt;&lt;/p&gt;

&lt;h2&gt;
  
  
  Fail closed instead of faking a replay
&lt;/h2&gt;

&lt;p&gt;Before replaying, Hindsight builds an ordered run graph from SigNoz spans and&lt;br&gt;
payload logs. It checks that every step has the expected payload, that hashes&lt;br&gt;
match, that tool calls retain their IDs and occurrence order, and that the&lt;br&gt;
recording is complete.&lt;/p&gt;

&lt;p&gt;If a payload was redacted, truncated, deleted by retention, or modified,&lt;br&gt;
replay returns a structured checkpoint error. It never falls through to a live&lt;br&gt;
model or tool. This is less convenient than "best effort," but it is the only&lt;br&gt;
behavior I would trust during incident response.&lt;/p&gt;

&lt;p&gt;The recorder supports three payload modes. &lt;code&gt;off&lt;/code&gt; avoids storing sensitive&lt;br&gt;
content. &lt;code&gt;redacted&lt;/code&gt; is the safe default for real systems. &lt;code&gt;full&lt;/code&gt; is used by the&lt;br&gt;
local demonstration because a complete payload is required for a fork.&lt;br&gt;
Operators have to choose retention and privacy policy deliberately; Hindsight&lt;br&gt;
does not pretend that raw agent messages are harmless.&lt;/p&gt;

&lt;h2&gt;
  
  
  Observing the fleet, not just one trace
&lt;/h2&gt;

&lt;p&gt;The same recorder emits metrics for run outcomes, step duration, tool errors,&lt;br&gt;
loop score, tokens, and cost. The replay engine adds incidents, fork attempts,&lt;br&gt;
and verified-resolution duration.&lt;/p&gt;

&lt;p&gt;&lt;a href="https://media2.dev.to/dynamic/image/width=800%2Cheight=%2Cfit=scale-down%2Cgravity=auto%2Cformat=auto/https%3A%2F%2Fdev-to-uploads.s3.us-east-2.amazonaws.com%2Fuploads%2Farticles%2Fdnbm6z2ggigqiwwbrdws.png" class="article-body-image-wrapper"&gt;&lt;img src="https://media2.dev.to/dynamic/image/width=800%2Cheight=%2Cfit=scale-down%2Cgravity=auto%2Cformat=auto/https%3A%2F%2Fdev-to-uploads.s3.us-east-2.amazonaws.com%2Fuploads%2Farticles%2Fdnbm6z2ggigqiwwbrdws.png" alt="Hindsight metrics in SigNoz" width="800" height="450"&gt;&lt;/a&gt;&lt;/p&gt;

&lt;p&gt;Two dashboards ship as JSON:&lt;/p&gt;

&lt;ul&gt;
&lt;li&gt;
&lt;strong&gt;Agent Reliability&lt;/strong&gt; groups success rate, incidents, p95 step latency, and
tool error rate by agent.&lt;/li&gt;
&lt;li&gt;
&lt;strong&gt;Hindsight Ops&lt;/strong&gt; measures incidents opened, forks executed, and the time
until a fork is verified.&lt;/li&gt;
&lt;/ul&gt;

&lt;p&gt;This changed the product story. Hindsight is not only a debugger for one trace.&lt;br&gt;
It also measures whether the debugging workflow is improving agent reliability&lt;br&gt;
and resolution time.&lt;/p&gt;

&lt;h2&gt;
  
  
  From a SigNoz alert to a testable incident
&lt;/h2&gt;

&lt;p&gt;The recorder emits trace-correlated &lt;code&gt;run_failed&lt;/code&gt; and &lt;code&gt;loop_detected&lt;/code&gt; log&lt;br&gt;
events. SigNoz rules group those events by trace ID, run ID, agent ID, and&lt;br&gt;
failure type. An authenticated webhook sends the firing alert to Hindsight,&lt;br&gt;
which deduplicates deliveries and opens one incident anchored to the trace.&lt;/p&gt;

&lt;p&gt;Aggregate cost and latency alerts deliberately do not invent trace incidents.&lt;br&gt;
A fleet metric can indicate that something is wrong, but it does not provide&lt;br&gt;
an authoritative run ID. Those rules remain notifications until an operator&lt;br&gt;
selects a real trace.&lt;/p&gt;

&lt;p&gt;This is a small design decision with an important consequence: the incident&lt;br&gt;
screen never claims more evidence than SigNoz supplied.&lt;/p&gt;

&lt;h2&gt;
  
  
  Forking one variable
&lt;/h2&gt;

&lt;p&gt;A fork request identifies the original trace, branch step, mutation, mock&lt;br&gt;
policy, optional incident, and idempotency key. The replay engine sends a&lt;br&gt;
complete checkpoint to a configured runner—not a callback URL supplied by the&lt;br&gt;
browser.&lt;/p&gt;

&lt;p&gt;The runner rejects the request if the recorded agent revision does not match.&lt;br&gt;
Recorded tools are matched by name, normalized argument hash, and occurrence.&lt;br&gt;
In strict mode, every required tool result must already exist. A tool declared&lt;br&gt;
safe may run in hybrid mode, but side-effecting tools never execute live.&lt;/p&gt;

&lt;p&gt;For the demonstration, I use a tool-output override: replace the malformed&lt;br&gt;
result at the failed step, then resume the same agent loop. The fork emits a&lt;br&gt;
new trace with an OpenTelemetry span link to the original.&lt;/p&gt;

&lt;h2&gt;
  
  
  A green response is not proof
&lt;/h2&gt;

&lt;p&gt;The most important part of Hindsight is what happens after the fork succeeds.&lt;br&gt;
The engine queries both traces from SigNoz and verifies:&lt;/p&gt;

&lt;ol&gt;
&lt;li&gt;the original trace contains the incident's failure condition;&lt;/li&gt;
&lt;li&gt;the fork links to the original trace and exact branch point;&lt;/li&gt;
&lt;li&gt;the fork carries the same incident ID and mutation hash;&lt;/li&gt;
&lt;li&gt;the registered runner revision matches;&lt;/li&gt;
&lt;li&gt;the fork outcome is successful; and&lt;/li&gt;
&lt;li&gt;the original failure is absent from the fork.&lt;/li&gt;
&lt;/ol&gt;

&lt;p&gt;Only that verification path can mark an incident resolved. A manual status&lt;br&gt;
update cannot do it, and a resolved Alertmanager notification cannot do it.&lt;br&gt;
If telemetry arrives late or any evidence disagrees, the incident returns to&lt;br&gt;
open with the failed verification reason.&lt;/p&gt;

&lt;h2&gt;
  
  
  What I learned
&lt;/h2&gt;

&lt;p&gt;The hardest part was not drawing the trace timeline. It was preserving honest&lt;br&gt;
boundaries between recorded data, fixture data, replay, and execution.&lt;/p&gt;

&lt;p&gt;Three lessons survived every redesign:&lt;/p&gt;

&lt;ul&gt;
&lt;li&gt;Observability becomes more useful when it drives a constrained action, not
merely another visualization.&lt;/li&gt;
&lt;li&gt;Replayability is a data-integrity property. Missing evidence must be visible,
not patched over with live calls.&lt;/li&gt;
&lt;li&gt;Incident resolution should be an evidence-backed state transition, not a
button.&lt;/li&gt;
&lt;/ul&gt;

&lt;p&gt;Hindsight now builds successfully, its telemetry configuration is validated&lt;br&gt;
against a shared contract, and 80 automated checks cover recording, payload&lt;br&gt;
policy, graph reconstruction, replay, mutation validation, webhook parsing,&lt;br&gt;
deduplication, responsive layout, sorting, provisioning, and verified&lt;br&gt;
resolution.&lt;/p&gt;

&lt;p&gt;The result is the workflow I wanted at the start: &lt;strong&gt;Record. Replay. Fork. Prove&lt;br&gt;
the fix.&lt;/strong&gt; SigNoz supplies the evidence; Hindsight turns it into a controlled&lt;br&gt;
counterfactual.&lt;/p&gt;




&lt;p&gt;Repository: &lt;a href="https://github.com/piyushpradhan/hindsight" rel="noopener noreferrer"&gt;https://github.com/piyushpradhan/hindsight&lt;/a&gt;&lt;/p&gt;

&lt;p&gt;AI coding assistants helped with implementation, testing, design review, and&lt;br&gt;
editing. The architecture, technical decisions, integration verification, and&lt;br&gt;
final text were reviewed and owned by the author.&lt;/p&gt;

</description>
    </item>
    <item>
      <title>Think before you CREATE TABLE: A relational algebra approach</title>
      <dc:creator>Piyush Pradhan</dc:creator>
      <pubDate>Wed, 04 Feb 2026 17:50:30 +0000</pubDate>
      <link>https://dev.to/piyushpradhan_314/think-before-you-create-table-a-relational-algebra-approach-5a2f</link>
      <guid>https://dev.to/piyushpradhan_314/think-before-you-create-table-a-relational-algebra-approach-5a2f</guid>
      <description>&lt;p&gt;After building a fair amount of applications for personal projects, there came a point when I started wondering: what if one of these actually took off? Would my database hold up? Would I be scrambling to fix schema issues while angry users are tweeting about downtime?&lt;/p&gt;

&lt;p&gt;Sure, those apps work. The queries run fine with 100 users. But what about 10,000? 100,000? I have no way to know if I'm making horrible mistakes that I just haven't encountered yet.&lt;/p&gt;

&lt;p&gt;Personal projects are pretty low stakes, if the database gets weird I can just wipe it clean and start over. But I can't do that with production applications. What if I join a company with millions of users and I'm the one who has to design a critical table? Messing up without even knowing is the worst thing that could happen.&lt;/p&gt;

&lt;p&gt;The problem is: I don't know what I don't know. And SQL doesn't tell you if you're making a mistake when you're designing something. It lets you do it.&lt;/p&gt;

&lt;h2&gt;
  
  
  What this post is really about
&lt;/h2&gt;

&lt;p&gt;Apologies for taking so long to get to the point. This isn't me pretending to be some sort of database design expert—this is just me going back to the fundamentals in an attempt to figure out a way to at least make sure I'm not making any critical mistakes.&lt;/p&gt;

&lt;p&gt;What I hope to demonstrate is:&lt;/p&gt;

&lt;ul&gt;
&lt;li&gt;How to think about schema design using relational algebra concepts&lt;/li&gt;
&lt;li&gt;Real examples of schemas that I've dealt with (I didn't create them... well not ALL of them)&lt;/li&gt;
&lt;li&gt;A methodology I'll be developing to catch these issues early on&lt;/li&gt;
&lt;/ul&gt;

&lt;h2&gt;
  
  
  Common Schema Mistakes (And How Relational Algebra Exposes Them)
&lt;/h2&gt;

&lt;h3&gt;
  
  
  Example 1: The JSONB Flexibility Trap
&lt;/h3&gt;



&lt;div class="highlight js-code-highlight"&gt;
&lt;pre class="highlight sql"&gt;&lt;code&gt;&lt;span class="k"&gt;CREATE&lt;/span&gt; &lt;span class="k"&gt;TABLE&lt;/span&gt; &lt;span class="n"&gt;products&lt;/span&gt; &lt;span class="p"&gt;(&lt;/span&gt;
    &lt;span class="n"&gt;id&lt;/span&gt; &lt;span class="nb"&gt;INT&lt;/span&gt; &lt;span class="k"&gt;PRIMARY&lt;/span&gt; &lt;span class="k"&gt;KEY&lt;/span&gt;&lt;span class="p"&gt;,&lt;/span&gt;
    &lt;span class="n"&gt;name&lt;/span&gt; &lt;span class="nb"&gt;VARCHAR&lt;/span&gt;&lt;span class="p"&gt;(&lt;/span&gt;&lt;span class="mi"&gt;255&lt;/span&gt;&lt;span class="p"&gt;),&lt;/span&gt;
    &lt;span class="n"&gt;attributes&lt;/span&gt; &lt;span class="n"&gt;JSONB&lt;/span&gt;  &lt;span class="c1"&gt;-- {"category": "boots", "color": "red", "size": "10"}&lt;/span&gt;
&lt;span class="p"&gt;);&lt;/span&gt;
&lt;/code&gt;&lt;/pre&gt;

&lt;/div&gt;



&lt;p&gt;&lt;strong&gt;Initial reasoning:&lt;/strong&gt; "Products have different attributes depending on their type. Boots have sizes, books have page counts. JSON gives us flexibility without needing a column for every possible attribute!"&lt;/p&gt;

&lt;p&gt;It seemed perfect. No messy migrations when we add new product types.&lt;/p&gt;

&lt;h4&gt;
  
  
  The Problems
&lt;/h4&gt;

&lt;p&gt;&lt;strong&gt;Problem 1: Simple queries became complicated&lt;/strong&gt;&lt;/p&gt;

&lt;ul&gt;
&lt;li&gt;"Find all red boots in size 10"&lt;/li&gt;
&lt;li&gt;Instead of a clean WHERE clause, I need JSON extraction functions&lt;/li&gt;
&lt;li&gt;Every query requires parsing JSON at runtime&lt;/li&gt;
&lt;/ul&gt;

&lt;p&gt;&lt;strong&gt;Problem 2: No way to enforce data quality&lt;/strong&gt;&lt;/p&gt;

&lt;ul&gt;
&lt;li&gt;Nothing stops someone from entering &lt;code&gt;{"color": "rde"}&lt;/code&gt; (typo)&lt;/li&gt;
&lt;li&gt;Nothing requires boots to have a size — no CHECK constraints&lt;/li&gt;
&lt;li&gt;Nothing validates that size is actually a number&lt;/li&gt;
&lt;li&gt;The database can't help me — JSON is just text with structure&lt;/li&gt;
&lt;/ul&gt;

&lt;p&gt;&lt;strong&gt;Problem 3: Performance degraded&lt;/strong&gt;&lt;/p&gt;

&lt;ul&gt;
&lt;li&gt;Filtering on attributes will do full table scans&lt;/li&gt;
&lt;li&gt;Indexes won't work (or required expression indexes that precompute values)&lt;/li&gt;
&lt;li&gt;As the table grows, these queries will gradually become slower&lt;/li&gt;
&lt;/ul&gt;

&lt;h4&gt;
  
  
  What Relational Algebra Reveals
&lt;/h4&gt;

&lt;p&gt;&lt;strong&gt;Query: Find red boots in size 10&lt;/strong&gt;&lt;/p&gt;

&lt;p&gt;σattributes-&amp;gt;&amp;gt;'category'='boots' ∧ attributes-&amp;gt;&amp;gt;'color'='red' ∧ attributes-&amp;gt;&amp;gt;'size'='10'(Products)&lt;/p&gt;

&lt;p&gt;&lt;strong&gt;Problems:&lt;/strong&gt;&lt;/p&gt;

&lt;p&gt;&lt;strong&gt;1. &lt;code&gt;attributes-&amp;gt;&amp;gt;'color'&lt;/code&gt; is NOT a proper selection predicate&lt;/strong&gt;&lt;/p&gt;

&lt;ul&gt;
&lt;li&gt;It's a function call, not an attribute reference&lt;/li&gt;
&lt;li&gt;The database can't use a standard B-tree index on this. Sure, I can create an expression index: &lt;code&gt;CREATE INDEX ON products ((attributes-&amp;gt;&amp;gt;'color'))&lt;/code&gt;. But now the database has to precompute and store these values in the index. So I'm storing the data twice anyway - once in JSON, once in the index!&lt;/li&gt;
&lt;/ul&gt;

&lt;p&gt;&lt;strong&gt;2. There's no way to express πcolor(Products)&lt;/strong&gt;&lt;/p&gt;

&lt;ul&gt;
&lt;li&gt;
&lt;code&gt;color&lt;/code&gt; is not an attribute in relational algebra terms—it's buried inside JSONB&lt;/li&gt;
&lt;li&gt;I have to use: πattributes-&amp;gt;&amp;gt;'color'(Products). This returns NULL for products without a &lt;code&gt;color&lt;/code&gt; key&lt;/li&gt;
&lt;li&gt;And I can't tell the difference between "no color" and "color key missing"&lt;/li&gt;
&lt;/ul&gt;

&lt;p&gt;&lt;strong&gt;3. Constraint: ∀p ∈ σcategory='boots': p.size ≠ NULL&lt;/strong&gt;&lt;/p&gt;

&lt;ul&gt;
&lt;li&gt;Translation: "All boots must have a size"&lt;/li&gt;
&lt;li&gt;Not possible to express this at the database level&lt;/li&gt;
&lt;li&gt;No CHECK constraint can reach into JSON structure&lt;/li&gt;
&lt;li&gt;No foreign key can validate JSONB values&lt;/li&gt;
&lt;li&gt;Nothing stops me from inserting a boot with no size, or size: "extra large" (instead of something pre-defined like "UK07")&lt;/li&gt;
&lt;/ul&gt;

&lt;p&gt;&lt;strong&gt;What relational algebra tells us:&lt;/strong&gt;&lt;/p&gt;

&lt;p&gt;The expression we actually want to write is:&lt;/p&gt;

&lt;p&gt;πp.name(&lt;br&gt;
σc.name='red' ∧ s.name='10'(&lt;br&gt;
Products p ⋈ Boots b ⋈ Colors c ⋈ Sizes s&lt;br&gt;
)&lt;br&gt;
)&lt;/p&gt;
&lt;h3&gt;
  
  
  Example 2: The Denormalized Audit Trail
&lt;/h3&gt;


&lt;div class="highlight js-code-highlight"&gt;
&lt;pre class="highlight sql"&gt;&lt;code&gt;&lt;span class="k"&gt;CREATE&lt;/span&gt; &lt;span class="k"&gt;TABLE&lt;/span&gt; &lt;span class="n"&gt;user_audit&lt;/span&gt; &lt;span class="p"&gt;(&lt;/span&gt;
    &lt;span class="n"&gt;audit_id&lt;/span&gt; &lt;span class="nb"&gt;INT&lt;/span&gt; &lt;span class="k"&gt;PRIMARY&lt;/span&gt; &lt;span class="k"&gt;KEY&lt;/span&gt;&lt;span class="p"&gt;,&lt;/span&gt;
    &lt;span class="n"&gt;user_id&lt;/span&gt; &lt;span class="nb"&gt;INT&lt;/span&gt;&lt;span class="p"&gt;,&lt;/span&gt;
    &lt;span class="n"&gt;email&lt;/span&gt; &lt;span class="nb"&gt;VARCHAR&lt;/span&gt;&lt;span class="p"&gt;(&lt;/span&gt;&lt;span class="mi"&gt;100&lt;/span&gt;&lt;span class="p"&gt;),&lt;/span&gt;
    &lt;span class="n"&gt;department_name&lt;/span&gt; &lt;span class="nb"&gt;VARCHAR&lt;/span&gt;&lt;span class="p"&gt;(&lt;/span&gt;&lt;span class="mi"&gt;100&lt;/span&gt;&lt;span class="p"&gt;),&lt;/span&gt;  &lt;span class="c1"&gt;-- Copied from departments table&lt;/span&gt;
    &lt;span class="n"&gt;manager_name&lt;/span&gt; &lt;span class="nb"&gt;VARCHAR&lt;/span&gt;&lt;span class="p"&gt;(&lt;/span&gt;&lt;span class="mi"&gt;100&lt;/span&gt;&lt;span class="p"&gt;),&lt;/span&gt;     &lt;span class="c1"&gt;-- Copied from users table&lt;/span&gt;
    &lt;span class="n"&gt;changed_at&lt;/span&gt; &lt;span class="nb"&gt;TIMESTAMP&lt;/span&gt;&lt;span class="p"&gt;,&lt;/span&gt;
    &lt;span class="n"&gt;changed_by&lt;/span&gt; &lt;span class="nb"&gt;INT&lt;/span&gt;
&lt;span class="p"&gt;);&lt;/span&gt;
&lt;/code&gt;&lt;/pre&gt;

&lt;/div&gt;


&lt;p&gt;&lt;strong&gt;The reasoning seems solid:&lt;/strong&gt; "We need to preserve exactly what things looked like at the moment of the change. If a department gets renamed later, we want the audit log to show the old name."&lt;/p&gt;
&lt;h4&gt;
  
  
  The Problems
&lt;/h4&gt;

&lt;p&gt;&lt;strong&gt;Problem 1: The audit trail becomes misleading&lt;/strong&gt;&lt;/p&gt;

&lt;p&gt;(The deeper issue here is we're trying to preserve point-in-time state by copying data rather than maintaining temporal identity through &lt;code&gt;department_id&lt;/code&gt; and a history table BUT I'll have to research temporal modeling properly, so let's skip that for now.)&lt;/p&gt;

&lt;ul&gt;
&lt;li&gt;Let's say the Engineering department was renamed to "Engineering &amp;amp; AI". Now all old audit entries show "Engineering"&lt;/li&gt;
&lt;li&gt;Users looking at the audit log think: "Why does Bob's old record show a different department?" They don't realize it's the &lt;em&gt;same&lt;/em&gt; department, just renamed&lt;/li&gt;
&lt;/ul&gt;

&lt;p&gt;&lt;strong&gt;Problem 2: The "immutable" audit log needed updates&lt;/strong&gt;&lt;/p&gt;

&lt;ul&gt;
&lt;li&gt;When departments merged, we had to update thousands of audit rows which defeats the purpose of an "immutable" audit trail&lt;/li&gt;
&lt;/ul&gt;

&lt;p&gt;&lt;strong&gt;Problem 3: Simple queries became impossible&lt;/strong&gt;&lt;/p&gt;

&lt;ul&gt;
&lt;li&gt;"Show me everyone who's ever been in the Engineering department"&lt;/li&gt;
&lt;li&gt;But wait, is that "Engineering", "Engineering &amp;amp; AI", or both?&lt;/li&gt;
&lt;li&gt;String matching on department names is a nightmare&lt;/li&gt;
&lt;/ul&gt;
&lt;h4&gt;
  
  
  What Relational Algebra Reveals
&lt;/h4&gt;

&lt;p&gt;&lt;strong&gt;Query: Show audit trail with current department names&lt;/strong&gt;&lt;/p&gt;

&lt;p&gt;πemail,department_name,manager_name,changed_at(user_audit)&lt;/p&gt;

&lt;p&gt;&lt;strong&gt;Problems:&lt;/strong&gt;&lt;/p&gt;

&lt;p&gt;&lt;strong&gt;1. &lt;code&gt;department_name&lt;/code&gt; is STALE&lt;/strong&gt;&lt;/p&gt;

&lt;ul&gt;
&lt;li&gt;Department "Engineering" was renamed to "Engineering &amp;amp; AI"&lt;/li&gt;
&lt;li&gt;Audit shows old name, but users expect current name&lt;/li&gt;
&lt;li&gt;To get current names, we need to join anyway:&lt;/li&gt;
&lt;/ul&gt;

&lt;p&gt;πemail,d.name,m.name,changed_at(&lt;br&gt;
user_audit ⋈ departments d ⋈ users m&lt;br&gt;
)&lt;/p&gt;

&lt;p&gt;Here we're joining to other tables anyway! So we didn't really save any effort from copying the names.&lt;/p&gt;

&lt;p&gt;&lt;strong&gt;2. Immutability is violated&lt;/strong&gt;&lt;/p&gt;

&lt;ul&gt;
&lt;li&gt;If department name changes, we have three bad choices:

&lt;ul&gt;
&lt;li&gt;Update audit rows (violates immutability)&lt;/li&gt;
&lt;li&gt;Leave them stale (misleading audit trail)&lt;/li&gt;
&lt;li&gt;Store both old and current (now we need two columns, and which is "truth"?)&lt;/li&gt;
&lt;/ul&gt;
&lt;/li&gt;
&lt;/ul&gt;

&lt;p&gt;&lt;strong&gt;3. Can't query "when did user change departments"&lt;/strong&gt;&lt;/p&gt;

&lt;ul&gt;
&lt;li&gt;We need: σold_dept_name ≠ new_dept_name(user_audit)&lt;/li&gt;
&lt;li&gt;But &lt;code&gt;dept_name&lt;/code&gt; is a string that could be:

&lt;ul&gt;
&lt;li&gt;"Engineering" vs "engineering" (case difference)&lt;/li&gt;
&lt;li&gt;"Engineering" vs "Engineering &amp;amp; AI" (rename, not a move)&lt;/li&gt;
&lt;li&gt;Can't tell if it's the same department or different!&lt;/li&gt;
&lt;/ul&gt;
&lt;/li&gt;
&lt;/ul&gt;
&lt;h3&gt;
  
  
  Example 3: The Soft Delete Pattern
&lt;/h3&gt;


&lt;div class="highlight js-code-highlight"&gt;
&lt;pre class="highlight sql"&gt;&lt;code&gt;&lt;span class="k"&gt;CREATE&lt;/span&gt; &lt;span class="k"&gt;TABLE&lt;/span&gt; &lt;span class="n"&gt;posts&lt;/span&gt; &lt;span class="p"&gt;(&lt;/span&gt;
    &lt;span class="n"&gt;id&lt;/span&gt; &lt;span class="nb"&gt;INT&lt;/span&gt; &lt;span class="k"&gt;PRIMARY&lt;/span&gt; &lt;span class="k"&gt;KEY&lt;/span&gt;&lt;span class="p"&gt;,&lt;/span&gt;
    &lt;span class="n"&gt;title&lt;/span&gt; &lt;span class="nb"&gt;VARCHAR&lt;/span&gt;&lt;span class="p"&gt;(&lt;/span&gt;&lt;span class="mi"&gt;200&lt;/span&gt;&lt;span class="p"&gt;),&lt;/span&gt;
    &lt;span class="n"&gt;content&lt;/span&gt; &lt;span class="nb"&gt;TEXT&lt;/span&gt;&lt;span class="p"&gt;,&lt;/span&gt;
    &lt;span class="n"&gt;author_id&lt;/span&gt; &lt;span class="nb"&gt;INT&lt;/span&gt;&lt;span class="p"&gt;,&lt;/span&gt;
    &lt;span class="n"&gt;deleted_at&lt;/span&gt; &lt;span class="nb"&gt;TIMESTAMP&lt;/span&gt;  &lt;span class="c1"&gt;-- NULL = active, timestamp = deleted&lt;/span&gt;
&lt;span class="p"&gt;);&lt;/span&gt;

&lt;span class="k"&gt;CREATE&lt;/span&gt; &lt;span class="k"&gt;TABLE&lt;/span&gt; &lt;span class="n"&gt;comments&lt;/span&gt; &lt;span class="p"&gt;(&lt;/span&gt;
    &lt;span class="n"&gt;id&lt;/span&gt; &lt;span class="nb"&gt;INT&lt;/span&gt; &lt;span class="k"&gt;PRIMARY&lt;/span&gt; &lt;span class="k"&gt;KEY&lt;/span&gt;&lt;span class="p"&gt;,&lt;/span&gt;
    &lt;span class="n"&gt;post_id&lt;/span&gt; &lt;span class="nb"&gt;INT&lt;/span&gt; &lt;span class="k"&gt;REFERENCES&lt;/span&gt; &lt;span class="n"&gt;posts&lt;/span&gt;&lt;span class="p"&gt;(&lt;/span&gt;&lt;span class="n"&gt;id&lt;/span&gt;&lt;span class="p"&gt;),&lt;/span&gt;
    &lt;span class="n"&gt;user_id&lt;/span&gt; &lt;span class="nb"&gt;INT&lt;/span&gt;&lt;span class="p"&gt;,&lt;/span&gt;
    &lt;span class="nb"&gt;text&lt;/span&gt; &lt;span class="nb"&gt;TEXT&lt;/span&gt;&lt;span class="p"&gt;,&lt;/span&gt;
    &lt;span class="n"&gt;deleted_at&lt;/span&gt; &lt;span class="nb"&gt;TIMESTAMP&lt;/span&gt;
&lt;span class="p"&gt;);&lt;/span&gt;
&lt;/code&gt;&lt;/pre&gt;

&lt;/div&gt;


&lt;p&gt;&lt;strong&gt;The pitch:&lt;/strong&gt; "We never actually delete data! It's safer, we can undelete things, and we have a full audit trail."&lt;/p&gt;
&lt;h4&gt;
  
  
  The Problems
&lt;/h4&gt;

&lt;p&gt;&lt;strong&gt;Problem 1: Every query needs filtering&lt;/strong&gt;&lt;/p&gt;

&lt;ul&gt;
&lt;li&gt;Want active posts? &lt;code&gt;WHERE deleted_at IS NULL&lt;/code&gt;
&lt;/li&gt;
&lt;li&gt;Want active comments? &lt;code&gt;WHERE deleted_at IS NULL&lt;/code&gt;
&lt;/li&gt;
&lt;li&gt;Forgot it once? Data leak.&lt;/li&gt;
&lt;li&gt;Now every single query comes with this one WHERE clause&lt;/li&gt;
&lt;/ul&gt;

&lt;p&gt;&lt;strong&gt;Problem 2: Unique constraints are broken&lt;/strong&gt;&lt;/p&gt;

&lt;ul&gt;
&lt;li&gt;User email should be unique but soft-deleted user still has their email in the table&lt;/li&gt;
&lt;li&gt;New user tries to register with same email → constraint violation!&lt;/li&gt;
&lt;li&gt;Workaround: &lt;code&gt;UNIQUE(email) WHERE deleted_at IS NULL&lt;/code&gt;. But now uniqueness is conditional, which violates relational model principles&lt;/li&gt;
&lt;/ul&gt;

&lt;p&gt;&lt;strong&gt;Problem 3: Referential integrity doesn't match application semantics&lt;/strong&gt;&lt;/p&gt;

&lt;ul&gt;
&lt;li&gt;
&lt;code&gt;comments.post_id REFERENCES posts(id)&lt;/code&gt; allows references to deleted posts&lt;/li&gt;
&lt;li&gt;Should comments on deleted posts be visible? Hidden? Cascade-deleted?&lt;/li&gt;
&lt;li&gt;The database says "this reference is valid" but your application has to enforce additional rules&lt;/li&gt;
&lt;li&gt;You've pushed referential integrity logic into application code&lt;/li&gt;
&lt;/ul&gt;

&lt;p&gt;&lt;strong&gt;Problem 4: Performance degradation&lt;/strong&gt;&lt;/p&gt;

&lt;ul&gt;
&lt;li&gt;Indexes include deleted rows (never queried, just bloat)&lt;/li&gt;
&lt;li&gt;Every query scans past thousands of deleted rows which is absolutely unnecessary and will affect performance&lt;/li&gt;
&lt;li&gt;Full table scans get slower over time&lt;/li&gt;
&lt;li&gt;You're essentially archiving data in the hot table&lt;/li&gt;
&lt;/ul&gt;
&lt;h4&gt;
  
  
  What Relational Algebra Reveals
&lt;/h4&gt;

&lt;p&gt;&lt;strong&gt;Query: Show all active posts&lt;/strong&gt;&lt;/p&gt;

&lt;p&gt;σdeleted_at=NULL(Posts)&lt;/p&gt;

&lt;p&gt;&lt;strong&gt;Problems:&lt;/strong&gt;&lt;/p&gt;

&lt;p&gt;&lt;strong&gt;1. Selection predicate required on EVERY query&lt;/strong&gt;&lt;/p&gt;

&lt;ul&gt;
&lt;li&gt;Forget it once → data leak&lt;/li&gt;
&lt;li&gt;Can't enforce this at schema level&lt;/li&gt;
&lt;li&gt;It's a runtime check, not a design constraint&lt;/li&gt;
&lt;/ul&gt;

&lt;p&gt;&lt;strong&gt;2. Join ambiguity&lt;/strong&gt;&lt;/p&gt;

&lt;p&gt;Posts ⋈ Comments&lt;/p&gt;

&lt;p&gt;Should this return:&lt;/p&gt;

&lt;ul&gt;
&lt;li&gt;Active posts with active comments?&lt;/li&gt;
&lt;li&gt;Active posts with all comments (including deleted)?&lt;/li&gt;
&lt;li&gt;All posts with active comments?&lt;/li&gt;
&lt;/ul&gt;

&lt;p&gt;The schema doesn't say! It's ambiguous!&lt;/p&gt;

&lt;p&gt;&lt;strong&gt;3. Uniqueness constraint can't be expressed&lt;/strong&gt;&lt;/p&gt;

&lt;p&gt;∀u1,u2 ∈ σdeleted_at=NULL(Users): u1 ≠ u2 → u1.email ≠ u2.email&lt;/p&gt;

&lt;p&gt;This is a CONDITIONAL constraint (only on subset where deleted_at IS NULL).&lt;br&gt;
Relational model assumes constraints apply to entire relation.&lt;br&gt;
We've violated the model.&lt;/p&gt;

&lt;p&gt;&lt;strong&gt;What relational algebra tells us:&lt;/strong&gt;&lt;/p&gt;

&lt;p&gt;If we express what we actually need:&lt;/p&gt;

&lt;p&gt;Active posts: πtitle,content(Posts)&lt;br&gt;&lt;br&gt;
Archived posts: πtitle,content,deleted_at(ArchivedPosts)&lt;/p&gt;



&lt;p&gt;I think we've seen enough examples of how easy it is to mess up common decisions.&lt;/p&gt;
&lt;h2&gt;
  
  
  A Methodology to Prevent These Mistakes
&lt;/h2&gt;

&lt;p&gt;Before jumping in and creating tables, let's follow these steps:&lt;/p&gt;

&lt;ol&gt;
&lt;li&gt;Gather requirements (queries and constraints)&lt;/li&gt;
&lt;li&gt;Express the queries in relational algebra for a clearer picture&lt;/li&gt;
&lt;li&gt;Identify relations and attributes from relational algebra expressions&lt;/li&gt;
&lt;li&gt;Apply normalization rules&lt;/li&gt;
&lt;li&gt;Validate with relational algebra (check if requirements can be expressed cleanly)&lt;/li&gt;
&lt;li&gt;Implement in SQL&lt;/li&gt;
&lt;/ol&gt;

&lt;p&gt;Let's take the first example to understand this better - the &lt;code&gt;products&lt;/code&gt; table.&lt;/p&gt;
&lt;h3&gt;
  
  
  Step 1: Gather Requirements
&lt;/h3&gt;
&lt;h4&gt;
  
  
  Queries
&lt;/h4&gt;

&lt;ul&gt;
&lt;li&gt;Find all products by category&lt;/li&gt;
&lt;li&gt;Find all red boots in size 10&lt;/li&gt;
&lt;li&gt;List all available colors&lt;/li&gt;
&lt;li&gt;Count products per category&lt;/li&gt;
&lt;/ul&gt;
&lt;h4&gt;
  
  
  Constraints
&lt;/h4&gt;

&lt;ul&gt;
&lt;li&gt;Every product must have a category&lt;/li&gt;
&lt;li&gt;Every boot must have a size&lt;/li&gt;
&lt;li&gt;Color must be from a pre-defined set of colors&lt;/li&gt;
&lt;/ul&gt;
&lt;h3&gt;
  
  
  Step 2: Express in Relational Algebra
&lt;/h3&gt;

&lt;p&gt;&lt;strong&gt;Query 1: Find all products by category&lt;/strong&gt;&lt;/p&gt;

&lt;p&gt;σcategory='boots'(Products)&lt;/p&gt;

&lt;ul&gt;
&lt;li&gt;Need Products relation (obviously)&lt;/li&gt;
&lt;li&gt;Need &lt;code&gt;category&lt;/code&gt; as an attribute—can't use JSONB&lt;/li&gt;
&lt;li&gt;Need index on &lt;code&gt;category&lt;/code&gt;
&lt;/li&gt;
&lt;/ul&gt;

&lt;p&gt;&lt;strong&gt;Query 2: Find all red boots in size 10&lt;/strong&gt;&lt;/p&gt;

&lt;p&gt;σcolor='red' ∧ size='10'(Boots)&lt;/p&gt;

&lt;p&gt;&lt;strong&gt;Notice:&lt;/strong&gt; No &lt;code&gt;category='boots'&lt;/code&gt; predicate needed. The fact that we're querying the Boots relation &lt;em&gt;is&lt;/em&gt; the category filter. This is a signal from relational algebra: if category is always the same for a subset of products, that subset should be its own relation.&lt;/p&gt;

&lt;ul&gt;
&lt;li&gt;Need &lt;code&gt;color&lt;/code&gt; and &lt;code&gt;size&lt;/code&gt; attributes&lt;/li&gt;
&lt;li&gt;Multiple selection predicates—can consider a compound index&lt;/li&gt;
&lt;/ul&gt;

&lt;p&gt;&lt;strong&gt;Query 3: List all available colors&lt;/strong&gt;&lt;/p&gt;

&lt;p&gt;πcolor(Products)&lt;/p&gt;

&lt;ul&gt;
&lt;li&gt;Need Colors as a separate relation&lt;/li&gt;
&lt;li&gt;If Colors is simply an attribute, then DISTINCT has to be used which will comparitively be slower&lt;/li&gt;
&lt;/ul&gt;

&lt;p&gt;&lt;strong&gt;Query 4: Count products per category&lt;/strong&gt;&lt;/p&gt;

&lt;p&gt;Γcategory; COUNT(*)-&amp;gt;count(Products)&lt;/p&gt;

&lt;ul&gt;
&lt;li&gt;Need &lt;code&gt;category&lt;/code&gt; as a groupable attribute&lt;/li&gt;
&lt;/ul&gt;
&lt;h4&gt;
  
  
  Constraint Check
&lt;/h4&gt;

&lt;p&gt;∀p ∈ σcategory='boots'(Products): p.size ≠ NULL&lt;/p&gt;

&lt;p&gt;Can we express this in the schema?&lt;/p&gt;

&lt;ul&gt;
&lt;li&gt;If size is in Products table: Need conditional NOT NULL (impossible!)&lt;/li&gt;
&lt;/ul&gt;

&lt;p&gt;&lt;strong&gt;Conclusion:&lt;/strong&gt; Boots should be a separate relation.&lt;/p&gt;
&lt;h3&gt;
  
  
  Step 3: Identify Relations and Attributes
&lt;/h3&gt;
&lt;h4&gt;
  
  
  Attributes used in selection must be columns
&lt;/h4&gt;

&lt;p&gt;σcolor='red' ∧ size='10'&lt;/p&gt;

&lt;p&gt;Attributes needed: color, size&lt;/p&gt;
&lt;h4&gt;
  
  
  Attributes used in projection must be columns
&lt;/h4&gt;

&lt;p&gt;πcolor(Products)&lt;/p&gt;

&lt;p&gt;Attributes needed: color&lt;/p&gt;
&lt;h4&gt;
  
  
  Joins indicate foreign key relationships
&lt;/h4&gt;

&lt;p&gt;Products ⋈ Boots&lt;/p&gt;

&lt;p&gt;Foreign key needed: Boots.product_id REFERENCES Products.id&lt;/p&gt;
&lt;h4&gt;
  
  
  Constraints dictate relation structure
&lt;/h4&gt;

&lt;p&gt;∀p ∈ σcategory='boots'(Products): p.size ≠ NULL&lt;/p&gt;

&lt;p&gt;Decision: Boots should be a separate relation. This allows &lt;code&gt;size&lt;/code&gt; column to have a NOT NULL constraint.&lt;/p&gt;
&lt;h4&gt;
  
  
  Identified Relations
&lt;/h4&gt;

&lt;p&gt;&lt;strong&gt;1. Products&lt;/strong&gt;(id, name, category_id)&lt;/p&gt;

&lt;ul&gt;
&lt;li&gt;Primary key: id&lt;/li&gt;
&lt;li&gt;Foreign key: category_id → Categories.id&lt;/li&gt;
&lt;/ul&gt;

&lt;p&gt;&lt;strong&gt;2. Categories&lt;/strong&gt;(id, name)&lt;/p&gt;

&lt;ul&gt;
&lt;li&gt;Primary key: id&lt;/li&gt;
&lt;li&gt;Unique: name&lt;/li&gt;
&lt;/ul&gt;

&lt;p&gt;&lt;strong&gt;3. Boots&lt;/strong&gt;(product_id, color_id, size_id, material_id)&lt;/p&gt;

&lt;ul&gt;
&lt;li&gt;Primary key: product_id&lt;/li&gt;
&lt;li&gt;Foreign keys:

&lt;ul&gt;
&lt;li&gt;product_id → Products.id&lt;/li&gt;
&lt;li&gt;color_id → Colors.id&lt;/li&gt;
&lt;li&gt;size_id → Sizes.id&lt;/li&gt;
&lt;li&gt;material_id → Materials.id&lt;/li&gt;
&lt;/ul&gt;
&lt;/li&gt;
&lt;/ul&gt;

&lt;p&gt;&lt;strong&gt;4. Colors&lt;/strong&gt;(id, name, hex_code)&lt;/p&gt;

&lt;ul&gt;
&lt;li&gt;Primary key: id&lt;/li&gt;
&lt;li&gt;Unique: name&lt;/li&gt;
&lt;/ul&gt;

&lt;p&gt;&lt;strong&gt;5. Sizes&lt;/strong&gt;(id, name, sort_order)&lt;/p&gt;

&lt;ul&gt;
&lt;li&gt;Primary key: id&lt;/li&gt;
&lt;li&gt;Unique: name&lt;/li&gt;
&lt;/ul&gt;
&lt;h3&gt;
  
  
  Step 4: Apply Normalization Rules
&lt;/h3&gt;

&lt;p&gt;Let's say you have:&lt;br&gt;
&lt;/p&gt;

&lt;div class="highlight js-code-highlight"&gt;
&lt;pre class="highlight sql"&gt;&lt;code&gt;&lt;span class="k"&gt;CREATE&lt;/span&gt; &lt;span class="k"&gt;TABLE&lt;/span&gt; &lt;span class="n"&gt;order_items&lt;/span&gt; &lt;span class="p"&gt;(&lt;/span&gt;
    &lt;span class="n"&gt;order_id&lt;/span&gt; &lt;span class="nb"&gt;INT&lt;/span&gt;&lt;span class="p"&gt;,&lt;/span&gt;
    &lt;span class="n"&gt;product_id&lt;/span&gt; &lt;span class="nb"&gt;INT&lt;/span&gt;&lt;span class="p"&gt;,&lt;/span&gt;
    &lt;span class="n"&gt;customer_name&lt;/span&gt; &lt;span class="nb"&gt;VARCHAR&lt;/span&gt;&lt;span class="p"&gt;(&lt;/span&gt;&lt;span class="mi"&gt;100&lt;/span&gt;&lt;span class="p"&gt;),&lt;/span&gt;
    &lt;span class="n"&gt;product_name&lt;/span&gt; &lt;span class="nb"&gt;VARCHAR&lt;/span&gt;&lt;span class="p"&gt;(&lt;/span&gt;&lt;span class="mi"&gt;100&lt;/span&gt;&lt;span class="p"&gt;),&lt;/span&gt;
    &lt;span class="n"&gt;quantity&lt;/span&gt; &lt;span class="nb"&gt;INT&lt;/span&gt;&lt;span class="p"&gt;,&lt;/span&gt;
    &lt;span class="k"&gt;PRIMARY&lt;/span&gt; &lt;span class="k"&gt;KEY&lt;/span&gt; &lt;span class="p"&gt;(&lt;/span&gt;&lt;span class="n"&gt;order_id&lt;/span&gt;&lt;span class="p"&gt;,&lt;/span&gt; &lt;span class="n"&gt;product_id&lt;/span&gt;&lt;span class="p"&gt;)&lt;/span&gt;
&lt;span class="p"&gt;);&lt;/span&gt;
&lt;/code&gt;&lt;/pre&gt;

&lt;/div&gt;



&lt;p&gt;Try expressing this business rule in RA:&lt;br&gt;
&lt;/p&gt;

&lt;div class="highlight js-code-highlight"&gt;
&lt;pre class="highlight plaintext"&gt;&lt;code&gt;∀oi1, oi2 ∈ order_items:  
 oi1.order_id = oi2.order_id → oi1.customer_name = oi2.customer_name
&lt;/code&gt;&lt;/pre&gt;

&lt;/div&gt;



&lt;p&gt;"All items in the same order must have the same customer name."&lt;/p&gt;

&lt;p&gt;Can this be enforced in SQL? No. There's no CHECK constraint that can span rows.&lt;/p&gt;

&lt;p&gt;&lt;strong&gt;Conclusion:&lt;/strong&gt; You have a functional dependency (order_id → customer_name) that's not captured by your primary key. This violates 2NF.&lt;/p&gt;

&lt;p&gt;&lt;strong&gt;The fix:&lt;/strong&gt;&lt;/p&gt;

&lt;ul&gt;
&lt;li&gt;Split into &lt;code&gt;orders(order_id, customer_name)&lt;/code&gt; and &lt;code&gt;order_items(order_id, product_id, quantity)&lt;/code&gt;
&lt;/li&gt;
&lt;li&gt;Now the functional dependency is enforced by the primary key of &lt;code&gt;orders&lt;/code&gt;
&lt;/li&gt;
&lt;/ul&gt;

&lt;p&gt;&lt;strong&gt;The pattern:&lt;/strong&gt; If a constraint can be expressed in relational algebra but can't be enforced in SQL, it's probably a normalization issue.&lt;/p&gt;

&lt;h3&gt;
  
  
  Step 5: Validate with Relational Algebra
&lt;/h3&gt;

&lt;p&gt;Rewrite the expressions from earlier with actual schema relations and check if it's natural and efficient.&lt;/p&gt;

&lt;p&gt;&lt;strong&gt;Expression from step 2:&lt;/strong&gt;&lt;/p&gt;

&lt;p&gt;σcolor='red' ∧ size='10'(Boots)&lt;/p&gt;

&lt;p&gt;&lt;strong&gt;Expression with actual schema relations:&lt;/strong&gt;&lt;/p&gt;

&lt;p&gt;πp.name, p.id(&lt;br&gt;&lt;br&gt;
    σc.name='red' ∧ s.name='10'(&lt;br&gt;&lt;br&gt;
    Products p&lt;br&gt;&lt;br&gt;
    ⋈(p.id = b.product_id) Boots b&lt;br&gt;&lt;br&gt;
    ⋈(b.color_id = c.id) Colors c&lt;br&gt;&lt;br&gt;
    ⋈(b.size_id = s.id) Sizes s&lt;br&gt;&lt;br&gt;
  )&lt;br&gt;&lt;br&gt;
)&lt;/p&gt;

&lt;p&gt;&lt;strong&gt;Notice what's missing:&lt;/strong&gt; No &lt;code&gt;category='boots'&lt;/code&gt; filter. We're querying Boots directly its existence as a relation &lt;em&gt;is&lt;/em&gt; the category filter. This is exactly what relational algebra tells us: repeated selection predicates on the same value suggest the need for a separate relation.&lt;/p&gt;
&lt;h4&gt;
  
  
  Validation Questions
&lt;/h4&gt;

&lt;ul&gt;
&lt;li&gt;
&lt;strong&gt;Are all operations standard RA?&lt;/strong&gt; Yes (σ, π, ⋈)&lt;/li&gt;
&lt;li&gt;
&lt;strong&gt;Are joins natural?&lt;/strong&gt; Yes (all on foreign key integer columns)&lt;/li&gt;
&lt;li&gt;
&lt;strong&gt;Can database optimize this?&lt;/strong&gt; Yes

&lt;ul&gt;
&lt;li&gt;Can use indexes on &lt;code&gt;color_id&lt;/code&gt;, &lt;code&gt;size_id&lt;/code&gt;
&lt;/li&gt;
&lt;li&gt;Can use index on &lt;code&gt;category_id&lt;/code&gt;
&lt;/li&gt;
&lt;li&gt;Query planner has statistics on all columns&lt;/li&gt;
&lt;li&gt;Can reorder joins for efficiency&lt;/li&gt;
&lt;/ul&gt;
&lt;/li&gt;
&lt;li&gt;
&lt;strong&gt;Does it handle edge cases?&lt;/strong&gt;

&lt;ul&gt;
&lt;li&gt;What if product has no color? Inner join excludes it (correct!)&lt;/li&gt;
&lt;li&gt;What if color doesn't exist? FK prevents invalid &lt;code&gt;color_id&lt;/code&gt; (correct!)&lt;/li&gt;
&lt;li&gt;What if size is NULL? NOT NULL constraint prevents it (correct!)&lt;/li&gt;
&lt;/ul&gt;
&lt;/li&gt;
&lt;/ul&gt;

&lt;p&gt;&lt;strong&gt;Conclusion:&lt;/strong&gt; Schema validated!&lt;/p&gt;
&lt;h4&gt;
  
  
  Bonus: From Relational Algebra to Indexes
&lt;/h4&gt;

&lt;p&gt;Here's something cool: RA expressions map directly to index strategy.&lt;/p&gt;

&lt;p&gt;When you see:&lt;/p&gt;

&lt;p&gt;σcolor='red' ∧ size='10'(Boots)&lt;/p&gt;

&lt;p&gt;The ∧ (AND) tells you these filters are evaluated together. In SQL:&lt;br&gt;
&lt;/p&gt;

&lt;div class="highlight js-code-highlight"&gt;
&lt;pre class="highlight sql"&gt;&lt;code&gt;&lt;span class="k"&gt;WHERE&lt;/span&gt; &lt;span class="n"&gt;color_id&lt;/span&gt; &lt;span class="o"&gt;=&lt;/span&gt; &lt;span class="n"&gt;X&lt;/span&gt; &lt;span class="k"&gt;AND&lt;/span&gt; &lt;span class="n"&gt;size_id&lt;/span&gt; &lt;span class="o"&gt;=&lt;/span&gt; &lt;span class="n"&gt;Y&lt;/span&gt;
&lt;/code&gt;&lt;/pre&gt;

&lt;/div&gt;



&lt;p&gt;&lt;strong&gt;Index strategy from RA:&lt;/strong&gt;&lt;/p&gt;

&lt;ul&gt;
&lt;li&gt;Single predicate → Single column index&lt;/li&gt;
&lt;li&gt;Multiple predicates with ∧ → Compound index&lt;/li&gt;
&lt;li&gt;Predicates with ∨ (OR) → Separate indexes (or bitmap scan)&lt;/li&gt;
&lt;/ul&gt;

&lt;p&gt;For our boots query:&lt;br&gt;
&lt;/p&gt;

&lt;div class="highlight js-code-highlight"&gt;
&lt;pre class="highlight sql"&gt;&lt;code&gt;&lt;span class="k"&gt;CREATE&lt;/span&gt; &lt;span class="k"&gt;INDEX&lt;/span&gt; &lt;span class="n"&gt;idx_boots_color_size&lt;/span&gt; &lt;span class="k"&gt;ON&lt;/span&gt; &lt;span class="n"&gt;boots&lt;/span&gt;&lt;span class="p"&gt;(&lt;/span&gt;&lt;span class="n"&gt;color_id&lt;/span&gt;&lt;span class="p"&gt;,&lt;/span&gt; &lt;span class="n"&gt;size_id&lt;/span&gt;&lt;span class="p"&gt;);&lt;/span&gt;
&lt;/code&gt;&lt;/pre&gt;

&lt;/div&gt;



&lt;p&gt;&lt;strong&gt;Why this order?&lt;/strong&gt; The RA expression doesn't tell you... but query selectivity does.&lt;br&gt;
For example, if &lt;code&gt;color&lt;/code&gt; filters out 90% of rows and &lt;code&gt;size&lt;/code&gt; filters out 50%, put color first. The database can use the index for:&lt;/p&gt;

&lt;ul&gt;
&lt;li&gt;Just color: ✓&lt;/li&gt;
&lt;li&gt;Color + size: ✓&lt;/li&gt;
&lt;li&gt;Just size: ✗ (needs separate index)&lt;/li&gt;
&lt;/ul&gt;

&lt;p&gt;&lt;strong&gt;Rule of thumb:&lt;/strong&gt; Order index columns from most selective to least selective, and match the order you'll most commonly query them.&lt;/p&gt;
&lt;h4&gt;
  
  
  Using Validation to Identify Bad Schema
&lt;/h4&gt;

&lt;p&gt;Let's take an example. And for this example, we're going back to the previous schema:&lt;br&gt;
&lt;/p&gt;

&lt;div class="highlight js-code-highlight"&gt;
&lt;pre class="highlight sql"&gt;&lt;code&gt;&lt;span class="k"&gt;CREATE&lt;/span&gt; &lt;span class="k"&gt;TABLE&lt;/span&gt; &lt;span class="n"&gt;products&lt;/span&gt; &lt;span class="p"&gt;(&lt;/span&gt;
  &lt;span class="n"&gt;id&lt;/span&gt; &lt;span class="nb"&gt;INT&lt;/span&gt; &lt;span class="k"&gt;PRIMARY&lt;/span&gt; &lt;span class="k"&gt;KEY&lt;/span&gt;&lt;span class="p"&gt;,&lt;/span&gt;
  &lt;span class="n"&gt;name&lt;/span&gt; &lt;span class="nb"&gt;VARCHAR&lt;/span&gt;&lt;span class="p"&gt;(&lt;/span&gt;&lt;span class="mi"&gt;255&lt;/span&gt;&lt;span class="p"&gt;),&lt;/span&gt;
  &lt;span class="n"&gt;attributes&lt;/span&gt; &lt;span class="n"&gt;JSONB&lt;/span&gt;
&lt;span class="p"&gt;);&lt;/span&gt;
&lt;/code&gt;&lt;/pre&gt;

&lt;/div&gt;



&lt;p&gt;Trying to express the same query in relational algebra:&lt;/p&gt;

&lt;p&gt;σattributes-&amp;gt;&amp;gt;'category'='boots' ∧ attributes-&amp;gt;&amp;gt;'color'='red' ∧ attributes-&amp;gt;&amp;gt;'size'='10'&lt;/p&gt;

&lt;p&gt;&lt;strong&gt;Validation Questions:&lt;/strong&gt;&lt;/p&gt;

&lt;ul&gt;
&lt;li&gt;
&lt;strong&gt;Are all operations standard RA?&lt;/strong&gt; No (&lt;code&gt;-&amp;gt;&amp;gt;&lt;/code&gt; is a function call, not an attribute reference)&lt;/li&gt;
&lt;li&gt;
&lt;strong&gt;Can database optimize this?&lt;/strong&gt; No, database can't use regular indexes on JSON keys&lt;/li&gt;
&lt;li&gt;
&lt;strong&gt;Does it handle edge cases?&lt;/strong&gt;

&lt;ul&gt;
&lt;li&gt;There's no type safety, can't enforce that color values are from a set of predefined colors&lt;/li&gt;
&lt;li&gt;No constraint enforcement, can't ensure if boots will have a size or material&lt;/li&gt;
&lt;/ul&gt;


&lt;/li&gt;

&lt;/ul&gt;

&lt;p&gt;&lt;strong&gt;Conclusion: Schema failed validation.&lt;/strong&gt;&lt;/p&gt;

&lt;h3&gt;
  
  
  Step 6: Implementing in SQL (Finally!)
&lt;/h3&gt;



&lt;div class="highlight js-code-highlight"&gt;
&lt;pre class="highlight sql"&gt;&lt;code&gt;&lt;span class="c1"&gt;-- Core entities&lt;/span&gt;
&lt;span class="k"&gt;CREATE&lt;/span&gt; &lt;span class="k"&gt;TABLE&lt;/span&gt; &lt;span class="n"&gt;categories&lt;/span&gt; &lt;span class="p"&gt;(&lt;/span&gt;
    &lt;span class="n"&gt;id&lt;/span&gt; &lt;span class="nb"&gt;SERIAL&lt;/span&gt; &lt;span class="k"&gt;PRIMARY&lt;/span&gt; &lt;span class="k"&gt;KEY&lt;/span&gt;&lt;span class="p"&gt;,&lt;/span&gt;
    &lt;span class="n"&gt;name&lt;/span&gt; &lt;span class="nb"&gt;VARCHAR&lt;/span&gt;&lt;span class="p"&gt;(&lt;/span&gt;&lt;span class="mi"&gt;100&lt;/span&gt;&lt;span class="p"&gt;)&lt;/span&gt; &lt;span class="k"&gt;UNIQUE&lt;/span&gt; &lt;span class="k"&gt;NOT&lt;/span&gt; &lt;span class="k"&gt;NULL&lt;/span&gt;
&lt;span class="p"&gt;);&lt;/span&gt;

&lt;span class="k"&gt;CREATE&lt;/span&gt; &lt;span class="k"&gt;TABLE&lt;/span&gt; &lt;span class="n"&gt;products&lt;/span&gt; &lt;span class="p"&gt;(&lt;/span&gt;
    &lt;span class="n"&gt;id&lt;/span&gt; &lt;span class="nb"&gt;SERIAL&lt;/span&gt; &lt;span class="k"&gt;PRIMARY&lt;/span&gt; &lt;span class="k"&gt;KEY&lt;/span&gt;&lt;span class="p"&gt;,&lt;/span&gt;
    &lt;span class="n"&gt;name&lt;/span&gt; &lt;span class="nb"&gt;VARCHAR&lt;/span&gt;&lt;span class="p"&gt;(&lt;/span&gt;&lt;span class="mi"&gt;200&lt;/span&gt;&lt;span class="p"&gt;)&lt;/span&gt; &lt;span class="k"&gt;NOT&lt;/span&gt; &lt;span class="k"&gt;NULL&lt;/span&gt;&lt;span class="p"&gt;,&lt;/span&gt;
    &lt;span class="n"&gt;category_id&lt;/span&gt; &lt;span class="nb"&gt;INT&lt;/span&gt; &lt;span class="k"&gt;NOT&lt;/span&gt; &lt;span class="k"&gt;NULL&lt;/span&gt; &lt;span class="k"&gt;REFERENCES&lt;/span&gt; &lt;span class="n"&gt;categories&lt;/span&gt;&lt;span class="p"&gt;(&lt;/span&gt;&lt;span class="n"&gt;id&lt;/span&gt;&lt;span class="p"&gt;),&lt;/span&gt;
    &lt;span class="n"&gt;created_at&lt;/span&gt; &lt;span class="nb"&gt;TIMESTAMP&lt;/span&gt; &lt;span class="k"&gt;DEFAULT&lt;/span&gt; &lt;span class="n"&gt;NOW&lt;/span&gt;&lt;span class="p"&gt;()&lt;/span&gt;
&lt;span class="p"&gt;);&lt;/span&gt;

&lt;span class="c1"&gt;-- Define the schema for the attributes&lt;/span&gt;
&lt;span class="k"&gt;CREATE&lt;/span&gt; &lt;span class="k"&gt;TABLE&lt;/span&gt; &lt;span class="n"&gt;colors&lt;/span&gt; &lt;span class="p"&gt;(&lt;/span&gt;
    &lt;span class="n"&gt;id&lt;/span&gt; &lt;span class="nb"&gt;SERIAL&lt;/span&gt; &lt;span class="k"&gt;PRIMARY&lt;/span&gt; &lt;span class="k"&gt;KEY&lt;/span&gt;&lt;span class="p"&gt;,&lt;/span&gt;
    &lt;span class="n"&gt;name&lt;/span&gt; &lt;span class="nb"&gt;VARCHAR&lt;/span&gt;&lt;span class="p"&gt;(&lt;/span&gt;&lt;span class="mi"&gt;50&lt;/span&gt;&lt;span class="p"&gt;)&lt;/span&gt; &lt;span class="k"&gt;UNIQUE&lt;/span&gt; &lt;span class="k"&gt;NOT&lt;/span&gt; &lt;span class="k"&gt;NULL&lt;/span&gt;&lt;span class="p"&gt;,&lt;/span&gt;
    &lt;span class="n"&gt;hex_code&lt;/span&gt; &lt;span class="nb"&gt;CHAR&lt;/span&gt;&lt;span class="p"&gt;(&lt;/span&gt;&lt;span class="mi"&gt;7&lt;/span&gt;&lt;span class="p"&gt;)&lt;/span&gt; &lt;span class="k"&gt;NOT&lt;/span&gt; &lt;span class="k"&gt;NULL&lt;/span&gt;
&lt;span class="p"&gt;);&lt;/span&gt;

&lt;span class="k"&gt;CREATE&lt;/span&gt; &lt;span class="k"&gt;TABLE&lt;/span&gt; &lt;span class="n"&gt;sizes&lt;/span&gt; &lt;span class="p"&gt;(&lt;/span&gt;
    &lt;span class="n"&gt;id&lt;/span&gt; &lt;span class="nb"&gt;SERIAL&lt;/span&gt; &lt;span class="k"&gt;PRIMARY&lt;/span&gt; &lt;span class="k"&gt;KEY&lt;/span&gt;&lt;span class="p"&gt;,&lt;/span&gt;
    &lt;span class="n"&gt;name&lt;/span&gt; &lt;span class="nb"&gt;VARCHAR&lt;/span&gt;&lt;span class="p"&gt;(&lt;/span&gt;&lt;span class="mi"&gt;10&lt;/span&gt;&lt;span class="p"&gt;)&lt;/span&gt; &lt;span class="k"&gt;UNIQUE&lt;/span&gt; &lt;span class="k"&gt;NOT&lt;/span&gt; &lt;span class="k"&gt;NULL&lt;/span&gt;&lt;span class="p"&gt;,&lt;/span&gt;
    &lt;span class="n"&gt;sort_order&lt;/span&gt; &lt;span class="nb"&gt;INT&lt;/span&gt; &lt;span class="k"&gt;NOT&lt;/span&gt; &lt;span class="k"&gt;NULL&lt;/span&gt;
&lt;span class="p"&gt;);&lt;/span&gt;

&lt;span class="k"&gt;CREATE&lt;/span&gt; &lt;span class="k"&gt;TABLE&lt;/span&gt; &lt;span class="n"&gt;materials&lt;/span&gt; &lt;span class="p"&gt;(&lt;/span&gt;
    &lt;span class="n"&gt;id&lt;/span&gt; &lt;span class="nb"&gt;SERIAL&lt;/span&gt; &lt;span class="k"&gt;PRIMARY&lt;/span&gt; &lt;span class="k"&gt;KEY&lt;/span&gt;&lt;span class="p"&gt;,&lt;/span&gt;
    &lt;span class="n"&gt;name&lt;/span&gt; &lt;span class="nb"&gt;VARCHAR&lt;/span&gt;&lt;span class="p"&gt;(&lt;/span&gt;&lt;span class="mi"&gt;50&lt;/span&gt;&lt;span class="p"&gt;)&lt;/span&gt; &lt;span class="k"&gt;UNIQUE&lt;/span&gt; &lt;span class="k"&gt;NOT&lt;/span&gt; &lt;span class="k"&gt;NULL&lt;/span&gt;
&lt;span class="p"&gt;);&lt;/span&gt;

&lt;span class="c1"&gt;-- Product category-specific attributes&lt;/span&gt;
&lt;span class="k"&gt;CREATE&lt;/span&gt; &lt;span class="k"&gt;TABLE&lt;/span&gt; &lt;span class="n"&gt;shirts&lt;/span&gt; &lt;span class="p"&gt;(&lt;/span&gt;
    &lt;span class="n"&gt;product_id&lt;/span&gt; &lt;span class="nb"&gt;INT&lt;/span&gt; &lt;span class="k"&gt;PRIMARY&lt;/span&gt; &lt;span class="k"&gt;KEY&lt;/span&gt; &lt;span class="k"&gt;REFERENCES&lt;/span&gt; &lt;span class="n"&gt;products&lt;/span&gt;&lt;span class="p"&gt;(&lt;/span&gt;&lt;span class="n"&gt;id&lt;/span&gt;&lt;span class="p"&gt;)&lt;/span&gt; &lt;span class="k"&gt;ON&lt;/span&gt; &lt;span class="k"&gt;DELETE&lt;/span&gt; &lt;span class="k"&gt;CASCADE&lt;/span&gt;&lt;span class="p"&gt;,&lt;/span&gt;
    &lt;span class="n"&gt;color_id&lt;/span&gt; &lt;span class="nb"&gt;INT&lt;/span&gt; &lt;span class="k"&gt;NOT&lt;/span&gt; &lt;span class="k"&gt;NULL&lt;/span&gt; &lt;span class="k"&gt;REFERENCES&lt;/span&gt; &lt;span class="n"&gt;colors&lt;/span&gt;&lt;span class="p"&gt;(&lt;/span&gt;&lt;span class="n"&gt;id&lt;/span&gt;&lt;span class="p"&gt;),&lt;/span&gt;
    &lt;span class="n"&gt;size_id&lt;/span&gt; &lt;span class="nb"&gt;INT&lt;/span&gt; &lt;span class="k"&gt;NOT&lt;/span&gt; &lt;span class="k"&gt;NULL&lt;/span&gt; &lt;span class="k"&gt;REFERENCES&lt;/span&gt; &lt;span class="n"&gt;sizes&lt;/span&gt;&lt;span class="p"&gt;(&lt;/span&gt;&lt;span class="n"&gt;id&lt;/span&gt;&lt;span class="p"&gt;),&lt;/span&gt;
    &lt;span class="n"&gt;material_id&lt;/span&gt; &lt;span class="nb"&gt;INT&lt;/span&gt; &lt;span class="k"&gt;REFERENCES&lt;/span&gt; &lt;span class="n"&gt;materials&lt;/span&gt;&lt;span class="p"&gt;(&lt;/span&gt;&lt;span class="n"&gt;id&lt;/span&gt;&lt;span class="p"&gt;)&lt;/span&gt;
&lt;span class="p"&gt;);&lt;/span&gt;

&lt;span class="k"&gt;CREATE&lt;/span&gt; &lt;span class="k"&gt;TABLE&lt;/span&gt; &lt;span class="n"&gt;boots&lt;/span&gt; &lt;span class="p"&gt;(&lt;/span&gt;
    &lt;span class="n"&gt;product_id&lt;/span&gt; &lt;span class="nb"&gt;INT&lt;/span&gt; &lt;span class="k"&gt;PRIMARY&lt;/span&gt; &lt;span class="k"&gt;KEY&lt;/span&gt; &lt;span class="k"&gt;REFERENCES&lt;/span&gt; &lt;span class="n"&gt;products&lt;/span&gt;&lt;span class="p"&gt;(&lt;/span&gt;&lt;span class="n"&gt;id&lt;/span&gt;&lt;span class="p"&gt;)&lt;/span&gt; &lt;span class="k"&gt;ON&lt;/span&gt; &lt;span class="k"&gt;DELETE&lt;/span&gt; &lt;span class="k"&gt;CASCADE&lt;/span&gt;&lt;span class="p"&gt;,&lt;/span&gt;
    &lt;span class="n"&gt;color_id&lt;/span&gt; &lt;span class="nb"&gt;INT&lt;/span&gt; &lt;span class="k"&gt;NOT&lt;/span&gt; &lt;span class="k"&gt;NULL&lt;/span&gt; &lt;span class="k"&gt;REFERENCES&lt;/span&gt; &lt;span class="n"&gt;colors&lt;/span&gt;&lt;span class="p"&gt;(&lt;/span&gt;&lt;span class="n"&gt;id&lt;/span&gt;&lt;span class="p"&gt;),&lt;/span&gt;
    &lt;span class="n"&gt;size_id&lt;/span&gt; &lt;span class="nb"&gt;INT&lt;/span&gt; &lt;span class="k"&gt;NOT&lt;/span&gt; &lt;span class="k"&gt;NULL&lt;/span&gt; &lt;span class="k"&gt;REFERENCES&lt;/span&gt; &lt;span class="n"&gt;sizes&lt;/span&gt;&lt;span class="p"&gt;(&lt;/span&gt;&lt;span class="n"&gt;id&lt;/span&gt;&lt;span class="p"&gt;),&lt;/span&gt;
    &lt;span class="n"&gt;width&lt;/span&gt; &lt;span class="nb"&gt;VARCHAR&lt;/span&gt;&lt;span class="p"&gt;(&lt;/span&gt;&lt;span class="mi"&gt;10&lt;/span&gt;&lt;span class="p"&gt;)&lt;/span&gt; &lt;span class="k"&gt;CHECK&lt;/span&gt; &lt;span class="p"&gt;(&lt;/span&gt;&lt;span class="n"&gt;width&lt;/span&gt; &lt;span class="k"&gt;IN&lt;/span&gt; &lt;span class="p"&gt;(&lt;/span&gt;&lt;span class="s1"&gt;'Narrow'&lt;/span&gt;&lt;span class="p"&gt;,&lt;/span&gt; &lt;span class="s1"&gt;'Medium'&lt;/span&gt;&lt;span class="p"&gt;,&lt;/span&gt; &lt;span class="s1"&gt;'Wide'&lt;/span&gt;&lt;span class="p"&gt;))&lt;/span&gt;
&lt;span class="p"&gt;);&lt;/span&gt;

&lt;span class="c1"&gt;-- Indexes for common queries (from RA analysis)&lt;/span&gt;
&lt;span class="k"&gt;CREATE&lt;/span&gt; &lt;span class="k"&gt;INDEX&lt;/span&gt; &lt;span class="n"&gt;idx_products_category&lt;/span&gt; &lt;span class="k"&gt;ON&lt;/span&gt; &lt;span class="n"&gt;products&lt;/span&gt;&lt;span class="p"&gt;(&lt;/span&gt;&lt;span class="n"&gt;category_id&lt;/span&gt;&lt;span class="p"&gt;);&lt;/span&gt;
&lt;span class="k"&gt;CREATE&lt;/span&gt; &lt;span class="k"&gt;INDEX&lt;/span&gt; &lt;span class="n"&gt;idx_boots_color_size&lt;/span&gt; &lt;span class="k"&gt;ON&lt;/span&gt; &lt;span class="n"&gt;boots&lt;/span&gt;&lt;span class="p"&gt;(&lt;/span&gt;&lt;span class="n"&gt;color_id&lt;/span&gt;&lt;span class="p"&gt;,&lt;/span&gt; &lt;span class="n"&gt;size_id&lt;/span&gt;&lt;span class="p"&gt;);&lt;/span&gt;
&lt;/code&gt;&lt;/pre&gt;

&lt;/div&gt;



&lt;h2&gt;
  
  
  Conclusion
&lt;/h2&gt;

&lt;p&gt;Now maybe in some cases (or even in this one) there are better ways to design the schema, but you really can't design a bad one at least after going through this tedious process and I think that's a huge win.&lt;/p&gt;

&lt;p&gt;Was it fun? No.&lt;br&gt;&lt;br&gt;
Was it as rewarding as I expected it to be? Also no.&lt;/p&gt;

&lt;p&gt;But it worked. It removed guesswork, exposed bad assumptions, and left me with a database design I actually trust.&lt;/p&gt;

</description>
      <category>database</category>
      <category>algebra</category>
      <category>backend</category>
      <category>sql</category>
    </item>
    <item>
      <title>How I created my first Flutter Animation</title>
      <dc:creator>Piyush Pradhan</dc:creator>
      <pubDate>Tue, 01 Dec 2020 15:21:28 +0000</pubDate>
      <link>https://dev.to/piyushpradhan_314/how-i-created-my-first-flutter-animation-k2p</link>
      <guid>https://dev.to/piyushpradhan_314/how-i-created-my-first-flutter-animation-k2p</guid>
      <description>&lt;p&gt;No matter how good the UI of an app or website is, you can always make it better by adding some really cool animations and transitions, and that is why I started learning about Flutter Animations and that’s what this blog is about, how I learned the basics of animation in Flutter. &lt;/p&gt;

&lt;h4&gt;
  
  
  Creating an Anim… Well, Step - 1!
&lt;/h4&gt;

&lt;p&gt;First we need to create an &lt;code&gt;AnimationController&lt;/code&gt;. &lt;/p&gt;

&lt;p&gt;What is an &lt;code&gt;AnimationController&lt;/code&gt;, you ask? It is a special Animation object that generates a value whenever the device is ready for a new frame. By default, it produces numbers ranging from 0.0 to 1.0 during a given duration. &lt;br&gt;
It does have several methods, about which we’ll be talking about as we go further. But first things first, let’s declare and initialise our &lt;code&gt;AnimationController&lt;/code&gt;.&lt;br&gt;
&lt;/p&gt;

&lt;div class="highlight js-code-highlight"&gt;
&lt;pre class="highlight dart"&gt;&lt;code&gt;&lt;span class="kd"&gt;class&lt;/span&gt; &lt;span class="nc"&gt;AnimationExample&lt;/span&gt; &lt;span class="kd"&gt;extends&lt;/span&gt; &lt;span class="n"&gt;StatefulWidget&lt;/span&gt; &lt;span class="p"&gt;{&lt;/span&gt;
  &lt;span class="nd"&gt;@override&lt;/span&gt; 
  &lt;span class="n"&gt;_AnimationExample&lt;/span&gt; &lt;span class="n"&gt;createState&lt;/span&gt;&lt;span class="p"&gt;()&lt;/span&gt; &lt;span class="o"&gt;=&lt;/span&gt;&lt;span class="p"&gt;&amp;gt;&lt;/span&gt; &lt;span class="n"&gt;_AnimationExampleState&lt;/span&gt;&lt;span class="p"&gt;();&lt;/span&gt; 
&lt;span class="p"&gt;}&lt;/span&gt;

&lt;span class="kd"&gt;class&lt;/span&gt; &lt;span class="nc"&gt;_AnimationExampleState&lt;/span&gt; &lt;span class="kd"&gt;extends&lt;/span&gt; &lt;span class="n"&gt;State&lt;/span&gt;&lt;span class="p"&gt;&amp;lt;&lt;/span&gt;&lt;span class="n"&gt;AnimationExample&lt;/span&gt;&lt;span class="p"&gt;&amp;gt;&lt;/span&gt; &lt;span class="k"&gt;with&lt;/span&gt; &lt;span class="n"&gt;SingleTickerProviderStateMixin&lt;/span&gt; &lt;span class="p"&gt;{&lt;/span&gt;

  &lt;span class="n"&gt;AnimationController&lt;/span&gt; &lt;span class="n"&gt;_animationController&lt;/span&gt;&lt;span class="p"&gt;;&lt;/span&gt; 
  &lt;span class="n"&gt;Animation&lt;/span&gt;&lt;span class="p"&gt;&amp;lt;&lt;/span&gt;&lt;span class="kt"&gt;double&lt;/span&gt;&lt;span class="p"&gt;&amp;gt;&lt;/span&gt; &lt;span class="n"&gt;_slidingAnimation&lt;/span&gt;&lt;span class="p"&gt;;&lt;/span&gt; 

  &lt;span class="nd"&gt;@override&lt;/span&gt; 
  &lt;span class="kt"&gt;void&lt;/span&gt; &lt;span class="n"&gt;initState&lt;/span&gt;&lt;span class="p"&gt;()&lt;/span&gt; &lt;span class="p"&gt;{&lt;/span&gt;
    &lt;span class="k"&gt;super&lt;/span&gt;&lt;span class="o"&gt;.&lt;/span&gt;&lt;span class="na"&gt;initState&lt;/span&gt;&lt;span class="p"&gt;();&lt;/span&gt; 
    &lt;span class="n"&gt;_animationController&lt;/span&gt; &lt;span class="o"&gt;=&lt;/span&gt; &lt;span class="n"&gt;AnimationController&lt;/span&gt;&lt;span class="p"&gt;(&lt;/span&gt;
    &lt;span class="nl"&gt;duration:&lt;/span&gt; &lt;span class="n"&gt;Duration&lt;/span&gt;&lt;span class="p"&gt;(&lt;/span&gt;&lt;span class="nl"&gt;milliseconds:&lt;/span&gt; &lt;span class="mi"&gt;200&lt;/span&gt;&lt;span class="p"&gt;),&lt;/span&gt; &lt;span class="nl"&gt;vsync:&lt;/span&gt; &lt;span class="k"&gt;this&lt;/span&gt;&lt;span class="p"&gt;);&lt;/span&gt;
  &lt;span class="p"&gt;}&lt;/span&gt; 
&lt;span class="p"&gt;}&lt;/span&gt;
&lt;/code&gt;&lt;/pre&gt;

&lt;/div&gt;



&lt;p&gt;&lt;strong&gt;Note :-&lt;/strong&gt; while creating an AnimationController you might have noticed that you have to pass in a vsync argument, for that first add a mixin named &lt;code&gt;SingleTickerProviderStateMixin&lt;/code&gt; and then pass in reference of that class as vsync argument. &lt;br&gt;
If you want to learn more about vsync and TickerProvider refer to &lt;a href="https://dash-overflow.net/articles/why_vsync/" rel="noopener noreferrer"&gt;https://dash-overflow.net/articles/why_vsync/&lt;/a&gt;&lt;/p&gt;
&lt;h4&gt;
  
  
  What to do next ?
&lt;/h4&gt;

&lt;p&gt;Now let’s go ahead and create the animation!&lt;br&gt;
As we had discussed earlier, by default, the animationController produces numbers ranging from 0.0 to 1.0, but we don’t always need fractional values, do we ? One way around that problem is to multiply the result by a suitable number, but it’s not a very good idea. So, flutter has this &lt;code&gt;TweenSequence&lt;/code&gt; which can be used to set a list of custom tween sequences, now you can get sequences of tweens which produce double, int, and other values.&lt;br&gt;
&lt;/p&gt;

&lt;div class="highlight js-code-highlight"&gt;
&lt;pre class="highlight dart"&gt;&lt;code&gt;&lt;span class="nd"&gt;@override&lt;/span&gt;
&lt;span class="kt"&gt;void&lt;/span&gt; &lt;span class="nf"&gt;initState&lt;/span&gt;&lt;span class="p"&gt;()&lt;/span&gt; &lt;span class="p"&gt;{&lt;/span&gt;
  &lt;span class="p"&gt;.&lt;/span&gt;
  &lt;span class="p"&gt;.&lt;/span&gt;
  &lt;span class="n"&gt;_slidingAnimation&lt;/span&gt; &lt;span class="o"&gt;=&lt;/span&gt; &lt;span class="n"&gt;TweenSequence&lt;/span&gt;&lt;span class="p"&gt;(&lt;/span&gt;
    &lt;span class="p"&gt;&amp;lt;&lt;/span&gt;&lt;span class="n"&gt;TweenSequenceItem&lt;/span&gt;&lt;span class="p"&gt;&amp;lt;&lt;/span&gt;&lt;span class="kt"&gt;double&lt;/span&gt;&lt;span class="p"&gt;&amp;gt;&amp;gt;[&lt;/span&gt;
      &lt;span class="n"&gt;TweenSequence&lt;/span&gt;&lt;span class="p"&gt;&amp;lt;&lt;/span&gt;&lt;span class="kt"&gt;double&lt;/span&gt;&lt;span class="p"&gt;&amp;gt;(&lt;/span&gt;
        &lt;span class="nl"&gt;tween:&lt;/span&gt; &lt;span class="n"&gt;Tween&lt;/span&gt;&lt;span class="p"&gt;&amp;lt;&lt;/span&gt;&lt;span class="kt"&gt;double&lt;/span&gt;&lt;span class="p"&gt;&amp;gt;(&lt;/span&gt;&lt;span class="nl"&gt;begin:&lt;/span&gt; &lt;span class="mi"&gt;0&lt;/span&gt;&lt;span class="p"&gt;,&lt;/span&gt; &lt;span class="nl"&gt;end:&lt;/span&gt; &lt;span class="mi"&gt;100&lt;/span&gt;&lt;span class="p"&gt;),&lt;/span&gt; 
        &lt;span class="nl"&gt;weight:&lt;/span&gt; &lt;span class="mi"&gt;100&lt;/span&gt;&lt;span class="p"&gt;,&lt;/span&gt; 
      &lt;span class="p"&gt;),&lt;/span&gt;
    &lt;span class="p"&gt;]&lt;/span&gt;
  &lt;span class="p"&gt;)&lt;/span&gt;&lt;span class="o"&gt;.&lt;/span&gt;&lt;span class="na"&gt;animate&lt;/span&gt;&lt;span class="p"&gt;(&lt;/span&gt;&lt;span class="n"&gt;_animationController&lt;/span&gt;&lt;span class="p"&gt;);&lt;/span&gt;
  &lt;span class="p"&gt;.&lt;/span&gt;
  &lt;span class="p"&gt;.&lt;/span&gt;
&lt;span class="p"&gt;}&lt;/span&gt;
&lt;/code&gt;&lt;/pre&gt;

&lt;/div&gt;



&lt;p&gt;No, we’re not done yet. We’ve created &lt;code&gt;AnimationController&lt;/code&gt; and an &lt;code&gt;Animation&lt;/code&gt; but the animation won’t start playing on its own. To set it off we use the &lt;code&gt;forward()&lt;/code&gt; method of &lt;code&gt;AnimationController&lt;/code&gt; to set it off, in forward direction or the &lt;code&gt;reverse()&lt;/code&gt; to play it backwards. &lt;/p&gt;

&lt;p&gt;And yes one more thing, whichever widget you want to animate, you must wrap it in an &lt;code&gt;AnimationBuilder&lt;/code&gt; widget.&lt;br&gt;
&lt;/p&gt;

&lt;div class="highlight js-code-highlight"&gt;
&lt;pre class="highlight dart"&gt;&lt;code&gt;&lt;span class="nd"&gt;@override&lt;/span&gt;
  &lt;span class="n"&gt;Widget&lt;/span&gt; &lt;span class="n"&gt;build&lt;/span&gt;&lt;span class="p"&gt;(&lt;/span&gt;&lt;span class="n"&gt;BuildContext&lt;/span&gt; &lt;span class="n"&gt;context&lt;/span&gt;&lt;span class="p"&gt;)&lt;/span&gt; &lt;span class="p"&gt;{&lt;/span&gt;
    &lt;span class="k"&gt;return&lt;/span&gt; &lt;span class="n"&gt;AnimatedBuilder&lt;/span&gt;&lt;span class="p"&gt;(&lt;/span&gt;
      &lt;span class="nl"&gt;animation:&lt;/span&gt; &lt;span class="n"&gt;_controller&lt;/span&gt;&lt;span class="p"&gt;,&lt;/span&gt;
        &lt;span class="nl"&gt;builder:&lt;/span&gt; &lt;span class="p"&gt;(&lt;/span&gt;&lt;span class="n"&gt;BuildContext&lt;/span&gt; &lt;span class="n"&gt;context&lt;/span&gt;&lt;span class="p"&gt;,&lt;/span&gt; &lt;span class="n"&gt;_&lt;/span&gt;&lt;span class="p"&gt;)&lt;/span&gt; &lt;span class="p"&gt;{&lt;/span&gt;
          &lt;span class="k"&gt;return&lt;/span&gt; &lt;span class="n"&gt;Padding&lt;/span&gt;&lt;span class="p"&gt;(&lt;/span&gt;
            &lt;span class="nl"&gt;padding:&lt;/span&gt; &lt;span class="n"&gt;EdgeInsets&lt;/span&gt;&lt;span class="o"&gt;.&lt;/span&gt;&lt;span class="na"&gt;only&lt;/span&gt;&lt;span class="p"&gt;(&lt;/span&gt;&lt;span class="nl"&gt;left:&lt;/span&gt; &lt;span class="n"&gt;_slidingAnimation&lt;/span&gt;&lt;span class="o"&gt;.&lt;/span&gt;&lt;span class="na"&gt;value&lt;/span&gt;&lt;span class="p"&gt;),&lt;/span&gt; 
            &lt;span class="nl"&gt;child:&lt;/span&gt; &lt;span class="n"&gt;IconButton&lt;/span&gt; &lt;span class="p"&gt;(&lt;/span&gt;
              &lt;span class="nl"&gt;icon:&lt;/span&gt; &lt;span class="n"&gt;Icon&lt;/span&gt;&lt;span class="p"&gt;(&lt;/span&gt;&lt;span class="n"&gt;Icons&lt;/span&gt;&lt;span class="o"&gt;.&lt;/span&gt;&lt;span class="na"&gt;someRandomIcon&lt;/span&gt;&lt;span class="p"&gt;),&lt;/span&gt; 
              &lt;span class="nl"&gt;onPressed:&lt;/span&gt; &lt;span class="p"&gt;()&lt;/span&gt; &lt;span class="p"&gt;{&lt;/span&gt;
              &lt;span class="n"&gt;_animationController&lt;/span&gt;&lt;span class="o"&gt;.&lt;/span&gt;&lt;span class="na"&gt;forward&lt;/span&gt;&lt;span class="p"&gt;();&lt;/span&gt; 
              &lt;span class="p"&gt;}&lt;/span&gt;
            &lt;span class="p"&gt;)&lt;/span&gt;
         &lt;span class="p"&gt;);&lt;/span&gt;
       &lt;span class="p"&gt;}&lt;/span&gt;
    &lt;span class="p"&gt;);&lt;/span&gt;
  &lt;span class="p"&gt;}&lt;/span&gt;
&lt;/code&gt;&lt;/pre&gt;

&lt;/div&gt;



&lt;p&gt;And that is how I made my first animation! It is a very basic animation so, I tried making something a bit more interesting than an icon sliding across the screen, and… &lt;/p&gt;

&lt;p&gt;&lt;a href="https://media.dev.to/dynamic/image/width=800%2Cheight=%2Cfit=scale-down%2Cgravity=auto%2Cformat=auto/https%3A%2F%2Fi.imgur.com%2Fpyi02xU.gif" class="article-body-image-wrapper"&gt;&lt;img src="https://media.dev.to/dynamic/image/width=800%2Cheight=%2Cfit=scale-down%2Cgravity=auto%2Cformat=auto/https%3A%2F%2Fi.imgur.com%2Fpyi02xU.gif" alt="img"&gt;&lt;/a&gt;&lt;/p&gt;

&lt;p&gt;If you have some suggestions, feel free to share. &lt;br&gt;
Thank you!&lt;/p&gt;

</description>
      <category>dart</category>
      <category>flutter</category>
      <category>todayilearned</category>
      <category>codenewbie</category>
    </item>
  </channel>
</rss>
