Google Sheet to relational schema: แผนการออกแบบทีละขั้นตอน

Why spreadsheets turn messy when they become a database

A spreadsheet is great for a small list. You can change columns on the fly, add notes anywhere, and fix problems by eye. That freedom starts to break down when the file becomes a shared source of truth.

As data grows, the same issues show up again and again. You see duplicates because there’s no single place to store a customer or product. You get conflicting values because two rows disagree about the same thing, like a phone number. Filtering and reporting become frustrating because some columns hide lists ("Tags", "Products", "Attendees") or mix formats ("$1,200", "1200", "1.2k").

Moving from a Google Sheet to a relational schema is about safety. A database forces clearer structure so you can query, validate, and update data without creating new contradictions.

A useful mental model: one row should represent one real thing. If a row represents a deal, a customer, and a list of products, updating any one of those later will be painful.

A quick test: does a single row ever need two values for the same field?

• One order has multiple products
• One project has multiple team members
• One customer has multiple addresses

If the answer is yes, it’s not a “wide row” problem. It’s a “separate table” problem. Once you model it cleanly, you can build forms and validation on top of it instead of relying on fragile manual edits.

Start by defining what the sheet actually means

A spreadsheet can look organized and still mean different things to different people. Before you convert a Google Sheet to a relational schema, agree on what the sheet is tracking.

Start with outcomes, not columns. What decisions should the data support: a weekly revenue report, a list of overdue tickets, a workflow that assigns follow-ups, or quick lookup during a customer call? If you can’t name a decision, that field often doesn’t belong in the database.

Next, pull out the nouns hiding in the headers and notes. These usually become your future tables: customers, orders, products, invoices, tickets, agents, locations. If a column mixes two nouns (like “Customer + Company”), you’re storing multiple things in one place.

Agree on definitions early

Small differences in meaning turn into big cleanups later. Get clear on basics:

• What counts as an “order” (a quote, a paid purchase, or both)?
• What is a “customer” (person, company, or either)?
• Can one order have multiple products?
• Can one email belong to multiple customers?
• What is “status” meant to show (current state or history)?

Example: if your sheet has one row per “Order” but the “Products” cell contains a comma-separated list, decide whether that row represents a checkout, a shipment, or an invoice. Each choice leads to a different schema.

Freeze a copy of the original sheet as read-only. You’ll use it to validate that the new tables still answer the same questions.

Clean the sheet so the structure is visible

Before converting a Google Sheet to a relational schema, make the sheet look like data, not a report. Databases need consistent rows and columns. Decorative layout hides patterns you need to model.

Remove layout tricks like merged cells, multiple header rows, and subtotals inside the data range. Keep one header row and then rows of records only. If you need totals, put them on a separate summary tab so they don’t get mixed into the real records.

Then make formats consistent across each column. A database can’t guess that “1/2/24”, “2024-02-01”, and “Feb 1” are the same date. The same applies to phone numbers, currency, and names. Pick one format and use it everywhere, even if it feels strict.

A short cleanup pass that usually pays off:

• Make sure each row represents one thing (one order, one customer, one ticket).
• Remove blank spacer rows and columns.
• Replace “N/A”, “-”, and empty strings with one rule you’ll keep.
• Mark which columns are calculated vs typed by a person.

Finally, flag any cell that contains multiple values, like “red, blue, green” in one column. Don’t fix the schema yet. Just mark those columns so you remember they’ll become separate rows later.

Identify repeating groups and fields that hide lists

The biggest warning sign in spreadsheet data modeling is repetition. Sheets often squeeze “more than one thing” into a single row by repeating columns or packing multiple values into one cell. That works for quick tracking, then breaks when you need filtering, reporting, or consistent updates.

Patterns that usually mean “this should be another table”

Scan for these shapes:

• Numbered columns like Item 1, Item 2, Item 3 or Phone 1, Phone 2.
• Repeated blocks like address fields duplicated for “Home” and “Work”.
• Cells with commas, line breaks, or “and” that combine values (for example, “Mouse, Keyboard, Monitor”).
• One column that mixes two concepts, like “Approved 2025-01-10” or “Alex (Manager)”.
• A row that represents two levels at once, like an Order row that also tries to store all Order Items.

Example: if your sales tracker uses Order ID, Customer, Product 1, Qty 1, Product 2, Qty 2, you’ll hit a wall. Some orders have 1 item, some have 8. The sheet either grows sideways forever or starts losing data. In a relational model, “Orders” becomes one table, and “Order Items” becomes another table with one row per product on the order.

