14 ms·

Something thats always bugged me about relational database modelling is how you have to use table relationships for everything. Humans have a special category f

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. This problem is heightened by the fact that a SQL table typically can't store composite types. Like, you can't make a column of lists-of-strings. Every modelling problem is solved by using more tables. 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. Solving every problem with tables might seem clever from a technical perspective, but its just not how humans think. There's a reason document databases like mongodb are popular. Even if they are worse technically, they're much easier to use and reason about. There's no reason SQL databases couldn'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.

codr7 2y ago
Easier to reason about in that they don't force you to clarify your thoughts, which will come back to bite your head off. And there's a pretty significant long term price to be paid. I would rather write Cobol than deal with MongoDB.
- Spivak 2y ago
  This is all nice and good but I've never once seen someone correctly clarify their thoughts ahead of time that doesn't end up being the wrong model as the code evolves. The only schemas that survive long term are the ones that create such a tangled ball of foreign keys that migrations are infeasible. Great advice for anyone looking to leave a legacy.
  - codr7 2y ago
    At least they exist, and force you to refactor the code to match reality. Document databases tend to take on a life of their own; and since they encourage shortcuts, the slope is typically down.
- yas_hmaheshwari 2y ago
  Nicely put! I see new grads talking about MERN stack (with M for Mongo), and always have an urge to correct them that replace M with Mysql
jeeyoungk 2y ago
+1 For example, BigQuery has natural support for arrays and nested data, and it's quite nice / essential for good data modeling. For example, "tags" can be stored as `Array<Struct<Key, Value>>`, and this can be used to implement things like, "search with fields with particular tags". This reduces the cognitive burden of remembering which tables join with which, especially if we know that a relationship is solely relevant in one context. I.e. Tags can only be joined to the main table, and no other joins are sensical.
minkles 2y ago
I think this is a poor understanding and laziness. SQL is type and schema first and people hate that because it makes things hard and complicated up front. Table explosions are rare if you know what you are doing. Many people don't any more. As for join heavy, complexity, this is not necessarily a problem in reality. It's incredibly easy to scale this out to huge systems (relatively) cheaply. Believe me as they scale up, they look way less hard and less complicated than arbitrary and poorly enforced schemas in document databases. I could write an essay on how to fuck up MongoDB (or any document store) because I spent nearly 2 years unfucking one.
- josephg 2y ago
  I’d love to read that essay. But to me sql looks like crap json - it’s like json except if (for purely historical reasons) we didn’t have objects or lists except at the top level of a json blob. Claiming it’s a moral failing on my part for wanting nice, intuitive data structures is a bit rich. Go on, next why don’t you tell me that json is bad and we should use xml. Or c is lazy and we should use assembler. Without more detail, that’s all I hear - moral justifications for a dated design. And the design is dated. As far as I can tell, the reason for the current design is because early sql databases had a fixed byte size for fields. So in that world, it would be impossible to embed a list in a cell. But that limitation hasn’t existed in modern sql databases for decades now. Why can’t we have nice things? I don’t see anything in sql that precludes embedded lists and objects in table cells. Postgres even - sort of - supports it already via json fields.
  - cess11 2y ago
    If JSON is enough for your use case, go ahead, use it. The big RDBM systems support it rather well now, so you can bring it straight into the SQL world too. XML is for the non-trivial case, where having schemas and tooling around them for validation and code generation becomes useful. XML also has a default programming language for querying and transformations, allowing you to implement adapters from CSV, JSON or whatever into XML, and then between XML schemas. XML can be used in a JSON-like way if that's what you want: XPath. When programming against XML it's usually not what you want though, in practice it tends to be nicer to use the schema to set up an object hierarchy native to the programming language and unmarshal into that. But there is at least one XPath oriented database, eXist, and it's pretty ergonomic if you find yourself with a lot of XML and isn't sure what it contains or what to use it for. I find it easier to work with than the NoSQL JSON varieties I come across professionally, and when I have Questions For The Customer it craps out PDF:s I can send them, which doesn't seem that easy to do with Mongo or Firestore. As for SQL databases, they come in a lot of shapes and colours. I'm not so sure there is a JSON-first database equivalent of DuckDB, for example. Maybe I'm wrong, it's not a specialty of mine.
