I used to think database design was mostly about knowing SQL.

You know:

CREATE TABLE users (...);
CREATE TABLE posts (...);

Then add a few foreign keys, write some joins, and you're done.

But after designing a few real systems, I realized that writing the SQL is probably the easy part.

The difficult part is deciding:

Where should the foreign key actually go?
Should this be a separate table or JSON?
When is a relationship 1:1, 1:N, or N:N?
Why does the foreign key go on one side and not the other?
If I'm using two databases, how do they even relate?
Should I use PostgreSQL, MongoDB, Turso, or something else?

And honestly, these questions can get confusing very quickly.

So in this article, I want to build a practical mental model for database design — not by memorizing rules, but by understanding why those rules exist.

1. First: Stop Thinking About Tables

Before creating a table, identify the entities in your system.

Suppose we're building a blogging application.

We might have:

User
Post
Comment
Tag

Now don't immediately start writing SQL.

First ask:

How are these things related?

Maybe:

User 1 ───── N Post

Post 1 ───── N Comment

Post N ───── N Tag

Now the database design becomes much easier.

Because the schema is basically a representation of these relationships.

2. The Three Relationships You Need to Understand

Most of the confusion starts around these three:

1 : 1
1 : N
N : N

Let's understand each one properly.

3. One-to-Many: The Easiest One

Suppose one user can create multiple posts.

User 1 ───────── N Post

For example:

Abhinav
  │
  ├── Post 1
  ├── Post 2
  ├── Post 3
  └── Post 4

Now where should the foreign key go?

My initial confusion used to be:

Should I keep post_id inside User?

Something like:

users
-----
id
name
post_id

But immediately we have a problem.

Which post should post_id contain?

Post 1?
Post 2?
Post 3?
Post 4?

One column cannot naturally represent an arbitrary number of posts.

So instead:

users
-----
id
name


posts
-----
id
title
user_id

Now:

posts.user_id → users.id

And everything makes sense.

User
  │
  │ 1
  │
  ├────────── Post
  ├────────── Post
  ├────────── Post
  └────────── Post
                N

So the basic rule is:

In a 1:N relationship, the foreign key normally goes on the N side.

Or even simpler:

The many side points toward the one side.

4. Why Does the Foreign Key Go on the Many Side?

This becomes clearer if we think about dependency.

A post needs to know:

"Which user does this post belong to?"

So:

Post → User

and therefore:

posts.user_id

We don't need:

users.post_ids

because we can derive a user's posts with:

SELECT *
FROM posts
WHERE user_id = 123;

This is an important concept:

A relationship doesn't need to be physically stored in both directions.

The database can derive the reverse direction using a query.

5. Don't Store the Same Relationship on Both Sides

You might sometimes see something like:

users
-----
id
post_id


posts
-----
id
user_id

At first this might look convenient.

But now we have two representations of the same relationship.

Imagine:

User 1

has:

Post 10
Post 20
Post 30

What should:

users.post_id

contain?

If it contains 10, what about 20 and 30?

And now we can also create inconsistent data:

users.post_id = 10

but:

posts.user_id = 2

Now which one is correct?

This is why a good general principle is:

One relationship should ideally have one authoritative representation.

Don't store redundant references unless you have a very specific reason.

6. Many-to-Many: One Foreign Key Is Not Enough

Now suppose we have:

User N ───── N Course

A user can enroll in many courses.

And a course can have many users.

For example:

Abhinav
 ├── Java
 ├── DSA
 └── System Design

Rahul
 ├── Java
 └── DSA

We can't solve this with:

users.course_id

because one user can have multiple courses.

And we can't simply put:

courses.user_id

because one course has multiple users.

So we create a junction table:

users
-----
id


courses
-------
id


user_courses
------------
user_id
course_id

Now:

User 1 ─── N UserCourse N ─── 1 Course

This gives us:

User N ───── N Course

without storing arrays of IDs inside either table.

7. The Junction Table Can Have Its Own Data

This is another reason many-to-many relationships are important.

Suppose:

user_courses
------------
user_id
course_id
joined_at
status
progress

Now the relationship itself has information.

For example:

Abhinav
   ↓
DSA
   ↓
