# How to design a scalable DB Schema I used to think database design was mostly about knowing SQL. You know: ```sql 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: ```text User Post Comment Tag ``` Now don't immediately start writing SQL. First ask: > How are these things related? Maybe: ```text 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: ```text 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. ```text User 1 ───────── N Post ``` For example: ```text 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: ```text users ----- id name post_id ``` But immediately we have a problem. Which post should `post_id` contain? ```text Post 1? Post 2? Post 3? Post 4? ``` One column cannot naturally represent an arbitrary number of posts. So instead: ```json users ----- id name posts ----- id title user_id ``` Now: ```text posts.user_id → users.id ``` And everything makes sense. ```text 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: ```text Post → User ``` and therefore: ```text posts.user_id ``` We don't need: ```text users.post_ids ``` because we can derive a user's posts with: ```sql 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: ```python 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: ```text User 1 ``` has: ```text Post 10 Post 20 Post 30 ``` What should: ```text users.post_id ``` contain? If it contains `10`, what about `20` and `30`? And now we can also create inconsistent data: ```text users.post_id = 10 ``` but: ```text 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: ```text User N ───── N Course ``` A user can enroll in many courses. And a course can have many users. For example: ```text Abhinav ├── Java ├── DSA └── System Design Rahul ├── Java └── DSA ``` We can't solve this with: ```text users.course_id ``` because one user can have multiple courses. And we can't simply put: ```text courses.user_id ``` because one course has multiple users. So we create a junction table: ```text users ----- id courses ------- id user_courses ------------ user_id course_id ``` Now: ```text User 1 ─── N UserCourse N ─── 1 Course ``` This gives us: ```text 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: ```text user_courses ------------ user_id course_id joined_at status progress ``` Now the relationship itself has information. For example: ```text 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: ```text 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: ```text 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: ```text users ----- id name profiles -------- id user_id bio avatar ``` where: ```text profiles.user_id → users.id ``` The profile points to the user. This is natural because: ```text 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: ```text profiles.user_id FK ``` What's stopping this? ```text profile_id user_id 1 100 2 100 3 100 ``` Now User `100` has three profiles. That's no longer `1:1`. It's: ```text User 1 ───── N Profile ``` So for a true one-to-one relationship, we need: ```text user_id UNIQUE ``` Therefore: ```text profiles -------- id user_id FK UNIQUE bio avatar ``` Now the database itself enforces: ```text 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: ```text 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: ```text email_addresses --------------- id user_id FK UNIQUE email ``` This is perfectly natural. The relationship is: ```text 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: ```text 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: ```text Profile belongs to User. ``` That naturally suggests: ```text profile.user_id ``` Another example: ```text User references a primary address. ``` That may suggest: ```text 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: ```text 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? ```text 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: ```text users ----- id name preferences JSON ``` For example: ```json { "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: ```text User ├── name ├── preferences ├── settings └── social_links ``` These can often be treated as attributes. But: ```text User ├── Orders ├── Payments ├── Subscriptions └── Comments ``` These are clearly separate entities. * * * # 16\. JSON Is Great for Aggregate Data Suppose we have: ```text users ----- id name preferences JSON ``` And preferences look like: ```json { "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: ```text users ----- id preferences JSONB ``` and query inside the JSON. Conceptually: ```sql 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: ```text 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: ```text users ----- id theme language notifications_enabled preferences JSON ``` Now: ```text theme language notifications_enabled ``` are easy to index and query. And the less important/flexible configuration can remain in: ```text 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: ```text Home: Darbhanga, Bihar Office: Patna, Bihar ``` We could store: ```json { "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: ```text users ----- id name addresses --------- id user_id FK type street city state pincode ``` Now: ```text User 1 ───── N Address ``` and: ```text addresses.user_id → users.id ``` This is much more flexible. * * * # 20\. Multiple Values Do NOT Automatically Mean JSON This is worth remembering. Suppose: ```text User → Tags ``` You could store: ```json ["java", "backend", "cloud"] ``` inside User. Maybe that's perfectly fine. But if Tags become entities: ```text tags ----- id name description created_at ``` and users can share tags: ```text User N ───── N Tag ``` then we need: ```text 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: ```text User → Preferences ``` If your application almost always does: ```text Get User ↓ Get Preferences ``` then embedding can make a lot of sense. But if your application constantly does: ```text 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: ```text 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: ```text Identity Service ↓ PostgreSQL Mail Service ↓ MongoDB ``` For example, imagine Invoy. The core business data could be: ```text PostgreSQL ----------- users organizations domains api_keys subscriptions webhooks ``` While the mail system might have: ```text 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: ```text Business Domain ↓ Service Boundary ↓ Data Ownership ↓ Database ``` For example: ```text Invoy │ ┌───────────┼───────────┐ ↓ ↓ ↓ Identity Mail Billing │ │ │ PostgreSQL MongoDB PostgreSQL ``` Each service owns its own data. The Mail Service might know: ```text 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: ```sql FOREIGN KEY (user_id) REFERENCES users(id) ``` The database can enforce that relationship. But suppose: ```text PostgreSQL users ``` and: ```text MongoDB emails ``` Now: ```text emails.userId = 123 ``` can logically refer to: ```text 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: ```text 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: ```text email_count = 100 ``` in PostgreSQL. But actual emails are in MongoDB. Then a new email arrives. You have to do: ```text MongoDB: insert email PostgreSQL: increment email_count ``` What if Mongo succeeds but PostgreSQL fails? Now: ```text 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: ```text Service A ──┐ ├── PostgreSQL Service B ──┘ ``` And that's completely fine. You don't need: ```text 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 ```text User Post Comment Tag ``` ### Step 2 — Identify cardinality ```text User 1:N Post Post 1:N Comment Post N:N Tag ``` ### Step 3 — Decide ownership/dependency ```text Post belongs to User Comment belongs to Post ``` ### Step 4 — Place foreign keys ```text posts.user_id comments.post_id ``` ### Step 5 — For N:N, create a junction table ```text 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: ```text UNIQUE ``` If something must exist: ```text FOREIGN KEY ``` If something is queried frequently: ```text 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. ```text 1 : N User ─────────< Post ↑ FK ``` **FK usually goes on the N side.** * * * ```text N : N User ───< UserCourse >─── Course ↑ FKs ``` **Use a junction table.** * * * ```text 1 : 1 User ───── Profile ↑ FK + UNIQUE ``` **If Profile is the dependent entity, put the FK there and make it UNIQUE.** * * * ```text Parent │ └── simple/flexible data ↓ JSON ``` **Use JSON when the data is tightly coupled to the parent and doesn't need independent relational behavior.** * * * ```text Parent │ └── independent entity ↓ Separate table ``` **Use a table when the data has its own identity, lifecycle, relationships, indexing, constraints, or independent querying needs.** * * * ```text 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. ```text 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: ```sql 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.**