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

#1

Post by vanilla-almond » Fri, Feb 14, 2020, 7:33 AM UTC

I've seen many examples of database schemas but don't always recognise a good design over a mediocre or poor design.

This is what I mean by schema (is this a good design?)

https://www.drupal.org/node/1785994

When I read about database schemas with thousands of tables I wonder what kind of problem could possibly require so many tables.

What are good schema examples? And what are some poor examples?

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

#2

Post by davismwfl » Fri, Feb 14, 2020, 8:13 AM UTC

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 doesn't make it bad.

In the end context, scale and usage all determine a good schema design. Sometimes what would be a good relational design on paper would be tragically horrid in practice once you get beyond a small dataset.

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

#3

Post by Namoshek » Fri, Feb 14, 2020, 8:47 AM UTC

I came to the same conclusion when I read that a basic SAP installation comes with over 20k tables. (see: https://retool.com/blog/erp-for-engineers/)

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

#4

Post by codegladiator » Fri, Feb 14, 2020, 9:06 AM UTC

3NF or more is good. Else its poor. Number of tables does not matter.

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

#5

Post by OJFord » Fri, Feb 14, 2020, 9:13 AM UTC

> When I read about database schemas with thousands of tables I wonder what kind of problem could possibly require so many tables.

They're probably mostly small (in terms of columns), many of them done just to have foreign key constraints, separate indexes, and arguably easier querying (joining) on just the data you need.

But I think it's a particular style, rather than a 'problem' that 'requires' so many.

(IANA database expert though, just my tuppence.)

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

#6

Post by hyperpallium » Fri, Feb 14, 2020, 9:18 AM UTC

diaclaimer: theoretical opinion.

I think the primary problem of giving examples here is similar to teaching software engineering, which needs complex projects solving complex problems - too big for a semester project.

A good schema depends on the problem it's solving.

A secondary problem is similar to code that has sacrificed clarity for performance. The tweaks made for performance are not intrinsic to the logical problem, but are an additional constraint.

For performance on common queries, schema can be flattened or "denomalized". The ability to do so was one of the original motivations for Codd's relational algebra.

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

#7

Post by adventured » Fri, Feb 14, 2020, 9:28 AM UTC

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.stack.imgur.com/AyIkW.png

19 million questions, 29m answers, 12m users, 10m visits per day, and most of the db action is in less than two dozen tables.

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

#8

Post by silveroriole » Fri, Feb 14, 2020, 9:35 AM UTC

codegladiator wrote: post #4

3NF or more is good. Else its poor. Number of tables does not matter.

There are good reasons to denormalise, but as a rule of thumb... yeah, this. I don’t think you can go that far wrong with schemas as long as you have an idea of your entities and their cardinalities. It’s much easier than designing, say, the associated Java classes, because there are clear rules about how to do it and it’s just obvious when you’ve done it wrong (your cardinality is all messed up).

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

#9

Post by mkettn » Fri, Feb 14, 2020, 9:36 AM UTC

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 try to keep things as tidy as possible.

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

#10

Post by coltnz » Fri, Feb 14, 2020, 9:43 AM UTC

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"