For “lists in a cell”, treat each value as its own record. A cell that says “Email, SMS” usually means you need a separate table (or a join table) to track channels cleanly.

Mixed columns are quieter but just as risky. Split them early so each field stores one clear fact.

Create tables from the entities you found

Once you can name the real-world things in the sheet, turn each one into its own table. Your spreadsheet stops being one big grid and becomes a set of smaller, purposeful lists.

If a row mixes details about two different things, it probably needs two tables. A sales tracker row might include customer info (name, phone), order info (date, status), and product info (SKU, price). Customers don’t change every time an order changes, and products don’t depend on a single order. Splitting them prevents duplicate edits and mismatched values.

Before you finalize anything, write a one-sentence purpose for each table. If you can’t describe what a table represents without saying “and also”, it’s usually too broad.

A few practical rules:

• Keep attributes that describe the same thing and share the same lifecycle together (customer name and customer email).
• Move anything that can appear multiple times into its own table (multiple order items, multiple addresses).
• If a cell contains a list (comma-separated values, repeated columns), that’s a separate table.
• If two sets of fields change for different reasons, separate them (order status vs customer contact info).

Then name columns clearly and consistently. Prefer simple nouns and avoid vague labels like “Info” or “Details”.

Choose keys that stay stable over time

Pick a primary key for every table early. A good key is boring: it never changes, it’s always present, and it identifies one row and only one row.

Natural keys (real-world values) can work, but only if they’re truly stable. An SKU is often a good natural key because it’s meant to be permanent. Email addresses sound stable, but people change emails, share inboxes, and create duplicates like “john@” and “john.work@”. Names, phone numbers, and addresses change and aren’t guaranteed unique.

A safe default is an auto-generated ID (like customer_id, order_id). Keep the natural identifier as a normal field, and add a uniqueness rule when it fits your business rules. If an email changes, the customer_id stays the same and related orders still point to the right customer.

Simple key rules:

• Use an auto ID when the real-world identifier might change, be missing, or be reused.
• Use a natural key only when you control it and it’s designed to be permanent (for example, SKU).
• Mark fields as unique only when duplicates would be wrong.
• Allow NULL only when “unknown” is a valid state; otherwise require a value.
• Write down what “unique” means (unique per table, per company, or per time period).

Example: in a Contacts table, use contact_id as the primary key. Keep email unique only if your rule is one contact equals one email. Allow phone to be empty because not everyone shares it.

Map relationships without guessing

Most hard mistakes come from guessing how things relate. Use a simple rule: if one row “owns” many of something, that’s one-to-many. Put the foreign key on the “many” side.

Example: one Customer can have many Orders. The Orders table should store customer_id. If you keep a comma-separated list of order numbers inside Customers, duplicates and missing data show up quickly.

Many-to-many is the common spreadsheet trap. If one Order can include many Products and one Product can appear in many Orders, you need a join table (often called line items). It typically includes order_id, product_id, plus fields like quantity and the price at the time of purchase.

One-to-one relationships are rare. They make sense when the extra data is optional or kept separate for privacy or performance (for example, User and UserProfile). They’re a warning sign when you split a table just because the sheet had two tabs.

History needs its own structure. If values can change over time (status, price, address), avoid overwriting a single column. Store changes as rows in a history table so you can answer “what was true on that date?”

Normalize enough to prevent contradictions

A simple rule: store one fact in one place. If a customer’s phone number appears in five rows, someone will update four of them and miss the fifth.

Normalization in plain terms:

1NF, 2NF, 3NF in practical terms

First normal form (1NF) means each cell holds one value. If a column contains “red, blue, green” or “SKU1|SKU2|SKU3”, that’s a hidden list. Break it into rows in a related table.

Second normal form (2NF) shows up most in line items. If you have OrderItems and the key is (OrderID, ProductID), then fields like CustomerName don’t belong there. They depend on the order, not the product.

Third normal form (3NF) means non-key fields shouldn’t depend on other non-key fields. Example: if you store ZipCode and City, and City is determined by ZipCode, you risk mismatches.

A quick self-check:

• Could the same value be edited in more than one place?
• Would one change force you to update many other rows?
• Are you storing labels that can be derived from an ID?
• Are totals stored next to the raw rows that produce them?

When denormalizing is OK