stult 2y ago
Your argument is a variant of the object–relational impedance mismatch problem[1]. It's easier for us to reason about objects (or functions and types) than it is to think in terms of SQL tables, so it is much easier to develop complex logic or domain models in general purpose programming languages. Yet data storage and retrieval is much, much more efficient with relational databases, and it is generally a good practice to logically separate the data storage layer from the rest of the system anyway. But implementing that layer to translate from the relational model to the object model frequently involves tons of finicky, manually crafted SQL statements embedded in general purpose code where the linting/type checking/static analysis tooling often isn't great for the embedded SQL. The only other alternatives are using an ORM that automagically handles most if not all of the relational-object mapping or a NoSQL solution like Mongo that avoids the mismatch altogether. Both those alternatives typically involve sacrificing performance and scalability, however, and the limitations and quirks of each frequently contribute to bugs (e.g., schema drift in document DBs, or implementation details of the ORM that make it hard to map nested relationships). [1] https://en.wikipedia.org/wiki/Object%E2%80%93relational_impedance_mismatch https://en.wikipedia.org/wiki/Object%E2%80%93relational_impe...
- rtpg 2y ago
  I don't think it's just about storage and retrievel being efficient. Sometimes it _is_ easier to think in terms of tables. I have a bunch of posts, written by various authors, on various sites. Store that in some OOP way, with ownership going in some direction (For example, sites own posts). Now look up the most recent posts for authors. Look up the most recent posts per site. Look up the most prolific authors. Do the authors work cross-site? Maybe they don't but people use the same name across sites. There are plenty of times I have put things into a database not for perf reasons but simply because it's easier to do queries that way than to write a bunch of bespoke logic at each variant of certain queries, simply due to ownership arrows being all over the place
Swizec 2y ago
> Like, you can't make a column of lists-of-strings. Most relational databases have supported lists-of-strings (arrays) for about 10 years now. All of them supported lists-of-strings even before that, if you were okay doing a bit of app-level work on top of your SQL. Relational databases these days support full JSON. And they're faster at it than many json-first databases :) edit: Actually I'm wrong. SQL added support for arrays in 1999, a full 25 years ago. https://en.wikipedia.org/wiki/SQL:1999 https://en.wikipedia.org/wiki/SQL:1999 https://stackoverflow.com/questions/30109111/at-what-version-of-the-sql-standard-did-arrays-appear https://stackoverflow.com/questions/30109111/at-what-version...
- josephg 2y ago
  I didn't know that - thanks!
sgarland 2y ago
Non-scalar data violates 1NF [0]. You _can_ have composite types, arrays, JSON, etc. as others have said, but you are likely giving up relational integrity in doing so, which is the raison d'être of RDBMS. The reason they're so strict is to protect you from yourself (and others). When you get a foreign key violation, that should be cause for alarm, not an annoyance to figure out how to overcome. > Solving every problem with tables might seem clever from a technical perspective, but its just not how humans think. Not without practice, no. You can in fact learn to do data modeling. [0]: https://en.wikipedia.org/wiki/First_normal_form https://en.wikipedia.org/wiki/First_normal_form
- sa46 2y ago
  Composite types can be atomic--a string is a composite type. The point (123, 456) is a composite type but is atomic. Good discussion at https://dba.stackexchange.com/questions/2342/what-is-atomic-relation-in-first-normal-form https://dba.stackexchange.com/questions/2342/what-is-atomic-...
  - sgarland 2y ago
    Good point. I’ll also add to my previous comment, that it’s fine to violate normal form if you are aware that you’re doing so, and have a good reason. There are some instances where an array happens to make excellent sense from a performance standpoint, for example, and storing it in RDBMS doesn’t magically make everything explode. I do disagree with people’s tendency to shift towards treating RDBMS as a way to store schema in JSON, though. Not only is it massively unperformant, and indexing it is a pain, but you can quite easily get yourself into situations where there are multiple versions of your schema-within-a-schema, and that can be a nightmare.
    - josephg 2y ago
      I don't think I was clear above. I want embedded objects. I wish there was a more "database-native" way to do it than using JSON, and all the ugliness that JSON brings. (Like dynamic typing, schema-within-a-schema, more complex indexing & querying, etc.) Sounds like something the databases should be able to provide natively, without needing to use JSON at all. (And apparently support for this sort of stuff is slowly improving! Thanks to all the sibling comments for details!)
