18 ms·
How and why the Relational Model works for databases
- uvdn7 5y agoMaybe the File System shouldn’t be hierarchical but rather relational as well.
- seanhunter 5y agoMicrosoft wondered that too and for a while developed "CairoOFS" as a possible replacement for NTFS. It was intended as a relational "object filesystem" https://betawiki.net/wiki/Microsoft_Cairo https://betawiki.net/wiki/Microsoft_Cairo
- taeric 5y agoAnd then we could have fun looking at the execution plan of our file access. :D Meant in jest; though I think the idea of a "one true way" to access files is a pipe dream. The hierarchical works better on my computer than whatever scheme we've cooked up for our phones.
- Semaphor 5y ago> The hierarchical works better on my computer than whatever scheme we've cooked up for our phones. What do you mean? Hierarchical is how I access files on my phone. Is that an Apple thing?
- taeric 5y agoI thought phones had moved to an "ownership" model where access to files goes through the applications. That said, trying right now, I see there is a files app that seems to be mainly types. I'm assuming that is actually by folder?
- Semaphor 5y ago> I thought phones had moved to an "ownership" model where access to files goes through the applications. There are many file explorers for Android, and they all have a normal browsing interface. The only "ownership" thing I’ve encountered is with the Nextcloud client, where it makes sense as those directories and files are not necessarily on the phone. I’m only on Android 10, but I think I would have heard if Android 12 completely changed things.
- taeric 5y agoYeah. I can only think I was confused by the few times I've tried to mess with the data. I thought I read they were trying to not go folders based.
- jasfi 5y agoWhat is the equivalent of a directory, and of a file? From the relation point of view, what would your tables be? It doesn't map that well, the idea sounds intriguing, but in practice a filesystem seems like a better model for files.
- dagss 5y agoYou would not have a "directory" in the sense you use the word (why would directory be required in a FILE system?) Instead a heap of files and you search for them using metadata (e.g. each file has an associated key value store that you search on). Music catalogues is a good example. In the 90s one vould wonder if the music sitting on HD should be organized as "Music/(genre)/(artist)" or "Music/(artist)/(genre)" as directories. Different choices was best for different persons. Eventually many music players for desktop (e.g. iTunes) just made a different metaphor. And Spotify, Netflix etc do not use a hierarchy but you search for items using metadata. Another example is e.g. /lib/libfoo-1.2.so Where one could instead have libraryname=foo scope=system version=1.2 sharedlibrary=true Or similar
- mnsc 5y ago> Music/(artist)/(genre) Well that's only necessary when your music library contains Zappa.
- jasfi 5y agoThat's not relational, that's closer to a NoSQL store.
- dagss 5y agoThat is orthogonal, NoSQL can be used as a relational DB too. SQL is one way to do a relational DB. Also what I describe is close to SQL if e.g. you declare a set of properties and property types for each "filetype". Relational is one aspect of a DB. Schemaless or not is another aspect.
- zozbot234 5y agoA "file" is just a key-value entry where the "key" is some label in an arbitrary namespace, and the "value" is a blob of bytes. The "directories" of a relational FS would be dynamically generated: they would arise as query result sets based on user input, as opposed to being fixed and materialized on-disk. This is how the relational FS worked in BeOS, but the exact same "virtual directory" feature was also ported to some versions of Windows.
- mftb 5y agoMicrosoft Sharepoint uses SQL Server for all user Item storage, so it's an example of an all relational file store. They did this around the time they were also experimenting with the Cairo a sibling comment mentions. Also as another commenter noted you query on the metadata. Folders/directories for instance are represented as metadata. Installations can also describe very detailed ontologies including ones provided by third parties. It's a lot of work. Edit: I think I was actually thinking of WinFS which came out of Cairo later, around 2000.
- kodemager 5y agoThat means onedrive for business uses sql server as it uses sharepoint online, interesting.
- mftb 5y agoThat's an interesting point. As I recall (unfortunately it's been a while). There was tension at first who would be top dog. SharePoint or OneDrive, and OneDrive won, so SharePoint Online is actually mapped over OneDrive. MS could do that because ironically even though SharePoint On-prem had been entirely SQL Server underneath, SharePoint On-prem devs generally never wrote a line of SQL. They interacted with the SharePoint Object model and that was basically mapped over OneDrive. What OneDrive is underneath I don't think MS had to be as forthcoming about because of it's cloud-based nature. I was on my way out by that point though.
- roenxi 5y agoThe only thing the relational models really gives you is the ability to join two relations, which lets data decompose for storage but re-compose for many different uses. I'm not immediately seeing how that would be useful in a file context, where generally you want to look up a specific blob of data (aka a file). A tree-based file-system is optimised for doing a search from the users perspective, finding a file takes log(files) steps and finding related files is trivially cheap. It is likely hard to outdo that with a relational model.
- klodolph 5y agoThe filesystem is used by different people for different purposes. Any attempt to make sense about “what makes sense” for files is going to be colored very differently depending on what perspective you have. From an end-user perspective, files are documents that I create and I want to be able to find them in different ways. From the perspective of a typical app developer, the filesystem is a hierarchical key-value store. The perspective of a database developer, backup software developer, system administrator, etc. is going to be completely different yet.
- roenxi 5y agoIt is easy to do that though - set up a database. I've met one, maybe 2, people who don't use file systems the conventional way. They're rare and they generally just want a search index as opposed to relational data.
- klodolph 5y ago> It is easy to do that though - set up a database. I think we have really, fundamentally, failed to communicate here. I’m 100% sure we are talking about different things.
- hvidgaard 5y agoI would see it more as a different and better way to store the same and additional information. In a RDBMS an index is already storing references in a tree format. If you map that to files and folders you now have basically the same thing as a file system. Doing it more as a DB enables the OS to use the knowledge from RDBMS for efficiency, which I'm sure rivals the best file systems and it's possible to create multiple indexes and views for other use cases. Our current view on file systems and the knowledge we have is heavily influenced by slow spinning disks, while RDBMS have leveraged RAM a lot more. With todays fast SSDs the file system operates in a reality that is more like RAM than a slow spinning disk.
- dboreham 5y ago> Maybe the File System shouldn’t be hierarchical but rather relational as well How do you know it isn't?
- twofornone 5y agoIn my limited experience, code built around relational databases is difficult to grok. The structure is inverted with respect to typical OOP. Rather than having a sane class hierarchy, where one can start at the top and navigate down to understand how objects are nested, the structure is inverted, piecing together class relationships requires looking at the tables and following foreign keys which effectively point to parent classes. It feels backwards.
- zozbot234 5y agoClass hierarchies are not "sane", they're inherently hard to extend in a way that preserves a consistent semantics. This is exactly what the relational model is intended to fix.
- lelanthran 5y ago> In my limited experience, code built around relational databases is difficult to grok. The structure is inverted with respect to typical OOP. Rather than having a sane class hierarchy, where one can start at the top and navigate down to understand how objects are nested, the structure is inverted, piecing together class relationships requires looking at the tables and following foreign keys which effectively point to parent classes. It feels backwards. Other than your first sentence (which is subjective), you are correct. The only question is whether you are going to mangle the database structure to fit your OO hierarchy or design your program in a non-OO way to fit the relational structure. Since the database will live on long past the program, and will have multiple programs talking to it, it makes sense to design your program around the data, not design the database structure around your program. [EDIT: See https://blogs.tedneward.com/post/the-vietnam-of-computer-science/ https://blogs.tedneward.com/post/the-vietnam-of-computer-sci... for why OO is a terrible design for persistent data]
- klodolph 5y agoI think it’s worth noting that “database-centric” and “app-centric” notions of databases are both found in the wild. That’s how I remembered the difference between MySQL and PostgreSQL—MySQL was what happened when a bunch of app developers needed a database, and it was extremely popular e.g. with the PHP webdev crowd. If you needed to access the database, you went through the app. PostgreSQL is what happened when DBAs designed a database. If you needed access to the database, you connected to the database. A lot of other databases can be understood this way… like how MongoDB further shifts database concepts into the application. (Honestly I’m definitely in your camp… if I need a database, design the database first, then write the code.)
- caditinpiscinam 5y ago> All these database aspects remained virtually unchanged since 1970, which is absolutely remarkable. Thanks to the NoSQL movement, we know it's not because of a lack of trying. Kind of an aside, but I find it odd that people treat "relational" and "SQL" as synonymous (as well as "non-relational" and "NoSQL"). You could make a relational database that was managed with a language other than SQL, right?
- bena 5y agoAs the pirate meme says, "Well yes, but actually no". This is kind of like a sticking effect. It got in at the right time and it was good enough and there wasn't enough interest in developing something to replace it that it just became standard. But if you want to get down to it, any entity data model implemented is kind of an attempt at a replacement.
- manbart 5y agoTutorial D is one such example of a non-SQL relational db language
- datatrashfire 5y agoYou're absolutely correct, but in practice I'm not aware of any relational databases with widespread adoption that don't use SQL.
- bayesian_horse 5y agoRethinkDB comes to mind. "Widespread adoption" is a very fungible term.
- BoiledCabbage 5y agoAs an FYI, I don't belive that's a correct use of the term "fungible".
- bayesian_horse 5y ago
- tyingq 5y agoMost operating systems do have the ability to add key/value pairs to files via extended attributes, like xattr on Linux. That's been around for quite some time. I suppose it goes unused since it's not centrally indexed, and also gets left behind in most types of file transfers.
- SuperCuber 5y agoOne problem I have with relational databases is I don't know of a good way to represent sum types - I remember seeing some possible solutions but they looked very complex and hard to understand (or dbms-specific)
- goodlinks 5y agocan you elaborate a little? i've not heard of the term sum types before and when googling superficailly they dont seem that exciting, particularly for persistent data. When would they not just be a foreign key to table or one column for each allowed datatype (or a mix of the two)? Sorry, I am assuming here its my ignorance thats teh issue not knowing any real word examples of why they are a big deal.
- ogogmad 5y agoI don't know if this explanation is a good one, but I'll try using Haskell syntax. In Haskell, you can have product types like: data CartesianCoordinate = Coord Float Float where an element of this type is expressed as `Coord x y` where x and y are both floats. Examples of elements of this type are `Coord 1.1 0.9` or `Coord -2.9 10.0`, etc. Product types are equivalent to structs in C, if you're familiar with C. But you can also have sum types. Instead of starting with the general idea, I'll point out that C enums are a special case of sum types: data Day = Monday | Tuesday | Wednesday | Thursday | Friday | Saturday | Sunday and then point out linked lists are more representative of the general idea: data ListOfFloats = Node Float ListOfFloats | End where an element of `ListOfFloats` is for instance `Node 1.0 (Node 2.0 End)`, or `Node 0.3 End`, or just `End`. The pipe symbol | is what makes a sum type a sum type. It means that an element of the type is either one possibility or another possibility or another possibility. One final example is a type consisting of all possible mathematical expressions. This is also a sum type: data Expr = Add Expr Expr | Times Expr Expr | Negate Expr | Inverse Expr | Const Float An element of this type can be something like `Add (Const 1.5) (Times (Const 0.2) (Const 2.8))`, which is supposed to represent the expression "1.5 + 0.2*0.8". Interestingly, you can't easily express this type in most OOP languages. In simple set theory parlance, product types refer to Cartesian products, and sum types are set-theoretic unions. The relevance to relational databases is that each row of a table corresponds to an element of some product type. Each row of the same table has the same product type. But there is no means defining a "table" whose elements belong to a sum type as opposed to a product type. Why is that?
- brianmcc 5y agoThe best advice I can give is that you can think of your app and its data store as either: #1 code is of prime importance, data store is simply a "bucket" for its data #2 data is of prime importance, code is simply the means to read/write/display it In my personal experience, #2 is a way better way to work. Apps can come and go, but data can last a long time, and the better your database is modelled the better the outcomes you'll have long term. Corollary - I have seen some abject disasters where #1 has been adopted. Not necessarily just because of #1 alone but it's certainly been a major factor.
- deleted 5y ago[deleted]
- ngcc_hk 5y agoOther than parts and bill-of-material with self loop, I do not know why you do not use data model. Show me the code I am confused. Show me the data, …
- goodlinks 5y agoexactly. give me an SME and direct access to the DB. i dont need to see any code, not even the UI. working on master data and BI changed my whole perspective on how to value software and what i demand of any business application going even vaguely close to business critical processes (hint: unfettered db access to cut through your bullshit.. its my damn data thank you :D )
- _xnmw 5y agoAnyone who thinks they don't need a relational database eventually ends up reinventing and rewriting aspects of an RDBMS, except badly. You're just kicking the can down the road.
- tuatoru 5y ago> What is a relation in English? Actually the dictionary definition works pretty well in this case. The following definition comes from Merriam Webster. an aspect ... that connects two or more things or parts as being or belonging or working together This is needlessly and wrongly freighting the model with a semantic interpretation. A relation is a subset of a cross-product of sets. Nothing more.
- Mikhail_Edoshin 5y agoBut there is a semantic interpretation: it's a set of true statements out of all possible statements.
- samatman 5y ago"Access Path Dependence" continues to plague our computers because they are a built-in assumption of file systems. This is really showing its age. I want my computer to think in terms of what data is, where it came from, any other metadata I received it with, and any metadata I've added, notably, but not primarily, the various "places" I've put it. The fact that I can't retrieve the URL I downloaded anything from, years later, no mater how many times I've moved it, is just shameful. It's cheap information our tools could be preserving but aren't. So if you ask me what I think of the relational model, I'll tell you: it's a good idea and we should try it.
- emadda 5y agoIf you want to use SQL with your Stripe data, I recently released a CLI called tdog that downloads your Stripe data to a SQL database. https://table.dog https://table.dog
- bob1029 5y agoI have grown to understand that the relational model is the answer for solving all hyper-complex problems. The Out of the Tar Pit paper was a revolution for my understanding of how to approach properly hard things: http://curtclifton.net/papers/MoseleyMarks06a.pdf http://curtclifton.net/papers/MoseleyMarks06a.pdf The sacred artifact in this paper is Chapter 9: Functional Relational Programming. Based upon inspiration in this paper, we have developed a hybrid FRP system where we map our live business state to a SQLite database (in memory) and then use queries defined by the business to determine logical outcomes or projections of state for presentation. Assuming you have all facts contained in appropriate tables, there is always some SQL query you could write to give the business what they want. An example: > Give me a SQL rule that says the submit button is disabled if the email address or phone number are blank/null on their current order. --Disable Order Submit Button Rule SELECT 1 -- 1 == true, 0 == false FROM Customer c, Order o WHERE o.CustomerId = c.Id AND c.IsActiveCustomer = 1 AND o.IsActiveOrder = 1 AND (IsNullOrEmpty(o.EmailAddress) OR IsNullOrEmpty(o.PhoneNumber)) I hope the advantages of this are becoming clear - You can have non-developers (ideally domain experts with some SQL background) build most of your complex software for you. No code changes are required when SQL changes. The relational model in this context is powerful because it is something that most professionals can adopt and collaborate with over time. You don't have to be a level 40 code wizard to understand that a Customers table is very likely related to a ShoppingCarts table by way of some customer identity. If anyone starts to glaze over at your schema diagrams, just move everything into excel and hand the stakeholders some spreadsheets with example data.
- freeqaz 5y agoDo you have any additional resources about this model of thought? It's like Redux on steroids lol. I wonder if anybody has done a SQLite-as-the-Store pattern library for front end apps before. I'd use the hell out of that!
- bob1029 5y agoOne of these days I am just going to have to write a book about it. There are so many layers and perspectives to consider. Maybe another small rant about our roadmap will help you see more clearly how it could work for you: I am currently looking at an iteration that will use event sourcing at the core (i.e. append-only logs which record the side-effects of commands), with real-time replays of these events into SQLite databases (among other in-memory working sets). The SQLite databases would serve as the actual business customer front-end. We would also now have a very powerful audit log query capability that would go directly against this event source. I would just think about the database as the layer the business can communicate with. It is your internal/technical customer. As long as that database is proper, everything else downstream works without you thinking about it. The biggest reason for pursing this is to decouple the schema from the logical reality as much as possible. The business likes to change their mind (sometimes for very good reason) and we have to keep them honest at some level. As proposed here, that source of truth will be the read-only event logs. When you look at this on a whiteboard, you may recognize that it resembles a textbook definition of CQRS. Perhaps try reading up on: CQRS, event sourcing, database normalization, SQLite's application-defined function capability, and anything else that looks adjacent.
- ashvardanian 5y agoHave opened this thread a couple of times today, hoping to see comments disagreeing with the post. Still no such comments, so I’ll take the duty… hopefully not causing a I have been programming for ~15 years, the majority of my life. Web, mobile, HPC, CUDA, assembly for x86 and ARM, kernel modules, LLVM plugins, and databases… Not once in my career I found the concept of relational databases efficient or relevant. They are frustrating to use, exceptionally slow and generally provide SQLs, or other DSLs, which is the most archaic form of query representation I could think of - not binary and not general-purpose. It reminds me of no-code development platforms. They may work (not very well) for some super simple tasks, but as soon as you want to do something at least remotely non-trivial, they put more barriers, then provide help. From personal experience, again, I have grown to hate frontend development so much, that have tried a dozen of website builders, before reverting to good old HTML/CSS (plus a bit of JS) every time I wanted to refresh my blog or companies website. Plus, I wouldn’t immediately dismiss the concept of Graph Databases. If we want to be truly canonical, we wouldn’t create hundreds of columns in our tables, with just a few relations. The ideology is that every unique “type” (in any sense you prefer), should be in its own table, linked with the other “types” in other tables… Then theory ends and starts practice. Try implementing a fast JOIN in a relational database. Then increase the depth to 3, tracing the relations of relations of relations. Even in a non distributed case it is a horror. Both the nested SQL queries and the program that will be evaluating them. Graph DBs are designed to solve specifically that issue really well. Another point: how “relational algebra” suddenly makes smth superior to anything else? It’s not a Grand Unified Theory of Physics, not rocket science and not even Graph Theory for that matter. The latter being the biggest and most studied branch of Theoretical Computer Science with brilliant theorems being published even today. Not saying that todays popular Graph DBs are good (they are mostly disgusting), but I would still much rather think of my data as a graph, than a table with some foreign keys
- vaughan 5y agoMy big realization was that the relational model is a constraint on your data so that relational algebra can optimize your queries for perf. But these constraints cause a whole range of issues, which always result in a poorly modeled domain as people try to work around the limits of the optimizer or the expressiveness of SQL. I've always felt that what we need is a way to maintain a logical schema (E-R diagram / graph schema), and then the physical schema is automatically generated along with denormalizations for perf as necessary. A graph db is simply denormalizing its joins using index-free adjacency.
- vaughan 5y agoI've come to realize the relational model is not a great fit for most data. It constrains your data model for the purpose of representing your queries in relational calculus, which allows a corresponding relational algebra to operate on them to help optimize disk access. This comes at a cost. Although, if this is your primary goal then that's fine, which it has been for many. Data these days is deeply nested or document-based, and encoding this in the relational model is incredibly unwieldy, with huge ugly join queries, and the planner starts making random guesses at 6 joins or so. Everyone ends up with a rigid, and poorly normalized physical schema to suit the sql planner. Think about all the times you avoid M-M joins because your queries will explode in complexity. Also, pretty much every app these days wants streaming updates to queries. The optimizer doesn't optimize for streaming updates and most streaming is done by polling. Incremental view maintenance is also very difficult to achieve as well as streaming SQL.
- mshaler 5y agoTwo thoughts here: - This is why ETL + variants are hard - Graph databases are (arguably because HN) better than the relational model (e.g., flexibility, ease of modeling, accessibility of algorithms/analytics)