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

71–80 of 181 posts

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

#71

Post by ArtWomb »

I always think there is some magical perfect schema that I am missing. And if I just designed things perfectly in the beginning, I wouldn't need all this complex query logic. And reverse lookup tables, and fan outs, and other sundry hacks

But action always trumps thought. And its better to just slurp up as much data as possible

The simplest design for keys in a dict type data store such as Redis hashes, is to just auto_increment user ids. Resolve id=user. And then all data is just flat {var:id=value}. Key type is just a string delimited by ":". Gets you in the game fast. Mine structure and relationships later ;)

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

#72

Post by snarfy »

inopinatus wrote: post #53
gigatexal wrote: post #42

Read 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.

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.

You made a great example, and I think it's the most important thing to remember when designing schema. I've seen arguments for/against 1NF, 3NF, various db features, etc, but really, the schema should model the real world relationships.

If your InvoiceItem has no description, and only has a FK to Product to get the description you're going to have a bad time. Once you build a system around a wrong relationship like that, it's very hard to go back and fix the inevitable issue that updating a product changes old sales invoice records.

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

#73

Post by blowski »

There are three volumes of "The Data Model Resource Book" by Len Silverston, and despite being about 20 years old they remain industry best practices. All sorts of data model design patterns - insurance, ecommerce, data warehouses, party, finance, etc. David Hay's "Data Model Patterns" is also excellent, although less concrete.

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

#74

Post by hnrodey »

leetrout wrote: post #49
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…

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.

Just a guess, your students probably didn't have the frame of reference or perspective to appreciate good design nor bad design.

Someone who's been in the trenches for several years, they'd probably get A LOT out of this technique as they would have many experiences to pull from.

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

#76

Post by billfruit »

davismwfl wrote: post #2

Kinda 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, I wish someone would do something about it.

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

#77

Post by gen220 »

inopinatus wrote: post #53
gigatexal wrote: post #42

Read 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.

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 period, you have a history of the values of an object, and the ability to make non-destructive updates to that object.

It’s an essential component of any audit-worthy system, because it empowers you to trivially answer the question “what did our database think John’s 2018/01/01 address was on the day that we mailed him a check on 2017/12/15”?

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

#79

Post by ltbarcly3 »

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 is already an API for that, it's called SQL.

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

#80

Post by noisy_boy »

adventured wrote: post #7

The Stackoverflow schema is decent. Especially for someone that is a relative amateur to study. It's nothing special or complex, however it's a good example of something that actually works well in practice (and at massive scale). The CRUD-CMS Q&A style lends itself nicely to a basic db schema that is easy to get your head around at a glance. https://meta.stackexchange.com/questions/2677/database-schem... https://i.s…

Any idea which tool was used to generate the ER diagram?
Post reply on HN