AdieuToLogic 2y ago
> Something thats always bugged me about relational database modelling is how you have to use table relationships for everything. This is pretty much the definition of what a RDBMS is. The tables provide the relations via designated columns. Relation navigation is therefore possible in either direction given an established relationship and very often possible starting from any table in the pathological case. If this conceptual model is not appropriate, it is best not to use it to begin with. > This problem is heightened by the fact that a SQL table typically can't store composite types. That is not what RDBMS' are designed to do, even though many vendors now support some form of composite data types (like JSON). Just like a moped is not designed to move a family across a continent. Pick the persistent storage mechanism which best fits the problem at hand: - If the problem calls for a document structure, use a document database. - If the problem calls for a directed graph structure, use a graph database. - If the problem calls for a relational structure, use a RDBMS. - If the problem calls for some combination of the above, consider using multiple storage technologies. None are perfect, but starting with the most appropriate persistent store can eliminate the weaknesses others would introduce in the same solution space.
ekimekim 2y ago
This is changing. Postgres (as you noted) supports JSON, and it also has excellent native support for arrays, range types, and composite types. For example: CREATE TYPE number_with_unit ( value DOUBLE PRECISION, unit TEXT ); CREATE TABLE example ( height number_with_unit, -- our composite type, eg. (6, 'ft') or (180, 'cm') known_aliases TEXT[], -- list of string active_times TSRANGE, -- time range, ie. (start, end) timestamp pair ); SELECT height.unit, known_aliases[1] AS primary_alias, upper(active_times) - lower(active_times) AS time_active FROM example;
- globular-toast 2y ago
  You need to be aware of the tradeoffs, though. For example, adding an alias in your example could easily result in a lost update: if two users add an alias at the same time the second write will overwrite the first. You can go up an isolation level or check it in your application (do an `UPDATE ... WHERE` then check it went through), of course, but with relational tables you get this for free without any worry or performance impact.
globular-toast 2y ago
Like some siblings mention, this is the object-relational impedance mismatch. A decent ORM like SQLAlchemy goes some way to closing this gap. Less good ORMs like Django don't help much at all, though. When you talk about "just" using nested data in your database you seem to be forgetting a whole load of problems that relational databases solve like concurrency issues, write performance, data integrity and consistency etc. The book Designing Data-Intensive Applications by Kleppmann is an essential read.
- boxed 2y ago
  Heh. I was just going to comment on how Djangos ORM is great because it cleanly and transparently solves the number one problem of the paper: the horrible joins. SQLAlchemy however falls flat on its face in this regard.
  - globular-toast 2y ago
    Django makes querying easier by generating the code for the joins, but you're still joining tables. The point about SQLAlchemy was specifically about the issue mentioned by the GP, namely having to make more tables for composites. SQLAlchemy does a better job of mapping db tables back to regular objects. With Django you get the `alias_set` stuff but it never lets you forget that this is really a database table. Django uses the active record pattern while SQLAlchemy is a data mapper.
boxed 2y ago
JSON fields are horrible though, as they aren't validated so you now have backwards compatibility problems in your SCHEMA. PER ROW. Like in Mongo.
zie 2y ago
I feel like you have never bothered to fully learn SQL and databases, as if you had, you wouldn't have said most of what you have said. Certainly some problems are not friendly to SQL databases, but many problems are, and lots of problems are being added into SQL databases too. I would not be surprised in the least if LLM's start getting stored in SQL databases eventually, as you probably can reduce their storage to tables and SQL databases are amazing at table storage. I agree it's not really how many humans think(though that can be said about many computer things), but that doesn't mean you can't learn how to do it.

