Skip to content

Normalized Tables vs JSONB

Designing the Order System Schema for a Food Ordering App: Tables vs JSONB

Intro

When designing a database schema for a food ordering app, one of the most important decisions is how to represent orders and their associated items.

Should we normalize the schema using separate tables for orders and order items? Or should we take a more flexible approach and store items inside a JSONB column on the order table itself?

Both approaches have pros and cons, and the choice depends on your requirements, priorities, and growth plans. Let’s break this down.

Option 1: Separate Tables

orders (

id SERIAL PRIMARY KEY,

customer_id INT REFERENCES customers(id),

status TEXT,

created_at TIMESTAMP DEFAULT now()

)


order_items (

id SERIAL PRIMARY KEY,

order_id INT REFERENCES orders(id),

menu_item_id INT REFERENCES menu_items(id),

name TEXT, -- snapshot

price NUMERIC(10,2), -- snapshot

quantity INT,

extras JSONB -- customizations

)


menu_items (

id SERIAL PRIMARY KEY,

name TEXT,

price NUMERIC(10,2),

available BOOLEAN

)