Hacker News Board Hacker News, browsed like a classic message board
Live data from Hacker News

Ask HN: What are some examples of good database schema designs?

news.ycombinator.com

Post reply on HN
Print view View original

111–120 of 181 posts

Re: Ask HN: What are some examples of good database schema designs?

#111

Post by _jal »

felixyz wrote: post #84
_jal wrote: post #62

Earlier quoted context omitted.

> It seems to me databases are uniquely treated this way, as some kind of disposable, simple piece of side equipment This is exactly right - lots of people are still cargo-culting rules of thumb that no longer make any sense. This was an artifact of the last generation's commercial DB market. Open source DBs weren't "there" yet; a combination of real limitations and risk-conservatism kept companies shoveling huge amo…

Very useful historical perspective, thanks! Confirms what I had pieced together, that DBs used to be a big liability for organizations, with a special clan (DBAs) of people gatekeeping and introducing patterns that programmers found infuriating. Hence the hatred towards stored procedures, layered schemas, and databases in general. It's probably important to keep stressing, as you do, how different things are now. It'…

DBAs still have their place. In my shop, we have more DBAs than infrastructure people.

When you have a small team working on a given tool that only really needs to manage its own data, it really doesn't matter. But some point, you do need expert gatekeepers to tell engineers when they're Doing It Wrong when there are many heterogenous clients accessing large datastores for different purposes, complex audit requirements, etc.

Re: Ask HN: What are some examples of good database schema designs?

#112

Post by dillonmckay »

ganonm wrote: post #26
GrumpyNl wrote: post #16

Earlier quoted context omitted.

Free download can be found here http://www.r-5.org/files/books/computers/languages/sql/style...

Please do not share links to pirated copies of resources such as this. I can't stop you from pirating it yourself, but it shouldn't be actively encouraged.

Could this be a citizen of Antigua and Barbuda?

Re: Ask HN: What are some examples of good database schema designs?

#113

Post by ak39 »

HeroOfAges wrote: post #97
DanielBMarkham wrote: post #70

A schema is a way of designing data structures such that they are efficiently organized and easy to use. But that leaves the obvious question: use for what? Structures, whether it's databases or object graphs, exist for only two reasons: to do stuff and to fit into a pattern of rules you've decided to use beforehand. Without either of those, there is no way to judge a schema. We could talk about generally organizing…

I agree, and I've come to the conclusion that you should avoid designing a database schema until you have some clear understanding about how the application you're persisting data for will be used. The data structures and schemas will practically jump out at you. It seems, at this point in my career, a very obvious thing, and yet I cannot communicate this point of view in a way to my colleagues that will affect chang…

>I agree, and I've come to the conclusion that you should avoid designing a database schema until you have some clear understanding about how the application you're persisting data for will be used.I don't buy this argument, with due respect. (I see a lot of this thought these days and I'm genuinely worried we are about to swing the pendulum of software design into the dark ages where focus from the data model is taken away in favour of convenience of process design, front end design and data access frameworks).

In my experience: the objects from the problem domain will always have certain immutable relationships with each other right from the beginning. You have to look for them first before you do anything. They are clearly identifiable. What to persist and what not to persist are also discernible from existing manual business processes and the documents operational staff use daily (many of these are legal documents like tax invoices, contract confirmation, mandates and policy agreements etc ... a great way to start is to look at what reports users might need. Yes, those boring reports! Start there). Then, cardinalities and ordinalities will be known the moment you understand the objects relationships too (Can a client with product A also have product B? etc). Then, identifiers, business keys and other coding formats are also determined very early on in the project.

All of this before you even choose a front-end framework or the people's favourite ORM library.

I start with the data model. I use a good ER modeling tool to avoid frustrating myself when large changes are needed for my model. The models, when sufficiently complete, are printed in large format and pasted on walls for all to see daily. That is then considered holiest of holies.

(I understand that data models that need changes other than adding new columns or new sub entities can be painful to refactor ... but the fear of this eventuality should not automatically translate into turning the development process on its head.)

Just my 2c.

Re: Ask HN: What are some examples of good database schema designs?

#114

Post by DanielBMarkham »

rguzman wrote: post #100
DanielBMarkham wrote: post #70

A schema is a way of designing data structures such that they are efficiently organized and easy to use. But that leaves the obvious question: use for what? Structures, whether it's databases or object graphs, exist for only two reasons: to do stuff and to fit into a pattern of rules you've decided to use beforehand. Without either of those, there is no way to judge a schema. We could talk about generally organizing…

i agree with everything you said, except for the part of your being unable to help. i think OP is asking for what information informs schema decisions, and what are some heuristics to use. expanding on the differences between your two examples would be very valuable!

I get what you're saying, that by moving to the meta level we can talk about heuristics and patterns of development.

Unfortunately, and I apologize for sounding difficult, this is still far too broad to gain traction on.

I think the thing to remember when you're learning various architectures, from database schemas to build pipelines, is that many times the people teaching you are teaching you from a position of having a completed project and then looking back on the lessons learned and applying some heuristic-making to them.

For instance, if you look at database normalization, which I started with when I started coding, it makes total sense for a small-ish project. Back in the day, you controlled the app, the machine, the storage, and the code. You owned it all. So changes to the schema involved a finite and easyish-to-do set of practices.

This started falling apart really quickly, though, with folks talking about impedance mismatch just a few years after relational databases went mainstream. If I had to generalize, everything got more and more complicated and the assumption that you could grab the entire application in your head easily and change it was no longer true. Then came a ton of CASE tools, now ORMs, and so forth, all in an effort to get us back to easily owning and changing data schemas.

