Database basics for non-developers: tables, rows and relationships
A plain-English explanation of how a database is organized, with tables, rows, columns, keys and relationships worked through on a real small-business example.
Key Takeaways
- A table is one kind of thing. Customers, orders and products each get their own table. Each row is one customer, one order or one product, and each column is one fact about it.
- Every row needs a permanent ID. A primary key is a value that is unique, never empty and never changes. Names and emails make poor keys because people share names and change emails.
- Relationships are just copied IDs. An order row stores the customer’s ID, not the customer’s name and address. That one column is what links the two tables.
- Write each fact once. If a phone number lives in one place, changing it once updates every order, invoice and report that uses it.
- Rules belong in the database. Required fields, allowed values and valid links can be enforced by the database itself, so bad data is refused instead of cleaned up later.
Quick-Start Prioritization Framework
| Term | Plain meaning | Bakery example |
|---|---|---|
| Table | A list of one kind of thing | customers, orders, products |
| Row | One item in that list | Maria Lopez, order #1042 |
| Column | One fact every row has | email, pickup date, price |
| Primary key | The row’s permanent ID | customer id 57 |
| Foreign key | Another table’s ID stored here | orders.customer_id = 57 |
| Relationship | How two tables connect | one customer has many orders |
If you have ever kept a customer list in a spreadsheet, you already understand half of how a database works. The other half is the part spreadsheets let you skip: rules about what each column may hold, a permanent ID for every row, and links between lists so a fact is written down once and reused everywhere. This guide explains those ideas with one running example, a small bakery that takes orders, so you can read a database design, describe the one you need, and spot a bad one before it causes trouble.
What a database is, compared with a spreadsheet
A database is a program that stores data on a server and answers questions about it. Most business apps use a relational database, such as PostgreSQL, MySQL or SQL Server. PostgreSQL’s own tutorial puts it simply: a relation is “essentially a mathematical term for table.” So when people say relational database, picture a set of tables that know how they connect.
A spreadsheet looks similar, but it trusts you to keep things tidy. Any cell can hold anything, two people can overwrite each other, and nothing stops you typing “next Tuesday” in a date column. A database turns those habits into rules that every app, user and import has to follow.
Why the difference matters once you have an app
When a web or phone app saves something, it is writing a row into a database table. When someone signs in on another device and sees their data, the app is reading it back. If the structure is clear, the app is easy to change and reports are easy to build. If it is muddled, every new feature fights the old data.
Rows have no built-in order
In a spreadsheet, row 5 is always row 5. In a database there is no fixed order: PostgreSQL’s documentation notes that SQL “does not guarantee the order of the rows within the table.” You ask for an order when you need one, such as newest orders first. This is why you never refer to “the third customer”; you refer to the customer with ID 57.
Tables, rows and columns, worked through
Take a bakery that sells cakes for pickup. A first attempt in a spreadsheet often looks like one wide sheet: customer name, phone, email, cake, size, price, pickup date, paid. It works for ten orders. By two hundred, the same customer appears forty times with three spellings and two phone numbers.
In a database, you split that sheet by subject. Microsoft’s database design guide gives the rule: divide information into tables by major subject, and store only facts about that subject in each table.
| Table | One row is | Columns |
|---|---|---|
| customers | one person who orders | id, name, phone, email, created_at |
| products | one item on the menu | id, name, size, price, active |
| orders | one order placed | id, customer_id, pickup_date, status, paid |
| order_items | one product inside one order | order_id, product_id, quantity, unit_price |
Columns have types
Each column holds one kind of value. In PostgreSQL’s words, “each column is of a specific data type.” pickup_date is a date, quantity is a whole number, price is a decimal amount, paid is true or false. Because the type is fixed, the database can refuse “next Tuesday” in a date column and can sort and add numbers correctly.
One value per cell
A cell should hold one value, never a list. “Chocolate, vanilla, lemon” in a single flavors cell is the classic mistake. Microsoft’s guide calls this first normal form: each intersection of row and column holds a single value. If a thing can have several of something, that something usually deserves its own table.
Keys: how a database knows which row is which
A primary key is the column that identifies each row. PostgreSQL requires its values to be “both unique and not null,” and a table can have only one primary key.
Pick a key that never changes
Most apps use a number or a long random ID generated by the database. Microsoft’s guide warns against names as keys because they are not unique, and against anything likely to change. An email address feels unique, but customers change jobs and providers. If orders were linked by email, every change would break the link.
Composite keys
Sometimes two columns together identify a row. In order_items, the pair of order_id and product_id is unique: the same cake appears once per order, with a quantity. That pair can be the primary key.
Foreign keys
A foreign key is a column holding another table’s primary key. orders.customer_id holds the id of a row in customers. PostgreSQL describes it as a constraint that the values “must match the values appearing in some row of another table.” In practice this means the database will refuse an order for customer 999 if there is no customer 999.
Relationships: one-to-many and many-to-many
Almost every relationship in a business app is one of two shapes. Learn to recognize them and you can read most database diagrams.
One-to-many
One customer places many orders; each order belongs to one customer. You model this by putting the customer’s id on each order. You never put a list of order numbers on the customer row. Microsoft’s guide states the rule: take the primary key from the “one” side and add it to the “many” side as a foreign key.
Many-to-many
One order contains many products, and one product appears in many orders. Neither table can hold the link without repeating itself, so you add a third table, often called a junction table. order_items is that table: each row says “this order includes this product, this many times, at this price.” The many-to-many becomes two one-to-many links.
One-to-one, rarely
Occasionally one row matches exactly one row elsewhere, such as a user and their private settings. It is usually simpler to keep those columns on the same table unless the extra data is large, sensitive or optional.
Why you write each fact once
Microsoft’s guide puts the principle in one line: “always try to record each fact just once.” Here is what that buys you, using the bakery.
Updates happen in one place
Maria changes her phone number. In the one-sheet version, she appears on 40 rows and someone has to fix all 40. In the database version, you change one row in customers, and every past and future order shows the new number because orders only store her id.
Deletes don’t take other facts with them
If products lived only inside order rows, deleting the last order for lemon cake would also delete the only record that lemon cake exists and what it costs. Keeping products in their own table means orders and menu items live independently.
When copying a value is correct
There is one deliberate exception worth knowing. order_items stores unit_price even though products also has a price. That is not duplication; it records what the customer actually paid. When you raise prices next month, old orders keep their original totals. A good rule of thumb: copy a value when you need to remember it as it was at that moment.
Rules the database can enforce for you
Constraints are rules attached to a table. They are the main reason a database stays cleaner than a shared spreadsheet, because every app screen, import and integration has to pass them.
| Constraint | What it stops | Bakery example |
|---|---|---|
| NOT NULL | Empty required fields | An order with no pickup date |
| UNIQUE | Duplicates | Two customer accounts with the same email |
| CHECK | Values outside a rule | A quantity of 0 or a negative price |
| FOREIGN KEY | Links to things that don’t exist | An order for a deleted customer |
What happens when a linked row is deleted
A foreign key also decides what happens on delete. PostgreSQL offers options including RESTRICT, which prevents deleting a row that others still point to, and CASCADE, which deletes the dependent rows too. For the bakery, deleting an order should cascade to its order_items, but deleting a product that appears in past orders should be refused. Many apps avoid deleting products at all and mark them inactive instead.
How to describe a database you need
Whether you are briefing a developer, using a no-code tool or asking an AI builder, a short written description saves rounds of rework. Write it in this order.
- The things you track, one per line, as plural nouns: customers, products, orders.
- For each, the facts you need and their type: pickup date (date), paid (yes/no), price (money).
- How they connect, in sentences: one customer has many orders; an order has many products, each with a quantity.
- Which fields are required and which must be unique.
- Who can see or change each table, for example staff see all orders, customers see their own.
Test it with sample data
Microsoft’s guide recommends refining the design with sample data before you build. Write five realistic rows per table on paper, including awkward ones: a customer with two orders on the same day, an order with three cakes, a cancelled order. If any of them doesn’t fit without repeating information or leaving cells blank, the design needs another table or column.
Common mistakes
These are the design errors that cause the most pain later, and each is easy to avoid on day one.
One giant table
Copying the spreadsheet layout straight into a database keeps every problem the spreadsheet had. Split by subject first.
Lists inside a cell
Comma-separated values in one column can’t be counted, filtered or linked reliably. Use a related table.
Numbered columns
Columns like item1, item2, item3 mean the fourth item has nowhere to go. That is a one-to-many relationship pretending to be columns.
Storing totals you can calculate
An order_total column that someone types in will drift from the sum of the items. Calculate totals from the rows, or have the app compute and store them in one place only.
Free-text status fields
“Done”, “done”, “complete” and “Completed” are four statuses to a database. Limit status to a fixed set of values such as pending, ready, collected, cancelled.
If you’d rather not set this up yourself
If you’d rather not design and host the database yourself, AI app builders such as Floot create the tables, keys and relationships from a plain-English description like the one above, and give each app its own Postgres database you can download. The description you write using this guide is exactly what such a tool needs.
Frequently Asked Questions
What is the difference between a database and a spreadsheet?
Both store data in rows and columns, but a database enforces rules: each column has a type, each row has a permanent ID, and tables link to each other through those IDs. A spreadsheet lets any cell hold anything, which is flexible for one person and error-prone for a team or an app.
What is a primary key in simple terms?
It is the permanent ID of a row, like a customer number. It must be unique and never empty, and it should never change. Apps usually let the database generate it automatically.
What is a foreign key?
It is a column that stores another table’s ID to link the two. An order row stores customer_id, which points to one row in the customers table. The database can refuse links to rows that don’t exist.
What does a relational database mean?
A database that stores data in tables and connects them through keys. PostgreSQL, MySQL and SQL Server are relational databases, and most business apps use one.
Do I need to learn SQL to use a database?
Not to design one. You need to understand tables, keys and relationships so you can describe what you want and check what was built. SQL is the language for querying the data, and many tools now write it for you.
How many tables does a small business app need?
Usually between five and fifteen. A simple order app might have customers, products, orders, order items and users. Each distinct thing you track, and each many-to-many link, tends to become one table.
Sources
- PostgreSQL, Tutorial: Concepts (version 18), 2026 https://www.postgresql.org/docs/current/tutorial-concepts.html
- PostgreSQL, Constraints (version 18), 2026 https://www.postgresql.org/docs/current/ddl-constraints.html
- Microsoft Support, Database design basics https://support.microsoft.com/en-us/office/database-design-basics-eb2159cf-1e30-401a-8084-bd4f9c9ca1f5