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

171–180 of 181 posts

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

#171

Post by jaxn »

AdrianB1 wrote: post #130
jaxn wrote: post #34

Earlier quoted context omitted.

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

Seeing some training sessions on performance tuning that use this database as the example, an educated guess is that it's done on purpose. I saw a few cases where usual rules have to be bent to get functionality, sometimes reality beats the book.

Oh, I'm sure it is on purpose. I have had the same problem before and seeing this example makes me a little more comfortable to denormalize like that.

I'm curious what they do if someone changes their name (though that should be really rare on SO)

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

#172

Post by iblaine »

iblaine wrote: post #101

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

s/1NF/3NF/g ...point here is that data good design comes from objects in 3NF...1NF is a good start, 3NF would be better...

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

#173

Post by madno »

contingencies wrote: post #125
dspillett wrote: post #89

Earlier quoted context omitted.

> SQL Antipatterns I'd definitely second that recommendation, both for relative beginners and those of us who have been at it long enough to have learned and forgotten these things a few times over... https://pragprog.com/book/bksqla/sql-antipatterns or your favourite [e]book seller, for those wanting a copy. > why this is bad, it then shows you the recommended way of doing it and why this way works better He also ta…

SQL Antipatterns Saw this thread and just acquired and read this. The book's premise is a great one, I just don't like the execution. Years of my life were dedicated to SQL CRUD and schema evolution before dabbled briefly in NoSQL (meh), random caching systems, then had my aha moment and upgraded to files on unix (awesome caching! great compatibility!) and occasional use of SQLite (easy backup and parallelism! no RDB…

Your approach sounds sensible! Do you have some blog posts, pointers which further explains the approach and factors to consider?

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

#174

Post by madno »

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…

Haven't heard specifically of the terminology "three schema approach", but the idea fits with other notions I've heard about: https://www.martinfowler.com/bliki/BoundedContext.html and Clean Architecture: https://blog.cleancoder.com/uncle-bob/2012/08/13/the-clean-a...

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

#175

Post by madno »

jacques_chester wrote: post #54

> When I read about database schemas with thousands of tables I wonder what kind of problem could possibly require so many tables. Businesses that trade in thousands of products, employ thousands of people in dozens of countries, with hundreds of different taxation, human relations, timekeeping, payroll, health insurance and retirement savings laws, dozens of sites, inventories in hundreds of locations managed by doz…

Sounds like SAP :)

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

#176

Post by madno »

gen220 wrote: post #160
andrewflnr wrote: post #151

Earlier quoted context omitted.

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…

For total ordering are you using an instant (with say seconds granularity) or an additional monotone id to distinguish between changes for the same day?

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

#177

Post by madno »

femto113 wrote: post #33

Take a look at Magento (open source e-commerce). I'll leave it to you to decide whether it's reducible to notions like "good" or "bad" but it's definitely fascinating in a WTF sort of way. https://i.stack.imgur.com/wnwrJ.jpg

LOL, appreciate the link :D

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

#178

Post by madno »

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…

Nice pattern indeed.

This articles explores when it is not the case to apply it (for example when eventual consistency would be a big problem): https://medium.com/@hugo.oliveira.rocha/what-they-dont-tell-...

I guess at some level of load (for scalability reasons) one might need to decouple writing into the append only event log from updating the tables for fast reads, so the one transaction approach won't work, hence the eventual consistency between written data and read data.

Schema maintenance is also a non-trivial task as described in the article: keeping backward compatibility for various event versions, upcasting, lazy upcasting. Hitting a good granularity level for events is important, too big or too small, both are a problem.

For the operational team without intimate app knowledge it is also harder to do ad-hoc work.

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

#179

Post by triangleman »

contingencies wrote: post #125
dspillett wrote: post #89

Earlier quoted context omitted.

> SQL Antipatterns I'd definitely second that recommendation, both for relative beginners and those of us who have been at it long enough to have learned and forgotten these things a few times over... https://pragprog.com/book/bksqla/sql-antipatterns or your favourite [e]book seller, for those wanting a copy. > why this is bad, it then shows you the recommended way of doing it and why this way works better He also ta…

SQL Antipatterns Saw this thread and just acquired and read this. The book's premise is a great one, I just don't like the execution. Years of my life were dedicated to SQL CRUD and schema evolution before dabbled briefly in NoSQL (meh), random caching systems, then had my aha moment and upgraded to files on unix (awesome caching! great compatibility!) and occasional use of SQLite (easy backup and parallelism! no RDB…

I'm sorry can you help me understand how to stop using Join? Some examples or articles on the subject? I can't wrap my head around such a concept.
Post reply on HN