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

21–30 of 181 posts

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

#21

Post by chrismeller »

Faaak wrote: post #20
mkettn wrote: post #9

10 years ago, I'd said "at least third normal form"... but today: Whatever gets the job done. When the application is not really dependent on weird queries (e.g. just a blog), screw the normal forms and design your schema to use the least number of queries for a certain task. Nobody understands three lines of code of queries with left and right joins. On the other hand, if your bookkeeping application uses a database…

I'd rather say: use an ORM ! It will design the DB schema better and faster than you. Still comprehensive enough

It also usually forces your design towards the entities themselves rather than the specific way they’re stored, which positions you better for switching to a completely different storage system in the future if, for instance, it’s becoming too slow or expensive to maintain everything in a traditional big name RDBMS.

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

#22

Post by felixyz »

mkettn wrote: post #9

10 years ago, I'd said "at least third normal form"... but today: Whatever gets the job done. When the application is not really dependent on weird queries (e.g. just a blog), screw the normal forms and design your schema to use the least number of queries for a certain task. Nobody understands three lines of code of queries with left and right joins. On the other hand, if your bookkeeping application uses a database…

I'd say this applies to virtually all best practices, patterns, architectures, etc. If you're doing something very simple, who cares about modularity or any kind of code hygiene? I don't. But what happens in reality? Simple and small systems or experiments grow, one little addition at a time, and we all know the mess that ensues.

So in my understanding, the question posed only applies to at least moderately complex systems, which is where engineering skills matter. And in that context, learning what distinguishes a good database design is obviously very valuable, not to say crucial.

> Nobody understands three lines of code of queries with left and right joins.

Not sure if you're being flippant, but a) this is not true, and more importantly b) why is it that we don't expect programmers to be at least as fluent in SQL as in other, less important, languages?

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

#25

Post by semireg »

I’ve lived 20 years as an amateur SQL database designer and last year I designed my first production nosql schema on mongo. Nosql is a different world... collections, embedded documents. I’m not sure it’s 100% “correct” but it’s working great for me so far.

My project is a license server for my electron app. The tech stack is a joy: mongo/mongoose, express, graphql, JWT, all with a React web front end. Payments by Stripe. The actual licenses are the signed JWT vended by the graphql API (not to be confused with the totally separate JWT for auth).

The main goal is to sell software so I license by machine fingerprint (node module on electron).

It’s been running for over 6 months without issue. I’m just beginning to start architecting an update where I allow a subscription model similar to Jetbrains Perpetual Fallback license, but slightly different in favor of the user. I’ve taken a lot of notes from comments at https://news.ycombinator.com/item?id=17007036

Here’s what I’m thinking so far:

A) Non-Expiring License at $97.99. Unlimited bug fixes for that one version. or B) Expiring License at $9.79/month, and you always have access to the latest version. After 12 consecutive payments you are granted a Non-Expiring License for the most recent version at the time of each payment.

Now, to model this...

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

#26

Post by ganonm »

GrumpyNl wrote: post #16
ganonm wrote: post #12

I'd highly recommend reading SQL Antipatterns. It's a very approachable book that illustrates how to design a database schema by taking commonly encountered scenarios and first showing you the naive approach. After explaining why this is bad, it then shows you the recommended way of doing it and why this way works better. I think 'learning from how not to do something' is a really powerful pedagogical technique that…

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.

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

#27

Post by felixyz »

chrismeller wrote: post #21
Faaak wrote: post #20

Earlier quoted context omitted.

I'd rather say: use an ORM ! It will design the DB schema better and faster than you. Still comprehensive enough

It also usually forces your design towards the entities themselves rather than the specific way they’re stored, which positions you better for switching to a completely different storage system in the future if, for instance, it’s becoming too slow or expensive to maintain everything in a traditional big name RDBMS.

> forces your design towards the entities themselves

I agree that it's very important to not let the physical schema leak into the rest of the system, and to have a strong conceptual model (aka entities and relations). This has been well understood for almost half a century: https://en.wikipedia.org/wiki/Three-schema_approach

But I don't think ORMs are in any special position to help with this. They typically introduce so much other confusion that they tend to divert attention from designing both a good physical schema and a good conceptual model, and maintain a sensible mapping between the two. This can be done with RDBMS views for example, with a fraction of the overhead of an ORM. Most ORM-based code bases I've seen leak tons of db-level details.

> switching to a completely different storage system in the future

Designing for this eventuality is not healthy IMO. If you get there, it will be a so-called "good problem to have" and you will have to deal with whatever unique challenges you face at that level. We might as well be writing code with the possibility of "switching to a completely different programming language in the future" in mind. Yes, clean, modular code will help, but beyond that, not committing to the capabilities of the tools you have chosen will harm you system.

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

#28

Post by felixyz »

coltnz wrote: post #10

I've been using Len Silverstein's Universal Data Models for 15 years. You'll be writing to lots of tables and will want views for your common aggregates. But you'll have the common tables you'll need, the patterns for those you don't and be able to handle new requirements with minimal change. There is no Customer table. "The Data Model Resource Book, Vol. 1: A Library of Universal Data Models for All Enterprises"

In a similar vein, I recommend David Hay's "Data Model Patternns: Conventions of Thought". It's more focused on the concept model side, but with awareness of how models are implemented in db schemas and the trade-offs involved.

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

#29

Post by orsenthil »

GrumpyNl wrote: post #16
ganonm wrote: post #12

I'd highly recommend reading SQL Antipatterns. It's a very approachable book that illustrates how to design a database schema by taking commonly encountered scenarios and first showing you the naive approach. After explaining why this is bad, it then shows you the recommended way of doing it and why this way works better. I think 'learning from how not to do something' is a really powerful pedagogical technique that…

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

I don't think this is a legal (US laws) copy of the book. It is still under copyright and publisher has not released it as Free download.

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

#30

Post by felixyz »

gerdusvz wrote: post #18

Schema's that reflects reality not the current specs. Flexibility is key. In my experience adding tables and migrating existing data to them is hard, adding columns easy. So spend extra time at the start on what tables there should be. Spec: Product has a supplier [tables:product, supplier] Reality: Product can be bought from multiple suppliers [table:product, supplier, product_supplier]

100%

The importance of having db schemas and other software entities reflect real-world things is not appreciated enough. It looks unimportant at first, but soon all intuition becomes useless and the system tends to not have "joints" in the right places, ie it doesn't have flexibility in the same places that reality does.

Post reply on HN