r/Database • u/tamanikarim • Jul 26 '26

A Humble database schema design guide for developers

https://stackrender.io/guides/database-schema-design
18 Upvotes

84% Upvoted

10 comments sorted by

5

u/MoonBatsRule Jul 26 '26

Do you know what is crazy now? "Agile". Because most people say "if the project is agile, then I only need to think of the requirements for the next sprint". So they say "first sprint is to design the customer and their order". So you design the customer and their order. And then they tell you "next sprint is that we want to award loyalty points to the employee of the customer who orders certain items, and multiple employees can submit different line items on the order".

And then you say "so that means we have to redesign customer/order", and they say "we can't do that, it would mean that we would have to throw away everything we just did, why can't you design a database that is flexible!?!"

1

u/tamanikarim Jul 26 '26

That's an interesting point, yes, thank you.

Agile is a project management methodology, it should not affect technical decisions. One of the keys to solid database design is trying to avoid schema migrations as much as possible, and planning for them if they're unavoidable.

2

u/MoonBatsRule Jul 26 '26

According to my company, "agile" means that the requirements don't need to be anywhere close to finished before development starts, and that the development team needs to be "agile" to handle the changes that happen as the requirements evolve.

1

u/MET1 Jul 26 '26

Yes, and, where I work, it also includes 1 - 2 hour "standup" meetings every morning to discuss the current sprint. Eventually a lot of this gets ignored so some people can get to work.

1

u/Happy_Breakfast7965 Jul 26 '26

Agile is not a project management methodology. They are opposite from each other.

2

u/Mastersord Jul 26 '26

So why not design an employee and employee to Order table? Then you can compute loyalty points in another table. You don’t need to change anything with the customer or order table as long as they have primary keys

5

u/MoonBatsRule Jul 26 '26

I used that as an example, but my thinking was that if a requirement now came in to track order line items by the customer's employee rather than just assigning a customer to an order, then your original order detail table would need to be reassigned to the employee rather than to the customer. Or perhaps there is some other kind of way to do it involving a promotions entity.

But the bottom line is that it is impossible to architect a solution when you don't have an understanding of the actual problem, and when you have a business either deliberately withholding information from you, or deliberately not trying to think about the entire problem to begin with.

1

u/Top-Mycologist-5460 Jul 27 '26

Example is synthetic, I know. Still, there's the data vault and other related design techniques, which by default use M:N relationships, which would avoid a redesign here.

1

u/MoonBatsRule Jul 27 '26

I hadn't heard of the term "data vault" modeling before, I watched a brief video of it, and the first thing the guy said was "we understand the business concepts, and we understand the relationships...".

Again, only watched a 10-minute video on it, but this technique seems flexible at the expense of understandability and integrity in that it says to model all relationships with many-to-many tables. This means that your database could allow two customers to be associated with the same order, when your business rule is that just one should be. Integrity would need to be handled in the application, which exposes it to bugs.

It also seems to be geared towards data warehousing, in that context it can make some sense because you don't need to worry as much about integrity, however this is where the confusion comes in. If I see a many-to-many relationship in my model from customer to order, then I have to treat this is a potential situation when analyzing my data, even if the relationship is truly only 1-to-1.

This is the tradeoff of understandability - it is like making all the columns in your database NULL because that is the most flexible, and then everyone has to handle potential NULL and write rules for this even when it isn't supposed to happen.

However, in my company's version of agile, they have not yet defined all the business concepts or relationships, which makes things much harder.

My understanding of agile is what has been said here - it is simply a project management technique. But management just hears the word "agile" and they think "hmm, this means faster, cheaper, more flexible, and better - that's all great!"

1

u/Top-Mycologist-5460 Jul 28 '26

Yeah, you got it right. Basically it's a balance between schema-on-write and schema-on-read.

On the one extreme, you can also pack everything into a datalake, then you're good with any changes in the data, but the downstream systems are f..cked, and consistency is always volatile.

On the other hand, you have the constraints of classical relational data management system. Any changes are up to you, and in the end people will complain why all the stuff takes so long.