Making database systems usable

&gt;They care less about impressive benchmarks or clever algorithms, and more about whether they can operate and use a database efficiently to query, update, analyze, and persist their data with minimal headache.<p>Hugely important, and I would add &quot;backup-and-restore&quot; to that list. At risk of sounding conceited, ease of use is a primary goal of rqlite[1] -- because in the real world databases must be <i>operated</i>[2]. I never add a feature if it&#x27;s going to measurably decrease how easy it is to operate the database.<p>[1] <a href="https:&#x2F;&#x2F;www.rqlite.io" rel="nofollow">https:&#x2F;&#x2F;www.rqlite.io</a><p>[2] <a href="https:&#x2F;&#x2F;docs.google.com&#x2F;presentation&#x2F;d&#x2F;1Q8lQgCaODlecHa2hS-OeMGoij22oMZ_xhyeRTrQnn6o&#x2F;edit#slide=id.ge8e3d69953_0_35" rel="nofollow">https:&#x2F;&#x2F;docs.google.com&#x2F;presentation&#x2F;d&#x2F;1Q8lQgCaODlecHa2hS-Oe...</a><p>Disclaimer: I&#x27;m the creator of rqlite.

A huge fraction (not 100%, but maybe 80%) of my frustration in trying to get technical people to use a database is that they have such a hard time understanding JOINs.<p>People endlessly want hacks, workarounds, and un-normalized data structures, for a single reason - they don&#x27;t want to have to think about JOIN. It&#x27;s not actually for performance reasons, it&#x27;s not actually for any reason other than it&#x27;s easier to imagine a big table with lots of columns.<p>I&#x27;m actually sympathetic to that reticence but what I am not sympathetic about is this: why, in 2024, can&#x27;t the computer figure out multi-table joins for me?<p>Unless your schema is really fucked up, there should only be one or two actually sensible ways to join across multiple tables. Like say I have apples in boxes in houses. I have an apple table, a box table, and a house table. apples have a box_id, boxes have a house_id. Now I want to find all the apples in a house. Do two joins, cool. But literally everyone seems to write this by hand, and it means that crud apps end up with thousands of nearly identical queries which are mostly just boringly spelling out the chain of joins that needs to be applied.<p>When I started using SQLAlchemy, I naively assumed that a trivial functionality of such a sophisticated ORM would be to implement `apple.join(house)`, automatically figuring out that `box` is the necessary intermediate step. SQLAlchemy even has all the additional relationship information to figure out the details of how those joins need to work. But after weeks of reading the documentation I realized that this is not a supported feature.<p>In the end I wrote a join tool myself, which can automatically find paths between distant tables in the schema, but it seems ludicrous to have to homebrew something like that.<p>I&#x27;m not a trained software engineer but it seems like this must be a very generic problem -- is there a name for the problem? Are there accepted solutions or no-go-theorems on what is possible? I have searched the internet a lot and mostly just find people saying &quot;oh we just type out all combinatorially-many possible queries&quot;... apologies in advance if I am very ignorant here

I think there&#x27;s a huge difference between how you design:<p>1. A database that&#x27;s meant to be understood by programmers, make queries by the application efficient in space and time, and provide strong referential integrity.<p>2. A database that&#x27;s meant to be played with by humans, where duplication is fine, referential integrity is a nice-to-have, every column is okay to be nullable, tables should contain complete objects as understood by the user that map 1-1ish to the real world, that eliminate as many opaque ids as feasible, and foreign keys might exist but aren&#x27;t enforced in any direction.<p>The latter database is far more ergonomic and you won&#x27;t run up against a user frustratingly bashing their keyboard because the database just refuses to do what they want. The stakes in #2 style databases are extremely low-- let the user destroy their copy of the database it&#x27;s fine, we can reload from the last save.<p>The nice thing is that it seems very possible to go from #1 -&gt; #2 mechanically, and hand that off to the users who want to play with it.

