Is One Big Table Enough for Your Data?
- 10 hours ago
- 11 min read
Updated: 11 minutes ago
One big table is often easier to start with. A Star Schema is usually easier to live with.
Let’s start with a different question
If someone asked me this during a training break, I probably wouldn’t answer it straight away. I’d ask a question of my own.
“Tell me about the report you’re building.”
It might sound like I’m avoiding the question, but I’m really not. In fact, I think it’s the only sensible place to begin.
Are you trying to answer a question from your manager before tomorrow morning’s meeting? Are you building a dashboard for your own team? Or are you creating something that other departments may eventually rely on?
Those may all look like similar reporting problems, but they’re very different in practice. That’s why I’ve never been comfortable with blanket advice like “Always use a Star Schema” or “Just keep everything in one table.” Both statements sound confident, but neither tells us much about the problem we’re actually trying to solve.
Good data modelling isn’t about following rules for the sake of it. It’s about choosing enough structure to support the work without adding complexity that doesn’t help.
Why this isn’t really a tool question
Most discussions frame this as a technical decision: should you build one large table, or should you separate the data into a Star Schema?
That’s certainly the visible decision, but I don’t think it’s the most important one.
To me, this is really a question about managing complexity.
Think about how many reports begin. Someone asks:
“Can you show me our monthly sales?”
Simple enough.
Then someone wants sales by region. Another manager asks for product categories. Finance wants targets included. Marketing wants campaign performance. Operations wants inventory levels.
None of these requests is unreasonable. In fact, each one makes the report more useful. But together, they gradually change the nature of what you’re building. The model that worked perfectly well when you were answering one question may not be the right model when you’re answering ten.
So instead of immediately asking:
“Should I use a Star Schema?”
I think the better question is:
Has the problem become more complex than the model I originally built to solve it?
That, to me, is the real starting point.
The innocent request that changes everything
Let’s take a very ordinary example.
Imagine you’ve built a sales dashboard from one clean table exported from your business system. Every row represents a sales transaction. The report shows sales by month, region, salesperson and product, and everyone is happy.
Then someone asks:
“Can we compare actual sales against monthly targets?”
At first glance, it sounds like a small enhancement. You already have the sales data, so surely you just add the targets.
But this is often where things start to get interesting.
Your sales data records transactions. Amy might have one transaction for $2,000 on 3 January, another for $3,500 on 5 January, and another for $1,800 a few days later.
Her target data tells a different story. There may be just one record saying Amy’s January target is $100,000.
The two datasets are related, but they don’t describe the same thing. Sales records transactions. Targets record plans.
If we simply merge Amy’s January target into every one of her January transactions, that $100,000 gets repeated again and again. If she made 120 transactions that month, the same target could appear 120 times.
Nothing may look obviously wrong. The data loads, the charts appear, and the report may look perfectly normal. But one careless calculation could add those repeated targets together and tell us that Amy’s January target was $12 million.
Clearly, it wasn’t.
The problem isn’t that one big table is automatically bad. The problem is that we’ve treated two different kinds of business information as though they exist at the same level of detail.
And that’s where the discussion changes from table design to analytical thinking.
When simple stops being simple
A single table can be wonderfully convenient when everything in it tells roughly the same story. If every row represents one sales transaction, one survey response or one customer enquiry, the structure is easy to understand.
The challenge begins when different business processes start being pushed into the same shape.
Sales transactions tell one story. Monthly targets tell another. Inventory levels tell another. Marketing campaigns, budgets and product returns may all belong in the same management discussion, but they describe different aspects of the business.
Putting them into the same dashboard may make perfect sense.
Putting them all into the same table may not.
This is why I hesitate whenever someone tells me a Star Schema is simply “better”. Better for what?
If the business problem is small and contained, a simple model may be exactly the right answer. But when the problem starts involving different activities, different levels of detail and different users, the structure usually needs to evolve with it.
A Star Schema doesn’t remove complexity. It gives that complexity somewhere sensible to live.
That distinction matters because complexity rarely disappears. If we don’t manage it in the model, it often reappears later in calculations, transformations, workarounds and inconsistent definitions.
One question changes the way you see the data
There’s one question I find particularly useful whenever I look at a data model:
What does one row represent?
It sounds almost too simple, but it’s one of the most useful questions in data modelling.
Suppose we have three datasets: Sales, Targets and Inventory.
Before thinking about formulas, joins or relationships, ask the same question of each.
In the Sales table, one row might represent one transaction.
In the Targets table, one row might represent one salesperson’s target for one month.
In the Inventory table, one row might represent one product in one warehouse on one day.
All three datasets may eventually appear in the same dashboard, but they don’t exist at the same level of detail. Data modellers call this grain.
Personally, I think the question is easier to remember than the terminology:
What does one row represent?
Once you start looking at data this way, modelling becomes much more intuitive. You stop seeing tables as collections of columns and start seeing them as records of different business activities. That small shift in thinking can prevent a surprising number of problems later.
So when would I actually use one big table?
After everything we’ve discussed, you might expect me to recommend a Star Schema most of the time.
I don’t. There are plenty of situations where I’d happily keep everything in one table because good design should be proportional to the problem.
Imagine you’re analysing feedback from a single training programme. Every row represents one participant, while the columns contain information such as department, experience, satisfaction rating, recommendation score and comments. Your objective is simply to understand the feedback and prepare a management summary.
Would I split that into separate Participant, Department and Course tables? Probably not. The additional structure is unlikely to improve the analysis. It simply creates more work.
The same applies to a one-off business question. Suppose a Sales Director wants to understand why revenue dipped last quarter before tomorrow morning’s management meeting. If the relevant data already exists in one clean table, I’d rather spend my time understanding the business problem than building a model that may never be used again.
This is where the phrase “best practice” needs context. Best practice for a temporary analysis may look very different from best practice for a reporting model that several departments depend on every month.
The question isn’t whether the model is sophisticated.
The question is whether it’s appropriate.
When does a Star Schema become worth the effort?
Now imagine that the same sales dashboard becomes popular.
Finance starts using it every month. Operations wants inventory added. Marketing wants campaign performance. Leadership asks for budgets and forecasts to be compared against actual sales.
At that point, the report has changed. You’re no longer analysing sales alone. You’re trying to understand how several business processes interact.
This is usually when I stop asking:
“How do I fit everything into one table?”
and start asking:
“Would these different business processes be clearer if each had its own place?”
Very often, the answer is yes.
A Star Schema becomes useful because sales, targets, inventory and returns can remain separate while still being analysed through shared reference tables such as Date, Product, Customer or Region.
That gives each business process its own structure without preventing us from bringing the insights together. There’s another benefit that I think deserves more attention: consistency.
When several reports use the same customer definition, the same product hierarchy and the same calendar, people spend less time debating whose number is correct and more time discussing what the number means.
That isn’t just a technical advantage. It’s a trust advantage. And in analytics, trust is often more valuable than cleverness.
Three questions I would ask before deciding
If we reduce our discussion to something practical, I would ask three questions:
1. Are you analysing one business process, or several?
If you’re analysing only sales, one table may be enough. If you’re combining sales, targets, returns and inventory, more structure is usually helpful. For example, analysing last quarter’s sales transactions is fundamentally different from building a management model that needs to compare sales against targets while also showing inventory and returns.
2. What does one row represent?
If the grain is consistent, a single table may remain perfectly manageable. If different datasets represent transactions, monthly plans and daily snapshots, be much more careful about forcing them together.
Think back to Amy. One row in Sales represents a transaction, while one row in Targets represents an entire month’s target. That difference alone should make us pause before merging them.
3. What might this report become?
A temporary analysis doesn’t need to be designed as though it will become the organisation’s reporting platform. Equally, a dashboard that is already spreading across teams probably shouldn’t be treated as though it will remain a personal report forever.
There’s a difference between over-engineering for every imaginable future and recognising a clear direction of travel. Good modelling sits somewhere between those two extremes.
A quick decision check
Those three questions are how I would probably talk through the decision with someone. But if you want something you can quickly refer to while building a model, I’d break the same thinking into five practical checks.
This isn’t a scorecard. There’s no magic number of ticks that suddenly means you must build a Star Schema.
It’s simply a way of asking whether the model still matches the problem. That’s really what good modelling comes down to: fit between structure and purpose.
What about performance?
Performance matters, but I wouldn’t make it the centre of this decision.
A well-designed dimensional model can offer advantages in storage, filtering and query performance, particularly as datasets and analytical workloads grow. But with a modest dataset, you may not notice much difference at all.
For many business users, I think there’s something even more important:
Can you trust the numbers?
Remember Amy’s $100,000 target?
The biggest risk was never that the report might refresh a little slower. The risk was that we could accidentally report $12 million and make a business decision from a number that had lost its meaning.
A fast wrong answer is still a wrong answer. That’s why I see data modelling not just as a performance technique, but as part of protecting the meaning of the data.
Here’s what I think many people miss
We started with a question about tables. But by now, I hope it’s clear that this is really a question about judgement. Analytics often tempts us to focus on the visible tool in front of us. We ask which feature to use, which formula to write, which chart to choose or which modelling technique is considered best practice.
Those questions matter, but they can distract us from the more important one:
What problem are we actually trying to solve?
If we treat the Star Schema versus one-big-table debate as a technical argument, we’ll probably end up debating rules. If we treat it as an analytical design question, we start thinking about the nature of the data, the business processes behind it, the decisions the report must support and how those needs may change.
Sometimes the right answer is to keep things simple. Sometimes the right answer is to introduce more structure. The skill lies in knowing when to do which.
A model can be perfectly appropriate when it is created and become inappropriate two years later because the business has changed around it. That doesn’t mean yesterday’s model was wrong. It means today’s problem is different.
Good analytics isn’t about defending the model we built. It’s about recognising when the model no longer serves the decisions we need to make.
So, should you use one big table or a Star Schema?
If you came to me with this question, I still wouldn’t ask which analytics tool you use first. I’d want to know what you’re analysing, what one row represents, who will use the model and what you expect it to become.
If you’re working with one business process, at one consistent level of detail, for a relatively contained piece of analysis, I’d be perfectly comfortable starting with one well-prepared table. There’s no prize for creating five tables when one solves the problem clearly and reliably.
If you’re bringing together several business processes, working with different levels of detail, sharing the model across teams or building something that will continue to grow, I’d lean strongly towards a Star Schema.
At that point, the additional structure is no longer unnecessary complexity. It’s how you manage the complexity that already exists. Perhaps that’s the simplest way to remember the answer:
One big table can make it easier to start. A Star Schema can make it easier to continue.
Ultimately, good data modelling isn’t about how impressive the model looks. It’s about whether the people using it can trust the answers that come out of it.
New to Data Modelling? A Quick Reference
Already comfortable with Star Schemas, fact tables, dimensions, cardinality and grain? You can stop here. If not, this quick reference is for you.
What is a Star Schema?
A Star Schema organises data so that the business activity being measured sits at the centre, while information that describes that activity sits around it.
Imagine we’re analysing the sales of a café. At the centre, we have every sale the café makes. Around those sales, we may want information about the product purchased, the customer, the date and the outlet.
The important idea isn’t the star shape itself. Sales records the activity, while the surrounding tables give us different ways to understand it.
What is a fact table?
The easiest way I know to think about a fact table is:
It records something that happened.
In our café example, a customer buys two cappuccinos and a slice of cheesecake. That transaction is an event, and the Sales table records it. Different businesses have different facts. Think claims in insurance, deliveries in logistics, admissions in healthcare, or registrations in training. The terminology sounds technical, but the idea is very ordinary. Businesses create events every day, and fact tables record those events so we can analyse them.
What is a dimension table?
If the fact tells us what happened, dimensions help us understand the context around what happened.
Suppose our café sold $12,000 worth of drinks last week. That number is useful, but almost immediately someone will ask which drinks, which outlet, which day or which customer segment. Product, Outlet, Date and Customer provide those different perspectives. A simple way to remember the relationship is:
Facts give us something to measure. Dimensions give us ways to understand the measurement.
What is cardinality?
Cardinality simply describes how records in two tables relate. Imagine one customer, ABC Pte Ltd, places several orders during the year. We still have one customer, but many orders are associated with that customer.
That is a one-to-many relationship.
The same pattern appears everywhere in business. One customer can have many orders, one product can appear in many transactions, and one salesperson can handle many sales.
There are other types of relationships, including many-to-many. For example, one student may attend several courses while each course has many students. Those relationships are perfectly legitimate, but they require a little more thought when we build the model. We’ll leave that discussion for another question. For now, the important idea is simply this:
Different business relationships may require different modelling structures.
What is grain?
Of all the concepts in this reference section, this is probably the one I’d encourage you to remember.
Grain answers one simple question:
What does one row represent?
A Sales table might have one row per transaction. A Targets table might have one row per salesperson per month. An Inventory table might have one row per product, per warehouse, per day.
All three datasets may appear in the same management dashboard, but that doesn’t mean they belong in the same table. They describe different business activities at different levels of detail.
And that brings us neatly back to where we started. When deciding between one big table and a Star Schema, don’t begin by counting tables. Begin by asking:
What does each row represent, and what business question am I trying to answer?
Once you can answer those two questions clearly, the right structure is usually much easier to see.
Comments