[deleted]
Ask HN: What are some examples of good database schema designs?
121–130 of 181 posts
Re: Ask HN: What are some examples of good database schema designs?
#122I'd highly recommend reading SQL Antipatterns. It's a very approachable book that illustrates how to design a database schema by taking commonly encountered scenarios and first showing you the naive approach. After explaining why this is bad, it then shows you the recommended way of doing it and why this way works better. I think 'learning from how not to do something' is a really powerful pedagogical technique that…
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.
In a work related context recently, I saw this approach used to attempt to describe how to achieve something. The problem for me was that I didn't understand the context well enough to understand why I'd initially even choose the wrong approach, let alone how that then related to choosing the correct approach. Making sure that the fundamentals are important here.
Re: Ask HN: What are some examples of good database schema designs?
#123I 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?
Re: Ask HN: What are some examples of good database schema designs?
#124Earlier 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…
I'm using Postgraphile in production on 2 real-world projects and it saves me an incredible amount of time. I spend 90%+ of my time on the front-end because the api is all automated. Postgrahile allows you to rename/disable api endpoints if needed. Postgraphile rocks. And it's written in modualar Javascript, so I can hack it if I need to. Unlike Hasura.
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".
Re: Ask HN: What are some examples of good database schema designs?
#125I'd highly recommend reading SQL Antipatterns. It's a very approachable book that illustrates how to design a database schema by taking commonly encountered scenarios and first showing you the naive approach. After explaining why this is bad, it then shows you the recommended way of doing it and why this way works better. I think 'learning from how not to do something' is a really powerful pedagogical technique that…
> 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 RDBMS master/slave complexity!).
While there are a few core issues raised, and all struck a chord, I was not a huge fan of the book overall because it was needlessly verbose. I found its authoritative tone grating. Ultimately with so many dialects and projects, SQL style is personal, organizational or project level preference. One issue I felt was undertreated was clarity of syntax. For example, I personally absolutely loathe any use of JOIN as needlessly obtuse cognitive baggage. An untreated issue was (over/mis)use of stored procedures.
Re: Ask HN: What are some examples of good database schema designs?
#126Earlier 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?
versioning solves this problem.
in practice, you have to support retroactive changes, so you have to have "middle" values for versions (i.e. 1.1 between 1 and 2).
People are usually interested in the timestamps associated with those versions (for which period of time is this version valid, when was this version created), so in practice it's easier to just keep track of the timestamps, and leave the versions implicit.
Re: Ask HN: What are some examples of good database schema designs?
#127Earlier 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…
Yes of course you cant allow uncontrolled sql execution, but an api that just maps to crud operations isn't good either.
Re: Ask HN: What are some examples of good database schema designs?
#128> I wonder what kind of problem could possibly require so many tables.
There are three main factors behind this, none of which are bad schema design:
1. Table partitioning/sharding. RDBMS storage engines don't tend to scale for common hybrid access patterns (e.g. contended inserts+reads on tables with many indices and uniqueness constraints) past a billion rows or so. You can scale them yourself, though, by splitting a table's dataset into multiple tables (all with the same schema) and getting the RDBMS to direct reads/writes appropriately.
2. ETL ingest tables for separate datasources. If you have e.g. 100 different scrapers for various APIs used in your data warehouse, all writing into your DB, then even if they all are writing data with the same schema, it's operationally very challenging to have them write to the same physical table. Much simpler to have them write into their own staging tables, which can have their own exclusive-access locks, get their own deduping, get TRUNCATEd separately between batch loads, etc. They can copy into a shared table, but this step is often unnecessary and skipped.
3. Matviews built for extremely expensive queries that exist for running specific reports against. These are effectively materializataions of subqueries shared by common customer queries. As such, there might be hundreds or thousands of these.
In the end, none of these are really tables ("relations", in the relational-algebra sense); they're all operational implementation details. Partitions are literally "parts" of larger tables; ETL ingest staging tables are distinct "inboxes" to a shared table; and reporting matviews are "caches" of queries. All of these things could be separate first-class object types, hidden inside the logic of the DBMS, not presented as "tables" per se, despite taking up storage space like tables. But—in most-any RDBMS I know of—they're not. So you see thousands of tables.
Re: Ask HN: What are some examples of good database schema designs?
#129> I wonder what kind of problem could possibly require so many tables. There are three main factors behind this, none of which are bad schema design: 1. Table partitioning/sharding. RDBMS storage engines don't tend to scale for common hybrid access patterns (e.g. contended inserts+reads on tables with many indices and uniqueness constraints) past a billion rows or so. You can scale them yourself, though, by splitting…
I think in the OP's case, they are staring at a dizzying number of tables for Drupal and wondering if the schema is "good."
It's easy to create a clean schema for a simple problem.
Drupal (and many other mature CMSes) are the result of a decade or more accumulation of features. Much like perl, "it's worse ... because people wanted it worse"[1].
1: https://groups.google.com/forum/m/#!msg/comp.lang.perl.misc/...
Re: Ask HN: What are some examples of good database schema designs?
#130The 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.
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.