Something thats always bugged me about relational database modelling is how you have to use table relationships for everything. Humans have a special category for ownership (eg Order owns DeliveryAddress), which works differently from other kinds of relationships. Eg Order references Products.<p>This problem is heightened by the fact that a SQL table typically can&#x27;t store composite types. Like, you can&#x27;t make a column of lists-of-strings. Every modelling problem is solved by using <i>more tables</i>. And any nontrivial application ends up with a table explosion. You have to study your database very closely, then write complex, slow, join-heavy queries, just to get the information about simple stuff - like an order.<p>Solving every problem with tables might seem clever from a technical perspective, but its just not how humans think. There&#x27;s a reason document databases like mongodb are popular. Even if they are worse technically, they&#x27;re much easier to use and reason about.<p>There&#x27;s no reason SQL databases couldn&#x27;t support nested data. Postgres sort of does already via JSON fields. But it feels like you have to fight the database to do it.

Prof Murat is calling for examples of how LLMs can help make DBs more usable.<p>I note that Julius.ai is making data analytics easier - just upload some data and ask for some charts and it does an ok job.<p>The problem of going from natural language to SQL is still a far from solved problem. The main benchmark for this is Bird-Bench and accuracy is only in the high 60s - <a href="https:&#x2F;&#x2F;bird-bench.github.io&#x2F;" rel="nofollow">https:&#x2F;&#x2F;bird-bench.github.io&#x2F;</a><p>I have been working on making tables queryable using function calling, where you a fixed number of canned queries based on the primary key and an event-time column. PyData talk on this - <a href="https:&#x2F;&#x2F;www.youtube.com&#x2F;watch?v=dRcjTe5qgwM" rel="nofollow">https:&#x2F;&#x2F;www.youtube.com&#x2F;watch?v=dRcjTe5qgwM</a>

&gt; <i>Users cannot interact with the database directly</i><p>It is not super hard to find UI designed for end users, these days.<p>If you know the basics such as what a relational database is, then here’s is a good UI: <a href="https:&#x2F;&#x2F;visualdb.com" rel="nofollow">https:&#x2F;&#x2F;visualdb.com</a>

How do you create the correct indexes? If we create index for all the conditions in a where clause wouldn’t the number of indexes become too many? I don’t fully grasp how to best create indexes. Does anyone have good reference and examples?

&gt; “Find all flights from Detroit to Beijing” ... which must be joined twice with flight info to express our query<p>Actually this can be done by nested query, without joining. I prefer nested query than joins. Because join is &quot;global&quot; and nested query is &quot;local&quot; (global means interfering with the whole SQL statement). The local one is easier to understand.

For me the join problem doesn&#x27;t exist anymore, because I use the excellent Django ORM for work and hobby projects.<p>This is a SELECT with 5 joins:<p><pre><code> Foo.objects.filter(a__b__c__d__e=3) </code></pre> Types are respected, valid relations exist and invalid relations do not. Clean.

Those who do not learn the relational model are bound to flounder. For most use cases, there is no reducible complexity in the relational model.

Author talked about usability problem with database, it&#x27;s oxymoron usability problem in this site where all the text are in gray which makes reading very painful. Had to use &quot;Darken Text&quot; Chrome ext so it is not killing my eye!

I think the core of this is the age old problem that people would rather invent crappy technologies that end up being a pain instead of taking some time to learn a standard, SQL.<p>SQL is not going to go away. It&#x27;s relatively easy to learn.

&gt; You youngins may not remember, but pre-2005 we had to call a travel agent to book our flights and get paper tickets in return. This sucked, we don&#x27;t have any transparency in to the process, we couldn&#x27;t explore options (price, convenience, dates, airports) and customize our trip. Having access to flight booking via web was really a great improvement for user experience.<p>I&#x27;m going to disagree. I could call my travel agent and say &quot;I am going to Chicago on &lt;date&gt; I need to be there by &lt;time&gt; call me back with a couple of nonstop options. And I&#x27;ll need a room at the Hilton and Towers for two nights.&quot;<p>vs. today I can spend my own time navigating travel sites, avoiding their dark patterns, wondering if I&#x27;m <i>really</i> getting the best prices, making sure I understand the terms (is this refundable? are any checked bags included?) etc and then do the same for the hotel booking.<p>If you work in a business with a travel department or assistants who can manage your travel you have an idea, compared to the hassle of doing it all yourself.