codr7 2y ago

Easier to reason about in that they don't force you to clarify your thoughts, which will come back to bite your head off. And there's a pretty significant long term price to be paid. I would rather write Cobol than deal with MongoDB.

Spivak 2y ago
This is all nice and good but I've never once seen someone correctly clarify their thoughts ahead of time that doesn't end up being the wrong model as the code evolves. The only schemas that survive long term are the ones that create such a tangled ball of foreign keys that migrations are infeasible. Great advice for anyone looking to leave a legacy.
- codr7 2y ago
  At least they exist, and force you to refactor the code to match reality. Document databases tend to take on a life of their own; and since they encourage shortcuts, the slope is typically down.
yas_hmaheshwari 2y ago
Nicely put! I see new grads talking about MERN stack (with M for Mongo), and always have an urge to correct them that replace M with Mysql

jeeyoungk 2y ago

+1 For example, BigQuery has natural support for arrays and nested data, and it's quite nice / essential for good data modeling. For example, "tags" can be stored as `Array<Struct<Key, Value>>`, and this can be used to implement things like, "search with fields with particular tags". This reduces the cognitive burden of remembering which tables join with which, especially if we know that a relationship is solely relevant in one context. I.e. Tags can only be joined to the main table, and no other joins are sensical.

minkles 2y ago

I think this is a poor understanding and laziness. SQL is type and schema first and people hate that because it makes things hard and complicated up front. Table explosions are rare if you know what you are doing. Many people don't any more. As for join heavy, complexity, this is not necessarily a problem in reality. It's incredibly easy to scale this out to huge systems (relatively) cheaply. Believe me as they scale up, they look way less hard and less complicated than arbitrary and poorly enforced schemas in document databases. I could write an essay on how to fuck up MongoDB (or any document store) because I spent nearly 2 years unfucking one.

josephg 2y ago
I’d love to read that essay. But to me sql looks like crap json - it’s like json except if (for purely historical reasons) we didn’t have objects or lists except at the top level of a json blob. Claiming it’s a moral failing on my part for wanting nice, intuitive data structures is a bit rich. Go on, next why don’t you tell me that json is bad and we should use xml. Or c is lazy and we should use assembler. Without more detail, that’s all I hear - moral justifications for a dated design. And the design is dated. As far as I can tell, the reason for the current design is because early sql databases had a fixed byte size for fields. So in that world, it would be impossible to embed a list in a cell. But that limitation hasn’t existed in modern sql databases for decades now. Why can’t we have nice things? I don’t see anything in sql that precludes embedded lists and objects in table cells. Postgres even - sort of - supports it already via json fields.
- cess11 2y ago
  If JSON is enough for your use case, go ahead, use it. The big RDBM systems support it rather well now, so you can bring it straight into the SQL world too. XML is for the non-trivial case, where having schemas and tooling around them for validation and code generation becomes useful. XML also has a default programming language for querying and transformations, allowing you to implement adapters from CSV, JSON or whatever into XML, and then between XML schemas. XML can be used in a JSON-like way if that's what you want: XPath. When programming against XML it's usually not what you want though, in practice it tends to be nicer to use the schema to set up an object hierarchy native to the programming language and unmarshal into that. But there is at least one XPath oriented database, eXist, and it's pretty ergonomic if you find yourself with a lot of XML and isn't sure what it contains or what to use it for. I find it easier to work with than the NoSQL JSON varieties I come across professionally, and when I have Questions For The Customer it craps out PDF:s I can send them, which doesn't seem that easy to do with Mongo or Firestore. As for SQL databases, they come in a lot of shapes and colours. I'm not so sure there is a JSON-first database equivalent of DuckDB, for example. Maybe I'm wrong, it's not a specialty of mine.

