My naive approach these days is avoiding relational schemas, because I'm tired of migrations. Document based NoSQL is hard to maintain too (dupes). The only solution to me are Graph databases like Neo4j or GunDB (inmemory).
Ask HN: What are some examples of good database schema designs?
81–90 of 181 posts
Re: Ask HN: What are some examples of good database schema designs?
#82I'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…
I've started reading, and already the second anti-pattern, "Naive trees", is something I've been dealing with at work recently. Hey, how'd he know?!? Cool book.
Re: Ask HN: What are some examples of good database schema designs?
#83Kinda tough to give a good answer without more context, IMO. What I mean is that a good e-commerce schema that serves a single small store and runs off a single database server would look quite different then a multi-tenant or distributed data store for a e-commerce site at scale. The one you linked is a pretty typical relational model and isn't bad, but it has trade offs that I'd personally not make, however, that d…
Yes, but where can we all see the schemas that has worked well and the ones which hasn't, I mean all that information isn't accessible to someone who wants to know the state of the art. In someways software industry has been really bad in documenting and sharing knowledge, it is really hard to get hold of documents of large software projects, and informed commentary on them from the industry for educational purposes,…
Very fair and very true, and I am 100% in agreement with your points, I do not know of a single resource we can go to as an educational toolset that is based on real world products/experience. The problem is the industry as a whole is locked into these NDA type agreements (along with proprietary information) which prevent a lot of the complete schema sharing etc which would help people learn the best.
That said, some of the best things to look at are open source projects and research their pain points and highlights. That will many times point you to the tradeoffs they made in modeling and in code. For e-commerce things like magento and other open source solutions can be really informative, even woocommerce and how it shoehorns itself into wordpress can be informative as to the tradeoffs on highly normalized vs lightly normalized vs key/values etc.
Re: Ask HN: What are some examples of good database schema designs?
#84Earlier quoted context omitted.
It's well-known to be a topic that splits opinion, so I'm not surprised we disagree :) To me, "designing the data model", "wiring up relationships", etc doesn't require an ORM. On the other hand, I do agree it's good to have some tooling around it and that's something many more bare-bones frameworks (ORM or not) are lacking. I don't hear people talk about "coding for the web, but design so that you can easily switch…
> 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's only been a fews years that Postgres has had row level security for example.
Re: Ask HN: What are some examples of good database schema designs?
#85I've always liked how isfdb, the Internet Speculative Fiction Database, laid theirs out. http://www.isfdb.org/wiki/index.php/Database_Schema
Re: Ask HN: What are some examples of good database schema designs?
#86Earlier 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.
which positions you better for switching to a completely different storage system in the future if
YAGNI
Re: Ask HN: What are some examples of good database schema designs?
#87Earlier 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.
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)
Re: Ask HN: What are some examples of good database schema designs?
#88Not to be too self-promoty, but I spent a ton of time thinking about the schema for my site, and have gotten some compliments on the detail and documentation: https://aytwit.com/sql
I hope that helps spark some ideas for you.
Re: Ask HN: What are some examples of good database schema designs?
#89I'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…
> SQL Antipatterns
I'd definitely second that recommendation, both for relative beginners and those of us who have been at it long enough to have learned and forgotten these things a few times over...
https://pragprog.com/book/bksqla/sql-antipatterns or your favourite [e]book seller, for those wanting a copy.
> why this is bad, it then shows you the recommended way of doing it and why this way works better
He also takes the time to mention when the anti-pattern might be OK to use.
Re: Ask HN: What are some examples of good database schema designs?
#90Earlier quoted context omitted.
I disagree with pretty much everything you said. :) I think there are plenty of bad ORMs and there are plenty of ways to use the good ones in a bad way, but that doesn’t mean that they aren’t providing the value I mentioned. For instance Entity Framework Core with code-first migrations has you designing the data models themselves, then wiring up relationships and other metadata (indexes, keys, etc.) in the DB context…
It's well-known to be a topic that splits opinion, so I'm not surprised we disagree :) To me, "designing the data model", "wiring up relationships", etc doesn't require an ORM. On the other hand, I do agree it's good to have some tooling around it and that's something many more bare-bones frameworks (ORM or not) are lacking. I don't hear people talk about "coding for the web, but design so that you can easily switch…
The GP has no sense of cost, designing around being DB agnostic is costly and, those who really need that flexibility are in the 1%.