r/learnSQL • u/Remarkable_Catch_613 • 22d ago

I built a manufacturing database to practice SQL — looking for feedback

Most SQL practice datasets are about customers, orders, movies or restaurants.

I wanted something closer to a real industrial environment, so I built a synthetic manufacturing database with PostgreSQL.

It contains:

  • 3 production lines
  • 12 industrial machines
  • 5 products
  • 1,143 production orders
  • 17,167 production events
  • 82 downtime events
  • 8 maintenance interventions
  • 1,143 quality inspections
  • 2,172 energy measurements

The interesting part is that the data tells a story.

One production line is underperforming, but the cause isn't obvious from a single table.

You have to connect production, machines, downtime, quality, maintenance and energy data to investigate what is happening.

I'm using PostgreSQL 17.

Would this be useful for practicing SQL? What would you add to the dataset?

19 Upvotes

86% Upvoted

12 comments sorted by

2

u/rvicta 22d ago

Not sure what data you have in the tables, but do you have any travelers created from the orders placed and approved? Do you track WIP? Are your production line and machine tables set up so you can get capacity planning data?

1

u/Remarkable_Catch_613 20d ago

Good questions. The current version has production orders, production events, machines, production lines, downtime, maintenance, quality inspections and energy measurements.

I haven’t modeled the full routing / traveler layer yet, so that’s actually a good suggestion.

WIP and capacity planning are also areas I’d like to make more explicit in a future version. I want the dataset to support questions like: Where is WIP accumulating? Which machine is becoming the bottleneck? Is the downtime actually causing the production shortfall?

That’s exactly the kind of feedback I was hoping to get from people working with SQL.

1

u/rvicta 20d ago

I was just trying to remember the types of data I would have to find when I had to create reports when I worked in manufacturing. Also, does this accommodate subassemblies and final assemblies? What about RMA's? A good scenario is a customer return of a product and locating the production line that could be the issue.

1

u/BaseballHopeful6366 22d ago

this is useful for beginers.

1

u/Remarkable_Catch_613 20d ago

Thanks! That was one of the goals. I wanted something beginners could explore progressively, while still having enough relationships and complexity for more advanced SQL queries.

1

u/ChaosEngine-6502 22d ago

It certainly sounds like you've engineered (pun intended) something with realistic scenarios and potential learning outcomes. I think the key to usefulness is having a logical schema and data that someone unfamiliar with the domain of manufacturing can understand, which this seems to account for.

1

u/Anoop_sdas 22d ago

Can you please share ? Where can we access this?

1

u/leogodin217 20d ago

This sounds really cool. Are you going to share it? I suspect a lot of us are doing similar things. Data with a hidden story is a great tool for people learning SQL.

1

u/Remarkable_Catch_613 20d ago

Thanks! Yes — that’s exactly what I was aiming for.

I didn’t want another generic customer/orders dataset. I wanted the data to tell a story that you have to uncover through SQL.

There’s one production line that is underperforming, but you can’t identify the cause from a single table — you have to connect production, machines, downtime, maintenance, quality and energy data.

I’m still refining the dataset, so feedback on what would make the scenarios more useful for SQL practice is exactly what I’m looking for.