stult 2y ago

Your argument is a variant of the object–relational impedance mismatch problem[1]. It's easier for us to reason about objects (or functions and types) than it is to think in terms of SQL tables, so it is much easier to develop complex logic or domain models in general purpose programming languages. Yet data storage and retrieval is much, much more efficient with relational databases, and it is generally a good practice to logically separate the data storage layer from the rest of the system anyway. But implementing that layer to translate from the relational model to the object model frequently involves tons of finicky, manually crafted SQL statements embedded in general purpose code where the linting/type checking/static analysis tooling often isn't great for the embedded SQL. The only other alternatives are using an ORM that automagically handles most if not all of the relational-object mapping or a NoSQL solution like Mongo that avoids the mismatch altogether. Both those alternatives typically involve sacrificing performance and scalability, however, and the limitations and quirks of each frequently contribute to bugs (e.g., schema drift in document DBs, or implementation details of the ORM that make it hard to map nested relationships). [1] https://en.wikipedia.org/wiki/Object%E2%80%93relational_impedance_mismatch https://en.wikipedia.org/wiki/Object%E2%80%93relational_impe...

rtpg 2y ago
I don't think it's just about storage and retrievel being efficient. Sometimes it _is_ easier to think in terms of tables. I have a bunch of posts, written by various authors, on various sites. Store that in some OOP way, with ownership going in some direction (For example, sites own posts). Now look up the most recent posts for authors. Look up the most recent posts per site. Look up the most prolific authors. Do the authors work cross-site? Maybe they don't but people use the same name across sites. There are plenty of times I have put things into a database not for perf reasons but simply because it's easier to do queries that way than to write a bunch of bespoke logic at each variant of certain queries, simply due to ownership arrows being all over the place

Swizec 2y ago

> Like, you can't make a column of lists-of-strings. Most relational databases have supported lists-of-strings (arrays) for about 10 years now. All of them supported lists-of-strings even before that, if you were okay doing a bit of app-level work on top of your SQL. Relational databases these days support full JSON. And they're faster at it than many json-first databases :) edit: Actually I'm wrong. SQL added support for arrays in 1999, a full 25 years ago. https://en.wikipedia.org/wiki/SQL:1999 https://en.wikipedia.org/wiki/SQL:1999 https://stackoverflow.com/questions/30109111/at-what-version-of-the-sql-standard-did-arrays-appear https://stackoverflow.com/questions/30109111/at-what-version...

josephg 2y ago
I didn't know that - thanks!

sgarland 2y ago

Non-scalar data violates 1NF [0]. You _can_ have composite types, arrays, JSON, etc. as others have said, but you are likely giving up relational integrity in doing so, which is the raison d'être of RDBMS. The reason they're so strict is to protect you from yourself (and others). When you get a foreign key violation, that should be cause for alarm, not an annoyance to figure out how to overcome. > Solving every problem with tables might seem clever from a technical perspective, but its just not how humans think. Not without practice, no. You can in fact learn to do data modeling. [0]: https://en.wikipedia.org/wiki/First_normal_form https://en.wikipedia.org/wiki/First_normal_form

sa46 2y ago
Composite types can be atomic--a string is a composite type. The point (123, 456) is a composite type but is atomic. Good discussion at https://dba.stackexchange.com/questions/2342/what-is-atomic-relation-in-first-normal-form https://dba.stackexchange.com/questions/2342/what-is-atomic-...
- sgarland 2y ago
  Good point. I’ll also add to my previous comment, that it’s fine to violate normal form if you are aware that you’re doing so, and have a good reason. There are some instances where an array happens to make excellent sense from a performance standpoint, for example, and storing it in RDBMS doesn’t magically make everything explode. I do disagree with people’s tendency to shift towards treating RDBMS as a way to store schema in JSON, though. Not only is it massively unperformant, and indexing it is a pain, but you can quite easily get yourself into situations where there are multiple versions of your schema-within-a-schema, and that can be a nightmare.
  - josephg 2y ago
    I don't think I was clear above. I want embedded objects. I wish there was a more "database-native" way to do it than using JSON, and all the ugliness that JSON brings. (Like dynamic typing, schema-within-a-schema, more complex indexing & querying, etc.) Sounds like something the databases should be able to provide natively, without needing to use JSON at all. (And apparently support for this sort of stuff is slowly improving! Thanks to all the sibling comments for details!)