progress = 72%

The progress doesn't belong purely to the User.

It doesn't belong purely to the Course either.

It belongs to:

the relationship between this User and this Course.

That's exactly what a junction table represents.

8. Now the Confusing One: One-to-One

This is where things get interesting.

Suppose:

User 1 ───── 1 Profile

A user has one profile.

A profile belongs to one user.

Now where should the foreign key go?

This is where I initially had the same question:

"There is only one on both sides. So how do I decide?"

The answer is not:

"Always put it on the left."

or:

"Always put it on the right."

Instead, we need to understand the semantics of the relationship.

9. Ask: Which Entity Is the Dependent One?

Suppose:

User
Profile

A profile is basically an extension of a user.

If the user disappears, does the profile still have much meaning?

Usually, no.

So we can model:

users
-----
id
name


profiles
--------
id
user_id
bio
avatar

where:

profiles.user_id → users.id

The profile points to the user.

This is natural because:

Profile belongs to User

So a useful default rule is:

In a 1:1 relationship, if one entity is clearly a dependent/extension of the other, put the foreign key on the dependent side.

10. But There Is One More Thing: UNIQUE

Suppose we only do:

profiles.user_id FK

What's stopping this?

profile_id    user_id

1             100
2             100
3             100

Now User 100 has three profiles.

That's no longer 1:1.

It's:

User 1 ───── N Profile

So for a true one-to-one relationship, we need:

user_id UNIQUE

Therefore:

profiles
--------
id
user_id FK UNIQUE
bio
avatar

Now the database itself enforces:

One User → One Profile

This is an important lesson:

A foreign key tells us what can be referenced. UNIQUE helps enforce the one-to-one cardinality.

11. What If Both Entities Are Independent?

Now let's make the 1:1 problem slightly harder.

Suppose:

User 1 ───── 1 EmailAddress

There are actually two different domain models we could have.

Model A

EmailAddress is a dependent object owned by User.

Then:

email_addresses
---------------
id
user_id FK UNIQUE
email

This is perfectly natural.

The relationship is:

EmailAddress → User

Model B

EmailAddress is an independent entity.

Imagine the system already has an EmailAddress entity, and a user simply references one as their primary email.

Then we could have:

users
-----
id
primary_email_id FK UNIQUE


email_addresses
---------------
id
email

Now the semantics are different.

We're saying:

User references an EmailAddress.

We're not saying:

EmailAddress is a child record of User.

Both can represent a 1:1 relationship.

So this gives us an important correction:

There is no universal law saying the FK must always go on one particular side in every 1:1 relationship.

We decide based on ownership, dependency, and domain semantics.

12. A Useful Trick for 1:1 Relationships

When you're confused, describe the relationship in English.

For example:

Profile belongs to User.

That naturally suggests:

profile.user_id

Another example:

User references a primary address.

That may suggest:

user.primary_address_id

The sentence often makes the direction obvious.

13. Don't Confuse Cardinality With Existence

This is a subtle but very important point.

Suppose:

User 1 ───── 1 Address

The fact that Address can technically exist independently does not automatically make the relationship something other than 1:1.

These are separate questions:

Cardinality

How many can be associated?

1 : 1

Lifecycle / ownership

Who owns or depends on whom?

These are related concepts, but they aren't the same thing.

14. Now Let's Talk About JSON vs Separate Tables

This is another place where database design gets interesting.

Suppose every user has preferences.

We could store:

users
-----
id
name
preferences JSON

For example:

{
  "theme": "dark",
  "language": "en",
  "notifications": true
}

This can be a perfectly good design.

But when should we do this?

15. Think: Is This Data an Attribute or an Entity?

This is probably one of the most useful questions you can ask while designing a database.

Ask:

Is this just a property of the parent, or is this actually an entity of its own?

For example:

User
 ├── name
 ├── preferences
 ├── settings
 └── social_links

These can often be treated as attributes.

But:

User
 ├── Orders
 ├── Payments
 ├── Subscriptions
 └── Comments

These are clearly separate entities.

16. JSON Is Great for Aggregate Data

Suppose we have:

users
-----
id
name
preferences JSON

And preferences look like:

{
  "theme": "dark",
  "language": "en",
  "editor": {
    "fontSize": 14,
    "fontFamily": "JetBrains Mono"
  },
  "notifications": {
    "email": true,
    "marketing": false
  }
}

This can be a great use case for JSON.

Why?

Because these values:

belong to the user
don't need their own identity
are usually fetched with the user
don't have an independent lifecycle
can have a flexible structure

So JSON is reasonable.

17. But What If I Need to Query the JSON?

Suppose I ask:

"How many users have dark mode enabled?"

Does that automatically mean:

"I should create a separate preferences table?"

No.

That's a common mistake.

Modern databases such as PostgreSQL can query and index JSON/JSONB data.

So you can have:

users
-----
id
preferences JSONB

and query inside the JSON.

Conceptually:

SELECT COUNT(*)
FROM users
WHERE preferences->>'theme' = 'dark';

If this query matters, you can also design an appropriate index.

So:

"I need to query JSON" does not automatically mean "make a separate table."

18. When Does JSON Start Becoming Uncomfortable?

Suppose preferences become a major part of your application's querying system.

You now constantly need:

How many users prefer dark mode?

How many users use Hindi?

How many have notifications enabled?

How many users use font size > 16?

Give me all users with:
theme = dark
language = Hindi
marketing = false

At this point, these values are becoming important relational/query dimensions.

You might decide to model frequently queried fields as normal columns:

users
-----
id
theme
language
notifications_enabled
preferences JSON

Now:

theme
language
notifications_enabled

are easy to index and query.

And the less important/flexible configuration can remain in:

preferences JSON

This is called a hybrid approach, and it's often very practical.

19. Patna Example

Let's make this more real.

Suppose we're building an application for people in Bihar.

A user can have multiple addresses:

Home:
Darbhanga, Bihar

Office:
Patna, Bihar

We could store:

{
  "addresses": [
    {
      "type": "home",
      "city": "Darbhanga",
      "state": "Bihar",
      "pincode": "846004"
    },
    {
      "type": "office",
      "city": "Patna",
      "state": "Bihar",
      "pincode": "800001"
    }
  ]
}

inside the User.

This is not automatically wrong.

But now imagine the product asks:

"Give me all users whose office is in Patna."

Or:

"Find every delivery address in Patna."

Or:

"Show all orders delivered to this address."

Or:

"An address has its own verification status."

Now Address is becoming an entity.

So we'd probably move to:

users
-----
id
name


addresses
---------
id
user_id FK
type
street
city
state
pincode

Now:

User 1 ───── N Address

and:

addresses.user_id → users.id

This is much more flexible.

20. Multiple Values Do NOT Automatically Mean JSON

This is worth remembering.

Suppose:

User → Tags

You could store:

["java", "backend", "cloud"]

inside User.

Maybe that's perfectly fine.

But if Tags become entities:

tags
-----
id
name
description
created_at

and users can share tags:

User N ───── N Tag

then we need:

user_tags
---------
user_id
tag_id

So the decision isn't:

"There are multiple values, therefore JSON."

The real question is:

"Are these values just part of the parent, or are they entities that need independent relational behavior?"

21. A Simple JSON vs Table Checklist

When deciding between JSON and a separate table, ask:

1. Does it have its own identity?

If yes → table becomes more attractive.

2. Is it queried independently?

If yes → table becomes more attractive.

3. Is it referenced by other entities?

If yes → table is usually better.

4. Does it have its own lifecycle?

If yes → table becomes more attractive.

5. Do I need indexes/constraints on its individual fields?

If yes → relational columns/table may be better.

6. Is it tightly coupled to the parent?

If yes → JSON/embedding may be attractive.

7. Is the structure flexible?

If yes → JSON becomes attractive.

8. Is the amount of data potentially huge or unbounded?

If yes → separate table usually becomes safer.

22. Database Design Is Also About Access Patterns

This is something I didn't appreciate initially.

You aren't designing a database only around:

"What data exists?"

You're also designing around:

"How will the application access this data?"

For example:

User → Preferences

If your application almost always does:

Get User
   ↓
Get Preferences

then embedding can make a lot of sense.

But if your application constantly does:

Find all users
where preference X = Y

