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

101–110 of 181 posts

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

#101

Post by iblaine »

> What are good schema examples?

Anything that doesn't break the first normal form("1NF")[1].

> And what are some poor examples?

Anything that breaks 1NF.

You break 1NF when...

* a column cannot be described by the key, ex. user.id describes user.name but not user.items_purchased.

* values in a column are repeated, ex. a user table that stores multiple addresses for the same user should be split into an addresses table.

Treat tables as classes and you'll be fine. Just like a method may not belong in a class, a column may not belong in a table.

[1](https://en.wikipedia.org/wiki/First_normal_form)

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

#102

Post by gen220 »

andrei_says_ wrote: post #98
gen220 wrote: post #77

Earlier quoted context omitted.

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?

Yes. In practice, the typical "UPDATE" looks like this:

1. create a "hole" in the current transaction space's valid space (through expirations of records in transaction time, valid time).

2. insert the new version at (transaction_period=(now, inf), valid_period=(effective_date, inf)).

It sounds complicated, and it sometimes is, but in practice most of the hard work (specifically around transaction periods) is performed for you by triggers. Usually you only have to think about valid periods, as a developer, which is easier to wrap your mind around.

The unfortunate thing is that there aren't a lot of very straightforward open source implementations of bitemporal triggers. The ones that exist are mostly designed to be theoretically sound and feature-complete, rather than usable.

At my company, we have our own implementation that has slowly grown over the years. It's about 500 lines of SQL triggers, and maybe 2k lines of library code in Python/Go to make the ergonomics a little bit easier on the developer.

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

#103

Post by adventured »

noisy_boy wrote: post #80
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?

I believe it was: https://dbschema.com

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

#104

Post by KptMarchewa »

snarfy wrote: post #72
inopinatus wrote: post #53

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

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 t…

Never done something regarding invoices and related stuff, so just an academic question: Why not just version the product description and FK to appropriate version?

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

#105

Post by davidw »

I've long recommended this book:

"The Data Model Resource Book": https://amzn.to/2tXNiuF

You can look at an implementation of a number of the ideas here: https://ofbiz.apache.org/

It's kind of complicated - probably overly so for some things - but there are a lot of ideas to think about and maybe utilize at least in part.

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

#106

Post by davidw »

deepaksurti wrote: post #15
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"

>> "The Data Model Resource Book, Vol. 1: A Library of Universal Data Models for All Enterprises" [1] https://www.wiley.com/en-us/The+Data+Model+Resource+Book%2C+...

As per my comment elsewhere, this open source project implements a lot of that book (I can second the recommendation):

https://ofbiz.apache.org/

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

#107

Post by markhalonen »

ltbarcly3 wrote: post #79
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 i…

I'm using Postgraphile in production on 2 real-world projects and it saves me an incredible amount of time. I spend 90%+ of my time on the front-end because the api is all automated.

Postgrahile allows you to rename/disable api endpoints if needed.

Postgraphile rocks. And it's written in modualar Javascript, so I can hack it if I need to. Unlike Hasura.

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

#108

Post by pcnix »

thrownaway954 wrote: post #63

it really all depends on the requirements. i use to think wordpress had the worst schema in the world. after actually using it and writing plugins for it, i've come to the conclusion that it is genius. their schema design really makes it very easy for others to "extend" the structure without having to actually alter the schema. to elaborate on what i mean by extend, wordpress's schema is basically a post table that h…

This is a database that satisfies at least the first three normal forms, and that might be why it felt comfortable. I admit that I have seen some dirty usage of this from a few WordPress plugin authors on a consultation, and it has since then not thought as greatly of it as a system that enforces good practices.
Post reply on HN