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

31–40 of 181 posts

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

#31

Post by felixyz »

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…

This is the only answer yet that seems to actually answer the question :) Great example!

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

#32

Post by Olreich »

Faaak wrote: post #20
mkettn wrote: post #9

10 years ago, I'd said "at least third normal form"... but today: Whatever gets the job done. When the application is not really dependent on weird queries (e.g. just a blog), screw the normal forms and design your schema to use the least number of queries for a certain task. Nobody understands three lines of code of queries with left and right joins. On the other hand, if your bookkeeping application uses a database…

I'd rather say: use an ORM ! It will design the DB schema better and faster than you. Still comprehensive enough

If you’re doing any moderately complex analysis of the data in your database, the ORM will quickly start falling down. Abstracting the query layer into the application codebase is nice, and mapping entities to objects is nice, but ORM is not a silver bullet. Learning what makes for a good schema vs a bad schema and how to avoid N+1 loading or query problems is important no matter what.

ORMs aren’t bad, but learn their escape hatches or else you’ll have a hard time doing more complicated things.

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

#34

Post by jaxn »

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…

Interesting to see the denormalization of user Display names on the most important tables, but not everywhere.

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

#35

Post by tpetry »

mkettn wrote: post #9

10 years ago, I'd said "at least third normal form"... but today: Whatever gets the job done. When the application is not really dependent on weird queries (e.g. just a blog), screw the normal forms and design your schema to use the least number of queries for a certain task. Nobody understands three lines of code of queries with left and right joins. On the other hand, if your bookkeeping application uses a database…

Just dont break the first normal form. Nowadays with json columns breaking the first normal form is a real dumb move.

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

#36

Post by dwd »

the-dude wrote: post #19

Northwind Traders

That made me laugh.

My 2 cents after doing this long enough to recognise it

- Aim for 3NF but not religiously. Still, if you need a flat table try a view.

- Any ternary relationship can be modeled as a pair of binary relations (you'll never regret keeping it simpler)

- You don't need EAV (Magento is a good example of why you shouldn't)

- On the other hand don't serialize data (looking at you WordPress)

- XML and JSON data types though are perfectly fine when you need to store an object

- Every table should have a primary key (preferably an integer)

- If you really want a string for your primary key make it a candidate key (why, because someone will insist on changing it)

- E/R diagrams are your friend

- So are Venn diagrams for visualizing a complex select

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

#37

Post by singingwolfboy »

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 one email address

2. Using PostgreSQL’s “row-level security” system to restrict database results based on the logged in user

3. Dividing tables across multiple schemas for additional security

4. SQL comments for documentation

5. SQL functions for common functionality, such as logging in or verifying an email

It’s a fascinating project, and well worth a look!

[1](https://www.graphile.org/postgraphile/)

[2](https://github.com/graphile/starter)

[3](https://github.com/graphile/starter/blob/master/%40app/db/mi...)

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

#38

Post by chrismeller »

felixyz wrote: post #27
chrismeller wrote: post #21

Earlier quoted context omitted.

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.

> 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 itself - your actual entities are completely portable and have nothing to do with the db itself outside of being used by it.

And sure, needing to switch to another storage system may be a good problem to have... that doesn’t mean you should explicitly tie all of your code to one particular RDBMS. If a user is a user is a user, it shouldn’t matter to anything else in your codebase how or where it is stored, it should still be the same entity. Moving those users from your SQL Server to Mongo or to a third party like Auth0 or an Azure/AWS/etc. federated directory service doesn’t change the fact that every user has an ID, an email, a name, etc.

Code for today, but design for tomorrow.

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

#39

Post by mshanu »

Too many tables in one schema tells me that there is no clear separation of responsibilities. I would first look at clear domain boundaries and separate them. Json type capability is one thing I have found useful when it comes to need for a non relational data to be stored simpler without creating associative tables.

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

#40

Post by fsajkdnjk »

i have been working with eventsourcing for the past few years and a design i have implemented in the repositories(db) lately is to have one table for events and one table for snapshots(ie. the objects in the current state in serialized form). then, depending on the needs of the application(ie. what queries will be run) I will create tables that will serve as pure indices by which I can then lookup the aggregates(object) I need. This gives me incredible flexibility and I do not need to bother with complex schema at all. I use event reactors within transaction context(imagine pre-save trigger per object) to fill these tables with data(or remove data). and from now on i think this is the way to go for me for anything. having your sql schema matching your objects/entities is very restricting and not flexible for future development. with this approach i have the full data available(snapshots) so i don't need to hydrate each aggregate from the event stream and i also have the ability to filter the aggregates as i need and also have highly optimized schema for any query i desire. when something changed in the future, i can simply play through the entire event stream and fill in new indices or whatever is needed. machines are fast these days so storing the entire object as snapshot in serialized form is nothing and it beats having to load tens of fields/columns and parse it into objects manually.
Post reply on HN