Where are the repositories of this info?
Just for record keeping, analysis, learning etc.
Ask HN: What are some examples of good database schema designs?
41–50 of 181 posts
Re: Ask HN: What are some examples of good database schema designs?
#42Read up on third normal form. It’s all you need for 99% of oltp databases (the backend to a store) and read up on snowflake (The design not the company) and Star schema for datawarehouses for analytics.
Re: Ask HN: What are some examples of good database schema designs?
#43PostGraphile [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…
Users having multiple addresses is something I've cursed a lot over. I work in a team that does data analytics for a news publishing company, and our print business is still very important. Unfortunately, in our database over print customers users are basically addresses because you don't really need to know how many people are receiving your paper as a distributor, only where and how many papers. Since it's also been a safe assumption for a century that people share newspapers with each other, market research was done street by street to inform ad buyers of which markets we reached. Many people have more than one home. Some people take out another subscription for a relative.
This mapped very awkwardly to digital subscribers who we had individual data on. We were able to join databases in a way that sort of works through more or less (mostly less) comfortable assumptions. The queries are not pretty.
Re: Ask HN: What are some examples of good database schema designs?
#44Take a look at http://www.softwaregems.com/Documents/Student%20Resolutions/.
I will refrain to comment on the quality of Drupal’s schema, but that diagram just shows a bunch of tables and how those tables are connected by foreign keys. What do those connections mean? What other constraints are there in the data? Are they represented in the diagram?
A good database design conveys a lot more semantics. There is currently one ISO standard (ISO 31320-2, key-based style modeling) for database design. Adop ting that standard does not automagically guarantee good designs, but, if used correctly, it helps a lot (it doesn’t help if you don’t have a good grasp of the Relational model, so I would recommend that you would get familiar with that first and foremost).
Most database schemas you’ll find around are rippled with useless ID attributes that are essentially record identifiers (they are not even “surrogates” as many people call them: for the definition of a “surrogate” read Codd’s 1979 paper) and, as a consequence, they are navigational in nature (they resemble more CODASYL structures than Relational models): to connect records in table A with records in table D, you must join with (navigate through) B and C, while in a well-designed Relational schema you might have joined A and D directly. Do you want a rule of thumb to recognized poorly designed databases? Check for ID attributes in every table (there are many other ways in which database design can suck, though).
How do you recognize good database diagrams? They can be easily translated into natural language (predicates and facts), and two (technically competent) different people would translate them in the same way, i.e., the model is unambiguous. Can you say so for Drupal’s schema?
Re: Ask HN: What are some examples of good database schema designs?
#45Earlier quoted context omitted.
> 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 introdu…
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 to deploy as a Windows desktop app." Or "write it in Python, but in such a way that we can easily swap to OCaml." It seems to me databases are uniquely treated this way, as some kind of disposable, simple piece of side equipment. Again, modular code will always be easier to migrate, but I prefer to take full advantage of db capabilities, as it results in much less code and frees up time and mental space to focus on a good conceptual model and physical schema, among other things.
I've never used EF, so I might not see what you are seeing.
Re: Ask HN: What are some examples of good database schema designs?
#46Take a look at http://www.softwaregems.com/Documents/Student%20Resolutions/ . I will refrain to comment on the quality of Drupal’s schema, but that diagram just shows a bunch of tables and how those tables are connected by foreign keys. What do those connections mean ? What other constraints are there in the data? Are they represented in the diagram? A good database design conveys a lot more semantics. There is curre…
your link ist just a directory?
Re: Ask HN: What are some examples of good database schema designs?
#47Vertabelo has a blog which is almost entirely devoted to modelling schemas for various contrived scenarios: https://www.vertabelo.com/blog/ .. I take umbrage with some of the decisions made but broadly they are interesting and well thought out.
Re: Ask HN: What are some examples of good database schema designs?
#48> good database schema designs?
Ones that ship.
Re: Ask HN: What are some examples of good database schema designs?
#49I'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.
Re: Ask HN: What are some examples of good database schema designs?
#50PostGraphile [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…
Users having multiple addresses is something I've cursed a lot over. I work in a team that does data analytics for a news publishing company, and our print business is still very important. Unfortunately, in our database over print customers users are basically addresses because you don't really need to know how many people are receiving your paper as a distributor, only where and how many papers. Since it's also bee…
There's a whole subfield of information science dedicated to basically this exact problem: entity resolution.
Hilariously, it has dozens of names, because it just comes up in so many places for so many people. It appears that "record linkage" is the term that has won the top spot at Wikipedia: https://en.wikipedia.org/wiki/Record_linkage