then you need to think carefully about indexing, JSON querying, or relational modelling.

So:

Schema design is partly data modelling and partly access-pattern modelling.

23. Now Let's Talk About Multiple Databases

This is where things get even more interesting.

Suppose I have:

PostgreSQL
MongoDB
Turso
Redis

Should I start putting different tables into different databases?

No.

Please don't do this just because:

"Modern systems use multiple databases."

Start with the simplest architecture that works.

24. Why Would We Use Multiple Databases?

A strong reason could be:

Identity Service
        ↓
PostgreSQL

Mail Service
        ↓
MongoDB

For example, imagine Invoy.

The core business data could be:

PostgreSQL
-----------
users
organizations
domains
api_keys
subscriptions
webhooks

While the mail system might have:

MongoDB
-------
emails
email_content
email_events
threads

Now the separation starts making sense.

Why?

Because Mail is becoming its own business domain/service.

25. Separate Database Often Makes Sense Around a Service Boundary

This is a useful heuristic:

Business Domain
       ↓
Service Boundary
       ↓
Data Ownership
       ↓
Database

For example:

                 Invoy
                   │
       ┌───────────┼───────────┐
       ↓           ↓           ↓
   Identity       Mail       Billing
       │           │           │
   PostgreSQL    MongoDB    PostgreSQL

Each service owns its own data.

The Mail Service might know:

user_id = 123

but it doesn't need to own the actual User record.

26. Cross-Database Relationships Are Different

Inside PostgreSQL we can do:

FOREIGN KEY (user_id)
REFERENCES users(id)

The database can enforce that relationship.

But suppose:

PostgreSQL
users

and:

MongoDB
emails

Now:

emails.userId = 123

can logically refer to:

postgres.users.id = 123

but MongoDB isn't enforcing a normal relational foreign key to PostgreSQL.

This is an:

Application-level or logical reference.

So:

Postgres
User 123
   │
   │ logical ID
   ↓
MongoDB
Email.userId = 123

The application/service is responsible for maintaining consistency.

27. This Is Why Multiple Databases Increase Complexity

Suppose you store:

email_count = 100

in PostgreSQL.

But actual emails are in MongoDB.

Then a new email arrives.

You have to do:

MongoDB:
insert email

PostgreSQL:
increment email_count

What if Mongo succeeds but PostgreSQL fails?

Now:

MongoDB = 101 emails
PostgreSQL = 100 emails

You have a consistency problem.

This is why splitting databases isn't something we should do casually.

28. My Rule for Multiple Databases

Before introducing another database, ask:

Why can't the existing database handle this?

Good reasons include:

completely different workload
different consistency requirements
different data model
independent scaling needs
independent ownership/service boundary
specialized storage requirements

Bad reason:

"Because MongoDB is cool."

😂

29. A Service Boundary Is a Strong Signal, Not a Law

You might have:

Service A ──┐
            ├── PostgreSQL
Service B ──┘

And that's completely fine.

You don't need:

Service A → PostgreSQL
Service B → MongoDB

just because there are two services.

Especially early in a product, keeping one database can dramatically simplify:

transactions
migrations
debugging
local development
consistency
backups
deployment

You can split later when there is a real reason.

30. The Mental Model I Now Use

When designing a database, I try to follow this sequence.

Step 1 — Identify entities

User
Post
Comment
Tag

Step 2 — Identify cardinality

User 1:N Post
Post 1:N Comment
Post N:N Tag

Step 3 — Decide ownership/dependency

Post belongs to User
Comment belongs to Post

Step 4 — Place foreign keys

posts.user_id
comments.post_id

Step 5 — For N:N, create a junction table

post_tags
---------
post_id
tag_id

Step 6 — Decide JSON vs entity

Ask:

Is this an attribute or an independent entity?

Step 7 — Think about access patterns

Ask:

How will I query this data?

Step 8 — Add constraints and indexes

Don't just rely on application code.

If something must be unique:

UNIQUE

If something must exist:

FOREIGN KEY

If something is queried frequently:

INDEX

Step 9 — Only then think about multiple databases

Ask:

Does this data actually need a different storage system?

31. The Cheat Sheet

Here's the whole article compressed into one mental model.

