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

141–150 of 181 posts

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

#141

Post by markhalonen »

BenjieGillam wrote: post #119
ltbarcly3 wrote: post #79

Earlier quoted context omitted.

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…

It's not generally safe to expose SQL to untrusted clients. For example, PostgreSQL 12.2 was released yesterday and fixed a security issue where `ALTER ... DEPENDS ON EXTENSION` did not have any privilege check whatsoever. SQL is also not at all well suited for the needs of frontend web app developers - just ask Facebook about their experiences with FQL! Using an API that's more ergonomic for the frontend, such as Gr…

No idea why Benjie is getting down voted, SQL from a client is a bad idea, and writing billions and billions of CRUD endpoints is soul-draining.

Postgrahile is the best of both worlds, providing a nice GraphQL interface on top of your database.

If you decide you need to write crud endpoints, you have your database still. It's a zero-cost abstraction, which is wild.

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

#142

Post by ak39 »

DanielBMarkham wrote: post #117
ak39 wrote: post #113

Earlier quoted context omitted.

>I agree, and I've come to the conclusion that you should avoid designing a database schema until you have some clear understanding about how the application you're persisting data for will be used. I don't buy this argument, with due respect. (I see a lot of this thought these days and I'm genuinely worried we are about to swing the pendulum of software design into the dark ages where focus from the data model is ta…

Analysis is great and absolutely necessary. Too often we confuse analysis with design, then the heartaches start. Some problem domain things are immutable, some are not, some change over a fixed range, some are firm but expected to change, etc. All that stuff is critical to know as part of analysis. I'd argue that you can do analysis incrementally right along with everything else. Reports sound like a great starting…

>All that stuff is critical to know as part of analysis.

Exactly. I wonder if the frustration to get started as soon as possible ultimately results in data models that aren't ready yet and therefore brittle (IOW: insufficient analysis and logical testing of the data models before UI and other process logic commenced).

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

#143

Post by andrewflnr »

gen220 wrote: post #77
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.

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 don't understand the distinction between "valid" and "could have been used for other transactions". Can you elaborate and/or give an example? Is one of them always a subset of the other?

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

#144

Post by nicktrocado92 »

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…

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

I'm interested in PostGraphile, but i have a question: How do apply permissions when your user table is different from postgres user systems? i only have handful of users that have permissions spaning a lot of tables.

Do i need to create one postgres user for each of my application users?

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

#145

Post by gen220 »

andrewflnr wrote: post #143
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 don't understand the distinction between "valid" and "could have been used for other transactions". Can you elaborate and/or give an example? Is one of them always a subset of the other?

Happy to! It's not impossible for one instance to be a subset of the other. They can also be mutually exclusive periods of time. Let's do an example to explore

Let's say that the present concept of John's address is "123 Apple St"; this was inserted into the database at 1/1/2020.

We don't have credible evidence for what John's address was prior to 1/1/2020.

Therefore, the row looks like this:

> (address:"123 Apple St", valid_period:[1/1/2020,+inf), transaction_period:[1/1/2020,+inf))

This is a case where transaction period and valid period are equal. If John sent us this information by mail, and he signed it 12/25/2019, we have credible evidence that this was John's address, at least effective 12/25/2019.

Therefore, his row would look like this: (address:"123 Apple St", valid_period:[12/25/2019,+inf), transaction_period:[1/1/2020,+inf)).

Now, the transaction period is still 1/1/2020, because this information found it's way into the database on 1/1/2020.

Now, let's continue with the second scenario. Let's say we get a second letter from John, processed on 6/1/2020, saying that he moved to "456 Orange St" on 5/1/2020. There are now two rows in the database, as below:

> (address:"123 Apple St", vp:[12/25/2019,5/1/2020), tp:[1/1/2020,+inf))

> (address:"456 Orange St", vp:[5/1/2020,+inf), tp:[6/1/2020,+inf))

Then, we receive a FINAL letter from John, processed on 7/1/2020, revealing that his previous letter contained a typo! it was "789 Orange St", not "456"! Darn. Our table now contains three rows:

> (address:"123 Apple St", vp:[12/25/2019,5/1/2020), tp:[1/1/2020,+inf))

> (address:"456 Orange St", vp:[5/1/2020,+inf), tp:[6/1/2020,7/1/2020))

> (address:"456 Orange St", vp:[5/1/2020,+inf), tp:[7/1/2020,+inf))

Let me know if you have any questions about this example!

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

#146

Post by konfusinomicon »

jaequery wrote: post #123

I always wondered why people don't share SQL schemas like we do with codes. Anyone know if there's such a site like it or even a marketplace for it?

This site is pretty solid from what I've dug into http://www.databaseanswers.org/data_models/index.htm

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

#147

Post by janci »

fsajkdnjk wrote: post #40

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(obje…

How do you solve event schema changes? I mean event has some data attached to it. The schema of this data may need to change in time. How to replay older events that do not match current schema? Do you keep all versions of event reactors to be able to replay old events?

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

#148

Post by BenjieGillam »

nicktrocado92 wrote: post #144
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…

>> 2. Using PostgreSQL’s “row-level security” system to restrict database results based on the logged in user I'm interested in PostGraphile, but i have a question: How do apply permissions when your user table is different from postgres user systems? i only have handful of users that have permissions spaning a lot of tables. Do i need to create one postgres user for each of my application users?

Absolutely not, this is a common misconception. Have a read of this: https://learn.graphile.org/docs/PostgreSQL_Row_Level_Securit...

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

#149

Post by BenjieGillam »

ltbarcly3 wrote: post #127
BenjieGillam wrote: post #119

Earlier quoted context omitted.

It's not generally safe to expose SQL to untrusted clients. For example, PostgreSQL 12.2 was released yesterday and fixed a security issue where `ALTER ... DEPENDS ON EXTENSION` did not have any privilege check whatsoever. SQL is also not at all well suited for the needs of frontend web app developers - just ask Facebook about their experiences with FQL! Using an API that's more ergonomic for the frontend, such as Gr…

Yes of course you cant allow uncontrolled sql execution, but an api that just maps to crud operations isn't good either.

Agreed: an API that _just_ maps to CRUD operations isn’t good. I’m not advocating for that, neither is singingwolfboy, and the starter repo he’s linked to basically does not use them: there are only 4 CRUD mutations, all the others are custom. I rarely use CRUD operations in PostGraphile, mostly I use custom mutations either defined in SQL or TypeScript.

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

#150

Post by felixyz »

_jal wrote: post #111
felixyz wrote: post #84

Earlier quoted context omitted.

Very useful historical perspective, thanks! Confirms what I had pieced together, that DBs used to be a big liability for organizations, with a special clan (DBAs) of people gatekeeping and introducing patterns that programmers found infuriating. Hence the hatred towards stored procedures, layered schemas, and databases in general. It's probably important to keep stressing, as you do, how different things are now. It'…

DBAs still have their place. In my shop, we have more DBAs than infrastructure people. When you have a small team working on a given tool that only really needs to manage its own data, it really doesn't matter. But some point, you do need expert gatekeepers to tell engineers when they're Doing It Wrong when there are many heterogenous clients accessing large datastores for different purposes, complex audit requiremen…

Yes specialization is often useful. But the divide between developers and DBAs seems to have been similar to the dev/ops divide. Probably still is in many places. There is always a need for seniors or specialists to guide work, I'm not against that. But something like DevOps for RDBMS is needed. DevDat?
Post reply on HN