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

151–160 of 181 posts

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

#151

Post by andrewflnr »

gen220 wrote: post #145
andrewflnr wrote: post #143

Earlier quoted context omitted.

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…

Ok, so the validity period tries to describe the real world, while the tp is more about the state of the database's knowledge, or rather belief. I think I understand how each one starts when it does, but not when you update the endings.

- Does the validity period of the erroneous entry ever get closed?

- Or the transaction period of the one that got superseded?

- Do transaction periods close for reasons other than finding out something was wrong, or can I think of them as the period that the rest of the row (including validity) is/was believed?

Thanks for the detailed explanation. This is really interesting.

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

#152

Post by fsajkdnjk »

janci wrote: post #147
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?

the stored events are called event but they are actually envelopes. the true changes("event"), or payload of the envelope, is stored as serialized field. the envelope holds metadata like dates, domain, aggregate type and id, event name, correlation, causation, user/account, event schema version(this is what you are asking about) and so on. i am using protocol buffers so they are backwards compatible and event schema can evolve in time. but you can use any type of format as long as you keep the schema version within the envelope and only add new fields to the objects.

then, when you are parsing the events(hydrating or replaying) you just check the schema version in the envelope and handle the changes/payload accordingly.

it's actually very trivial once you put it all together.

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

#153

Post by snarfy »

KptMarchewa wrote: post #104
snarfy wrote: post #72

Earlier quoted context omitted.

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?

That's one solution. You could copy the description to the invoice item. That's another. There are a lot more solutions, but the point is none of them will be trivial to implement in a large existing code base. It's really important to get the data model right as much as possible up front. This is a bit counter to all of the agile/scrum rage of iterate and refactor. That works for code, but schemas and data migrations do not lend themselves to constant refactoring.

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

#154

Post by auvi »

joshdick wrote: post #94
auvi wrote: post #78

This is a bit off topic maybe, but I have heard SAP works with tens of thousands of tables. Is that correct?

A recent HN article answers this question, actually! "A basic installation of SAP has 20,000 database tables, 3,000 of which are configuration tables." https://news.ycombinator.com/item?id=22244750

Is the schema (even partially) available publicly?

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

#156

Post by ltbarcly3 »

markhalonen wrote: post #139
ltbarcly3 wrote: post #124

Earlier quoted context omitted.

Im saying it does something that is a bad idea in the first place. You are saying "yea, but it does it with so little effort".

No I'm not agreeing with you at all. It's a great idea, and my clients and bank account agree with me.

This is frustrating.

There are lots of ways to do software. Some are widely considered by experts to be good, some are not so good.

Then there is a thing called business. You can certainly sell software that is built using bad practices, and honestly nobody will probably complain so long as it works. It might not be quite as maintainable, it might require more effort to add features, and it might be necessary to completely rewrite that software in 5 years when other parts of the system change.

Terrible software is bought all the time, and that's not even really a problem.

Even though you sold it and your customers are happy, there still are things you can probably learn, right?

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

#157

Post by ltbarcly3 »

iamaelephant wrote: post #134
ltbarcly3 wrote: post #127

Earlier quoted context omitted.

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

Counterpoint yes it is.

Not according to https://publications.opengroup.org/standards/soa

There is a lot of context lost in generalities so I admit you have to look at every specific situation, but in general CRUD means pushing business logic down to the client (which is generally some kind of code running in a browser or mobile app), which is the opposite of everything good in the world.

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

#158

Post by nujabe »

JacKTrocinskI wrote: post #55

1000 tables isn't an extraordinary amount. As far as database schema design goes it really depends on your needs, e.g. is your database intended for OLTP or OLAP use? Depending on your needs you can decide to what degree you want to normalise your tables, also there a lots schema designs to chose from (star, snowflake, etc.) and it's worth reading about them. Having a good schema design is about knowing the data you…

Not a database person but 1000 tables does seem like an extraordinary amount imo. At that scale I wonder if such a use case warrants a document based database...

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

#159

Post by sitkack »

dspillett wrote: post #96
leetrout wrote: post #49

Earlier quoted context omitted.

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.

Beginners often lack the experience to appreciate "critical thinking" based learning. At first they just want (need?) to know the steps to get something right, especially when they are still not particularly fluent in the very basics. Once you've got some experience (which usually means getting things wrong a few times, seeing wrongness promoted to production because there isn't time to refactor, and having to fix th…

> Beginners often lack the experience to appreciate "critical thinking" based learning.

I don't think that is it. _Beginners_ being the critical word.

Most learning is part of a negative feedback loop, if we only ever succeeded we wouldn't know why we succeeded, failure has such bad connotations in our society that it blinds students from deeply understanding a subject. Maybe replace it with experience?

Back to the subject of _Beginners_, we really should be teaching students from a very young age, philosophy, cognitive science, and epistemology. They should embrace experience, it shouldn't be up to the database schema instructor to teach both data modeling and learning by failure. Students should be fully versed in how to care and feed their brains by the time they arrive in the GPs class.

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

#160

Post by gen220 »

andrewflnr wrote: post #151
gen220 wrote: post #145

Earlier quoted context omitted.

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…

Ok, so the validity period tries to describe the real world, while the tp is more about the state of the database's knowledge, or rather belief. I think I understand how each one starts when it does, but not when you update the endings. - Does the validity period of the erroneous entry ever get closed? - Or the transaction period of the one that got superseded? - Do transaction periods close for reasons other than fi…

No problem! It’s fun! And a powerful conceptual tool. In my experience, it can be used to solve many thorny problems, and I never learned about it in school.

I had a typo in the last row, it should have been

> (“789 Orange St”, vp:(5/1/2020, +inf), tp:(7/1/2020, +inf))

my apologies!

- the valid period of the middle record is never closed, because it would be a misrepresentation of how the database’s perception of the address at that point in ”transaction time”.

- indeed, the superseded transaction period is closed.

- formally, transaction periods close (and new rows are created) whenever a column’s value changes. Think of it like an “updated at” time stamp, except the meaning is more like “canonical during”. The second half of your question is totally correct, the TP is the period during which the values representing the specified slice of valid time are/were believed.

As the third bullet point implies, one characteristic of this schema is that you have “non-destructive” updates: state is never lost, it’s just put into transaction history. This makes it possible to “roll back” to an earlier, known-good state: simply specify a point in transaction time.

There’s a whole additional rabbit hole to dive down: how to make this stuff fast and intuitive. People have mostly solved the fast part, but we’re still working on the intuition.

Post reply on HN