Use one-to-many when a record belongs to exactly one parent, like an invoice that belongs to one customer. Use many-to-many when records on both sides can connect to several on the other side, like products and orders, and give that connection its own table so it can hold details such as quantity and price. Get this choice right at the start and your reports, permissions and automations stay accurate as you grow. Get it wrong and your team ends up copying the same data into row after row.
A spreadsheet handles one list brilliantly
Open the sales tracker at almost any growing business and you'll find a cell that reads "Laptop, Monitor, Docking station". Someone typed three products into one field because the sheet had no better place to put them.
That isn't a failure of the spreadsheet. A spreadsheet is excellent at one list of similar things, such as a price list, a contact list or a monthly budget. The trouble starts when two lists need to talk to each other, and a spreadsheet can only connect them with lookups and copied values. Connecting lists properly is the job relationships in custom database software exist to do.
The rules have held since 1970
Relational databases aren't a trend. Edgar F. Codd, a mathematician at IBM's San Jose research lab, laid out the relational model in his 1970 paper "A Relational Model of Data for Large Shared Data Banks." According to IBM's history archive, Forbes later named it one of the most important innovations of the previous 85 years.
The core idea is simple. Store each kind of thing once, in its own table, and connect the tables with relationships instead of repeating data. Nearly every business system you use today, from accounting software to your CRM, is built on that idea.
One-to-many describes ownership
A one-to-many relationship means one record on the "one" side can have many records linked to it, but each of those records points back to only one parent. The test is a single question: can this record belong to more than one parent? If the answer is no, you're looking at one-to-many.
Real examples are everywhere. One customer has many invoices, but each invoice goes to one customer. One construction project has many site reports, but each report covers one project. One property has many maintenance tickets, but each ticket concerns one building.
Getting this right makes everyday questions easy to answer. "What has this customer bought this year?" becomes a filter on one record instead of a hunt through three tabs.
Many-to-many describes participation
A many-to-many relationship means records on both sides can connect to several records on the other. An order contains many products, and a product appears on many orders. An employee works on several projects, and each project has several employees.
The detail most people miss is that a many-to-many relationship needs its own table in the middle. Between orders and products sits an order lines table, where each row says which product, on which order, in what quantity and at what price. Between employees and projects sits a time entries table recording hours and roles.
That middle table is where the business value lives. It's the reason you can report margin per product, hours per project or utilisation per employee. Teams that skip it end up cramming lists into single fields, and the first time someone asks for a proper sales report, it's back to the drawing board.
One-to-one is rarer than it looks
A one-to-one relationship links a single record to exactly one other record, like an employee and their company car, or a signed contract and its scanned document. It's useful for keeping sensitive or rarely used details in a separate table with tighter access.
If you find yourself creating lots of one-to-one links, pause and check. Often the two tables describe the same thing and belong together.
Modelling relationships should take minutes, not a data engineer
Knowing which relationship you need is half the job. The other half is building it without a ticket to IT. Ninox is an AI-native low-code platform for building custom database software, with tables and links as the foundation, so you can set up all three relationship types yourself.
A link field connects an invoice to its customer, and the customer record lists every invoice linked to it. For many-to-many, you add a linking table such as order lines, then link it to both orders and products. From an order, an embedded form lets you open and edit its linked records inline instead of jumping between screens.
The rules come built in. Set order lines as a subtable of orders and deleting an order removes its lines too, so nothing is left orphaned. Unique fields block duplicate customers or product codes at the point of entry. Because a low-code platform like Ninox puts the data model in your team's hands, the people who know the business can shape it as the business changes.
You don't need to start from a blank screen either. Describe what you need in plain language, such as "customers with many orders, and orders with many products", and Ninox gives you a working starting point with tables, fields and logic in place, ready for your team to refine.
Understanding relationships is becoming table stakes
More teams are building the systems they run on instead of working around off-the-shelf tools. Forrester estimates the combined low-code and digital process automation market reached $13.2 billion by the end of 2023, and in its AI-accelerated scenario could approach $50 billion by 2028. As more businesses build custom database software of their own, knowing the difference between one-to-many and many-to-many is becoming a basic skill. The relational model is half a century old, and choosing a low-code platform that respects it is still the smartest place to start.
Take the next step.
