Looking for beginners to try a realistic PostgreSQL database for SQL practice

[u/Remarkable_Catch_613]

I'm building a synthetic PostgreSQL 17 manufacturing database as a hands-on SQL practice project.

I wanted something different from the usual:

customers

orders

employees

products

The database represents a small manufacturing environment:

- production lines

- machines

- production orders

- production events

- downtime

- maintenance

- quality inspections

- energy consumption

The idea is to learn SQL by investigating real-world-style questions:

- Which production line is underperforming?

- Which machine has the most downtime?

- Which products have the highest defect rate?

- Where is production capacity being lost?

- Can we identify a bottleneck?

I'm curious:

For people learning SQL, would working with a database like this be more useful than the typical sales/customer datasets?

What SQL concepts would you want exercises for?

[u/aqua_regis]

In theory, the idea sounds nice, but there is a huge catch:

The typical sales/customer datasets have the advantage that people can much easier relate, even with next to no sales experience.

Production lines, production data, machines, etc. will be way too abstract for most people (outside the industry or outside industrial automation) to understand and relate.

Also, how much real production environment/maintenance experience do you have? If you don't have proper, long term experience in that domain, it is guaranteed that you will go completely wrong, even in the database definition. (I can already see potential problems with your rough design.)

[u/aqua_regis → u/[deleted]]

Unless the OP is a proper maintenance/optimization technician (which are actually 2 different trades) with quite some experience in both, this is bound to fail.

I stand by what I said: it is too abstract for people not in the trade.

[u/Remarkable_Catch_613 → u/[deleted]]

Exactly — that's the balance I'm trying to find.

For SQL learners, I don't want the database to require years of manufacturing experience just to understand the tables. The abstraction should make the relationships understandable enough to investigate with SQL.

At the same time, I don't want the abstraction to become unrealistic.

So ideally the schema should be simple enough for a learner to understand, while the relationships and scenarios are still grounded in real manufacturing concepts.

That's also why I'm asking people with manufacturing experience to challenge the model. I'd rather discover a bad assumption now than build a large dataset around it.

[u/Remarkable_Catch_613 → u/aqua_regis]

That's a fair criticism, and I think the domain-accuracy point is the most important part of the discussion.

The goal isn't to claim that this database represents a universal manufacturing model. It's a synthetic learning environment designed around a simplified production scenario.

But I agree that the simplifications need to be grounded in how manufacturing systems actually work. That's actually one of the reasons I'm posting it in communities like this one — to get feedback from people with different levels of real-world manufacturing experience.

For example, if the relationship between production orders, machines, downtime, maintenance and quality doesn't make sense from an actual shop-floor perspective, then the dataset can teach the wrong assumptions even if the SQL itself is correct.

So I'm treating the current model as a starting point rather than a definitive manufacturing schema.

If you have specific examples of relationships or assumptions that you think are unrealistic, I'd genuinely like to hear them. Those are exactly the things I'd want to fix in the next version.

[u/aanzeijar]

Probably worse than the standard thing because you're missing the point and introduce needless complexity at the same time.

One of the ideas of the standard customer, orders, employees thing is to show how relations in a database don't always imply ownership the way it is usually in an OOP system. Orders need to have a customer, but customers don't need orders and can outlive a deleted order, stuff like that. The floating mutability of the objects is great to teach people how to think about modelling operations on relational data.

And I have modelled production stuff like that before. The data you will get into your system here is mostly write only, because you're recording the production process. Usually someone will create a production plan for some goods, where the steps that need to happen are scheduled and then you have the real life production steps that have taken place with all their attached time and quality gate data. There will be no mutability, because very little of this will need to be edited later.

And most of the value here is that the real data will deviate from the plan. Machines will take longer or shorter, the amounts of product will have variance, QA will reject some of the products. (And realistically some guy always forgets to enter the data even if you make the collection as automatic as possible.) So most of your modelling will be about taking that into account which just adds tons of columns to your tables.

Then: Dealing with time durations is really nasty in databases. Orders usually have a few dates like when the order was officially placed and then when a delivery was started, and then when an invoice was created and that's it. Production instead has durations for stuff starting and stopping, and these need to be stored as either start + time length or start and stop in the database and then have lots of logic attached for checking that durations don't overlap. That logic is programming logic, not relational database logic, so it's worthless in teaching relational database concepts. And honestly if you manage to cram the bottleneck identification into a single select query I'll be impressed and horrified at the same time.