But the real problem was there all along we just didn't realize it: thinking you knew everything and could manage it. This idea worked great in classrooms, worked great in personal and small apps. It even worked great in larger apps with tight control. But at the level of complexity we have now, it just doesn't seem realistic to be teaching people the perfect way to do things. (Of course, they should be aware of them!). Instead, what's needed is how to gradually get from here-to-there in a complex world without getting lost. So the heuristics you'd get would be perfect world completed apps, and what you really need to know is, well, how to develop software. That kind of advice ain't happening in an HN thread.

Hope that made some sense. Ping me offline if I can help explain any more.

Re: Ask HN: What are some examples of good database schema designs?

#116

Post by firethief »

dspillett wrote: post #96
leetrout wrote: post #49

Earlier quoted context omitted.

I tried that in a class I taught. The students were very frustrated and considered it a waste of time. I agree it’s a FANTASTIC way to learn. I was very disheartened I didn’t effectively communicate that to the students.

Beginners often lack the experience to appreciate "critical thinking" based learning. At first they just want (need?) to know the steps to get something right, especially when they are still not particularly fluent in the very basics. Once you've got some experience (which usually means getting things wrong a few times, seeing wrongness promoted to production because there isn't time to refactor, and having to fix th…

It's like humans have to learn how to extract features before they can make efficient use of straightforward supervised learning...

Re: Ask HN: What are some examples of good database schema designs?

#117

Post by DanielBMarkham »

ak39 wrote: post #113
HeroOfAges wrote: post #97

Earlier quoted context omitted.

I agree, and I've come to the conclusion that you should avoid designing a database schema until you have some clear understanding about how the application you're persisting data for will be used. The data structures and schemas will practically jump out at you. It seems, at this point in my career, a very obvious thing, and yet I cannot communicate this point of view in a way to my colleagues that will affect chang…

>I agree, and I've come to the conclusion that you should avoid designing a database schema until you have some clear understanding about how the application you're persisting data for will be used. I don't buy this argument, with due respect. (I see a lot of this thought these days and I'm genuinely worried we are about to swing the pendulum of software design into the dark ages where focus from the data model is ta…

Analysis is great and absolutely necessary.

Too often we confuse analysis with design, then the heartaches start. Some problem domain things are immutable, some are not, some change over a fixed range, some are firm but expected to change, etc. All that stuff is critical to know as part of analysis.

I'd argue that you can do analysis incrementally right along with everything else. Reports sound like a great starting point. I agree with your advice, even the part about the pendulum swinging too far the other way. The problem is that, honestly, we suck no matter which way the pendulum swings. We continue to confuse the process with the goals.

Re: Ask HN: What are some examples of good database schema designs?

#118

Post by alharith »

dspillett wrote: post #93
alharith wrote: post #87

Earlier quoted context omitted.

Just out of curiosity, why? Pirating traditional media, journals, and movies and entertainment is actively encouraged on this site, with popular sites and software for doing so frequently referenced, and it is never flagged. Seems like a double standard. (Full disclosure, I agree with your objection and believe piracy is wrong in any case, but seems like we should be consistent with the application of the rules)

There is a difference between talking generally about piracy and its ramifications, even encouraging it as some would be doing, and directly linking to unlicensed copies of material. Even if an individual did believe that piracy is perfectly acceptable, direct linking still puts the site under risk of moving into the cross-hairs of law enforcement.

I do not agree there is a material difference in that case. Whether or not the site is under the crosshairs is also irrelevant to the point.

Besides, as I mentioned in my comment, direct links to hubs that contain pirated material and direct links containing suites of tools specifically used for pirating such as DRM removal on ebooks and video players, and bypasses for news organizations and scientific publications are also frequently directly linked.

Re: Ask HN: What are some examples of good database schema designs?

#119

Post by BenjieGillam »

ltbarcly3 wrote: post #79
singingwolfboy wrote: post #37

PostGraphile [1] is a framework for generating a GraphQL API based on the tables in your database; as a result, good database design is crucial. Graphile Starter [2] is a quickstart project that demonstrates best practices for getting up and running with PostGraphile quickly. In particular, check out the SQL migration file in that project [3]. It demonstrates: 1. Dividing up tables so that one user can have more than…

If you are going to auto generate an api for a database, just use SQL. Adding extra steps between you and a database with no encapsulation is just adding extra steps for no reason. One of the key things you need to do in good database design is to map business verbs to API endpoints in something like a 1-1 way. Having an api endpoint that is essentially "insert this row to the database" is just cargo culting. There i…

It's not generally safe to expose SQL to untrusted clients. For example, PostgreSQL 12.2 was released yesterday and fixed a security issue where `ALTER ... DEPENDS ON EXTENSION` did not have any privilege check whatsoever. SQL is also not at all well suited for the needs of frontend web app developers - just ask Facebook about their experiences with FQL! Using an API that's more ergonomic for the frontend, such as GraphQL, backed by a language which is optimised for the backend, such as SQL, is the best of both worlds.

Re: Ask HN: What are some examples of good database schema designs?

#120

Post by AlphaSite »

ak39 wrote: post #113
HeroOfAges wrote: post #97

Earlier quoted context omitted.

I agree, and I've come to the conclusion that you should avoid designing a database schema until you have some clear understanding about how the application you're persisting data for will be used. The data structures and schemas will practically jump out at you. It seems, at this point in my career, a very obvious thing, and yet I cannot communicate this point of view in a way to my colleagues that will affect chang…

>I agree, and I've come to the conclusion that you should avoid designing a database schema until you have some clear understanding about how the application you're persisting data for will be used. I don't buy this argument, with due respect. (I see a lot of this thought these days and I'm genuinely worried we are about to swing the pendulum of software design into the dark ages where focus from the data model is ta…

I think the fear from defining the models too early is that you won’t see what’s not needed, this approach probably has a tendency towards adding unnecessary models, indices, etc.
Post reply on HN