WikiMedia's database schema is used by Wikipedia, one of the most popular websites there is, so it's arguably a very successful database schema design:
https://www.mediawiki.org/w/index.php?title=Manual:Database_...
Ask HN: What are some examples of good database schema designs?
91–100 of 181 posts
Re: Ask HN: What are some examples of good database schema designs?
#92A 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 would design the schema for a three-microservice widely-distributed high-performance application far differently than I would a local checkbook app for my brother.
I think it might be helpful to expand on what some of those differences are. I think most of us here would be able to come up with at least a satisfactory schema for a local checkbook app. What are the changes you need to make to that type of schema to make it appropriate for a multi-service app, specifically? What about distributed? HP? Are any of these changes in conflict with each other? Etc
Re: Ask HN: What are some examples of good database schema designs?
#93Earlier quoted context omitted.
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)
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.
Re: Ask HN: What are some examples of good database schema designs?
#94This is a bit off topic maybe, but I have heard SAP works with tens of thousands of tables. Is that correct?
A recent HN article answers this question, actually!
"A basic installation of SAP has 20,000 database tables, 3,000 of which are configuration tables."
Re: Ask HN: What are some examples of good database schema designs?
#95I'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…
THANK YOU!!!! This is a fantastic reference, and helps me put some teeth behind my instincts when it comes to SQL.
Re: Ask HN: What are some examples of good database schema designs?
#96I'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 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 the wrongness later once more layers or wrong are piled on top) you appreciate this sort of analysis much more as it helps you get things right first time more often, and/or helps you spot the actual problem (rather than chasing symptoms) in more complex situations.
Re: Ask HN: What are some examples of good database schema designs?
#97A 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 change.
Re: Ask HN: What are some examples of good database schema designs?
#98Earlier quoted context omitted.
Beware of crossing context boundaries when applying 3NF to commerce records. E.g. The description of an item in a sales invoice is the description at the time the contract of sale is made, and must be immortalised as such as a copy of that description.
Another commenter mentioned it already, but it’s worth repeating. You can solve this problem by introducing temporal concepts. We rely strongly on this at my place of work, it really works! Essentially, you just need two tstzrange columns, representing: (1) when the row was a “valid” representation of the key, (2) when the row could have been used to conduct other “transactions”. With a valid period and a transaction…
I imagine you also save changes to the address as separate versions so you can query them in the future?
Re: Ask HN: What are some examples of good database schema designs?
#99A 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…
For my last few projects, I’ve started by building with a document-based database knowing a rewrite will come within 6-12 months. On rewrite, I can design the new relational db with 5NF very quickly.
Re: Ask HN: What are some examples of good database schema designs?
#100A 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!