Denormalize mainly for read-heavy reporting, and do it safely: treat the report table as a copy you can rebuild. Keep normalized tables as the source of truth.

For derived values like totals, balances, and status, don’t duplicate them unless you have a clear rule for recalculating. A practical approach is: store raw transactions, compute totals in queries, and only cache totals when performance demands it.

Common modeling traps that create future cleanups

Most “it worked in the sheet” problems come from meaning, not tools. The goal is to make every row say one clear thing, the same way, every time.

Common traps:

• Using names as IDs. “John Smith” isn’t a unique identifier, and names change. Use a generated ID (or a verified email or phone), and treat display names as labels.
• Packing lists into one cell. It looks simple, but it breaks searching, validation, and reporting. Lists belong in a related table.
• Mixing current state with history. A single Status column can’t tell you both the latest status and how it changed. If timing matters, store status changes as events with timestamps.
• Overloading one table to mean multiple things. A Contacts sheet that includes customers, vendors, and employees usually ends up with fields that only apply to some rows. Split by role, or keep a shared Person table and add role-specific tables.
• Ignoring required vs optional fields. If key fields can be blank, you’ll get rows that can’t join cleanly. Decide what’s required and enforce it early.

If your Orders table has columns like Item 1, Item 2, Item 3, you’re looking at a repeating group. Plan for an Orders table plus an OrderItems table.

Quick checklist before you commit to the schema

Before you lock the schema, do a final pass for clarity. Most database pain later comes from small shortcuts that felt harmless early on.

Ask whether each table answers one simple question. “Customers” should mean customers, not customers plus their latest order plus call notes. If you can’t describe a table in one short sentence, it’s mixing things.

Final checks:

• Can you point to the column (or set of columns) that uniquely identifies each row, even if names change?
• Do any cells contain more than one value (comma-separated tags, multiple emails, Item1/Item2 columns)? If yes, split into a child table.
• For each relationship, is it stored as an intentional foreign key? For many-to-many, do you have a join table?
• Do important fields have rules (required where missing data breaks the process, unique where duplicates would be harmful)?
• Can you update a fact (customer address, product price, employee role) in exactly one place?

Reality test: imagine someone enters the same customer twice with a slightly different spelling. If your schema makes that easy, add a better key or a uniqueness rule.

Example: turning a sales tracker sheet into clean tables

Picture a sales tracker where each row is a deal. It has columns like Customer Name, Customer Email, Deal Amount, Stage, Close Date, Products (a comma-separated list), and Notes (sometimes multiple notes in one cell).

That single row hides two repeating groups: products (one deal can include many products) and notes (one deal can have many notes). This is where conversions often go wrong, because lists inside cells are hard to query and easy to contradict.

A clean “after” model that matches how the work actually behaves:

• Customers (CustomerId, Name, Email)
• Deals (DealId, CustomerId, Amount, Stage, CloseDate)
• Products (ProductId, Name, SKU)
• DealProducts (DealId, ProductId, Quantity, UnitPrice)
• DealNotes (NoteId, DealId, NoteText, CreatedAt)

CustomerId, DealId, and ProductId are stable identifiers. DealProducts solves the many-to-many relationship: one deal can include many products, and one product can appear in many deals. DealNotes keeps notes separate, so you don’t end up with “Note 1, Note 2, Note 3” columns.

Before modeling, a report like “revenue by product” means splitting strings and hoping people typed names consistently. After modeling, it becomes a straightforward query over DealProducts joined to Deals and Products.

Next steps: move from schema to a working app

Once your schema looks right on paper, move it into a real database and test with real data. Don’t import everything at once. Load a small batch first, fix what breaks, then repeat.

A practical order that keeps risk low:

• Create the tables and relationships.
• Import 50 to 200 rows, verify totals, and spot-check records.
• Fix mapping issues (wrong columns, missing IDs, duplicates), then re-import.
• When it’s stable, load the rest.

Add validation rules early so messy spreadsheet habits don’t return. Make required fields truly required, limit allowed values (like status), validate formats (dates and emails), and use foreign keys so you can’t create an order for a customer that doesn’t exist.

Then stop using the sheet for updates. Protecting your data gets much easier when people have simple forms and clear workflows.

If you want to turn the schema into a working internal tool without writing code, AppMaster (appmaster.io) can help: you model tables and relationships visually, then generate a production-ready backend, web app, and native mobile apps from the same model.