1 : N

User ─────────< Post
                 ↑
                 FK

FK usually goes on the N side.

N : N

User ───< UserCourse >─── Course
             ↑
            FKs

Use a junction table.

1 : 1

User ───── Profile
             ↑
          FK + UNIQUE

If Profile is the dependent entity, put the FK there and make it UNIQUE.

Parent
  │
  └── simple/flexible data
          ↓
        JSON

Use JSON when the data is tightly coupled to the parent and doesn't need independent relational behavior.

Parent
  │
  └── independent entity
          ↓
      Separate table

Use a table when the data has its own identity, lifecycle, relationships, indexing, constraints, or independent querying needs.

Service A ── Database A
      │
      │ logical reference
      ↓
Service B ── Database B

Across database boundaries, relationships are usually logical/application-level references rather than normal foreign keys.

32. The Most Important Lesson

I think the biggest mistake beginners make is trying to memorize rules like:

"Foreign key always goes here."

or:

"JSON is bad."

or:

"Every relationship needs a table."

None of these are universally true.

Instead, ask questions.

What is the entity?

How many can exist?

Who owns it?

Who depends on whom?

Does it have its own identity?

How will I query it?

Does it need independent indexes?

Does another entity reference it?

How strongly coupled is it to the parent?

Does it need a different consistency model?

Does it actually need another database?

Once you start asking these questions, database design becomes much less about memorizing syntax and much more about modelling the actual system.

And that's the real skill.

You don't want to reach a point where you can write:

CREATE TABLE ...

really fast.

You want to reach a point where, when someone gives you a product requirement, you can sit down with a blank page and say:

"Okay. These are the entities. This is a 1:N. This one is N:N. This thing is just an attribute, so I'll keep it as JSON. This other thing is clearly an entity, so I'll give it a table. This relationship needs a unique constraint. And there's absolutely no reason for me to introduce another database yet."

That's database design.

Command Palette

Comments

More from this blog

1. First: Stop Thinking About Tables

2. The Three Relationships You Need to Understand

3. One-to-Many: The Easiest One

4. Why Does the Foreign Key Go on the Many Side?

5. Don't Store the Same Relationship on Both Sides

6. Many-to-Many: One Foreign Key Is Not Enough

7. The Junction Table Can Have Its Own Data

8. Now the Confusing One: One-to-One

9. Ask: Which Entity Is the Dependent One?

10. But There Is One More Thing: UNIQUE

11. What If Both Entities Are Independent?

Model A

Model B

12. A Useful Trick for 1:1 Relationships

13. Don't Confuse Cardinality With Existence

Cardinality

Lifecycle / ownership

14. Now Let's Talk About JSON vs Separate Tables

15. Think: Is This Data an Attribute or an Entity?

16. JSON Is Great for Aggregate Data

17. But What If I Need to Query the JSON?

18. When Does JSON Start Becoming Uncomfortable?

19. Patna Example

20. Multiple Values Do NOT Automatically Mean JSON

21. A Simple JSON vs Table Checklist

1. Does it have its own identity?

2. Is it queried independently?

3. Is it referenced by other entities?

4. Does it have its own lifecycle?

5. Do I need indexes/constraints on its individual fields?

6. Is it tightly coupled to the parent?

7. Is the structure flexible?

8. Is the amount of data potentially huge or unbounded?

22. Database Design Is Also About Access Patterns

23. Now Let's Talk About Multiple Databases

24. Why Would We Use Multiple Databases?

25. Separate Database Often Makes Sense Around a Service Boundary

26. Cross-Database Relationships Are Different

27. This Is Why Multiple Databases Increase Complexity

28. My Rule for Multiple Databases

29. A Service Boundary Is a Strong Signal, Not a Law

30. The Mental Model I Now Use

Step 1 — Identify entities

Step 2 — Identify cardinality

Step 3 — Decide ownership/dependency

Step 4 — Place foreign keys

Step 5 — For N:N, create a junction table

Step 6 — Decide JSON vs entity

Step 7 — Think about access patterns

Step 8 — Add constraints and indexes

Step 9 — Only then think about multiple databases

31. The Cheat Sheet

32. The Most Important Lesson