AdieuToLogic 2y ago

> Something thats always bugged me about relational database modelling is how you have to use table relationships for everything. This is pretty much the definition of what a RDBMS is. The tables provide the relations via designated columns. Relation navigation is therefore possible in either direction given an established relationship and very often possible starting from any table in the pathological case. If this conceptual model is not appropriate, it is best not to use it to begin with. > This problem is heightened by the fact that a SQL table typically can't store composite types. That is not what RDBMS' are designed to do, even though many vendors now support some form of composite data types (like JSON). Just like a moped is not designed to move a family across a continent. Pick the persistent storage mechanism which best fits the problem at hand: - If the problem calls for a document structure, use a document database. - If the problem calls for a directed graph structure, use a graph database. - If the problem calls for a relational structure, use a RDBMS. - If the problem calls for some combination of the above, consider using multiple storage technologies. None are perfect, but starting with the most appropriate persistent store can eliminate the weaknesses others would introduce in the same solution space.

ekimekim 2y ago

This is changing. Postgres (as you noted) supports JSON, and it also has excellent native support for arrays, range types, and composite types. For example: CREATE TYPE number_with_unit ( value DOUBLE PRECISION, unit TEXT ); CREATE TABLE example ( height number_with_unit, -- our composite type, eg. (6, 'ft') or (180, 'cm') known_aliases TEXT[], -- list of string active_times TSRANGE, -- time range, ie. (start, end) timestamp pair ); SELECT height.unit, known_aliases[1] AS primary_alias, upper(active_times) - lower(active_times) AS time_active FROM example;

globular-toast 2y ago
You need to be aware of the tradeoffs, though. For example, adding an alias in your example could easily result in a lost update: if two users add an alias at the same time the second write will overwrite the first. You can go up an isolation level or check it in your application (do an `UPDATE ... WHERE` then check it went through), of course, but with relational tables you get this for free without any worry or performance impact.

globular-toast 2y ago

Like some siblings mention, this is the object-relational impedance mismatch. A decent ORM like SQLAlchemy goes some way to closing this gap. Less good ORMs like Django don't help much at all, though. When you talk about "just" using nested data in your database you seem to be forgetting a whole load of problems that relational databases solve like concurrency issues, write performance, data integrity and consistency etc. The book Designing Data-Intensive Applications by Kleppmann is an essential read.

boxed 2y ago
Heh. I was just going to comment on how Djangos ORM is great because it cleanly and transparently solves the number one problem of the paper: the horrible joins. SQLAlchemy however falls flat on its face in this regard.
- globular-toast 2y ago
  Django makes querying easier by generating the code for the joins, but you're still joining tables. The point about SQLAlchemy was specifically about the issue mentioned by the GP, namely having to make more tables for composites. SQLAlchemy does a better job of mapping db tables back to regular objects. With Django you get the `alias_set` stuff but it never lets you forget that this is really a database table. Django uses the active record pattern while SQLAlchemy is a data mapper.

boxed 2y ago

JSON fields are horrible though, as they aren't validated so you now have backwards compatibility problems in your SCHEMA. PER ROW. Like in Mongo.

zie 2y ago

I feel like you have never bothered to fully learn SQL and databases, as if you had, you wouldn't have said most of what you have said. Certainly some problems are not friendly to SQL databases, but many problems are, and lots of problems are being added into SQL databases too. I would not be surprised in the least if LLM's start getting stored in SQL databases eventually, as you probably can reduce their storage to tables and SQL databases are amazing at table storage. I agree it's not really how many humans think(though that can be said about many computer things), but that doesn't mean you can't learn how to do it.