8 ms·

> But it does not let me model records with variable size fields. This restriction seems totally arbitrary Yes it does. Customers have orders which are an arra

> But it does not let me model records with variable size fields. This restriction seems totally arbitrary Yes it does. Customers have orders which are an array. You have two tables then CUSTOMER and ORDER and you JOIN them. Why not just put the orders inside of CUSTOMER? Because now you can't query it, because you don't know how many columns will come out and you can't have disparate columns across rows. So maybe you dump it all in one column, but obviously that has problem in terms of forming relations. Sure, it's a different way of thinking. But its faster, its MUCH safer, the invariants are actually properly specified. Sure you can use mongodb and that will work for wild-westing your way through software. But I wouldn't dare touch a mongo instance without going through the application, because all the constraints are willy-nilly implicitly applied in the application. But I directly view and edit SQL databases daily.

josephg 2y ago
> You have two tables then CUSTOMER and ORDER and you JOIN them. Why not just put the orders inside of CUSTOMER? Because now you can't query it, because you don't know how many columns will come out and you can't have disparate columns across rows. Why wouldn't you be able to query it? You can query JSON fields that contain lists. Why not SQL fields with strongly typed lists? > Sure, it's a different way of thinking. But its faster, its MUCH safer, the invariants are actually properly specified. Why would it be safer? An embedded list has pretty clear and obvious semantics. And as ekimekim said in another comment, postgres already has partial support. Apparently this works today: 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 ); An embedded list also sounds much faster to me - because you don't have to JOIN. Embedding a list promises to the database "I'll always fetch this content in the context of the containing record". Instead of (fetch row) -> (fetch referencing key) -> (fetch rows in child table), the database can simply fetch the associated field directly. > But I wouldn't dare touch a mongo instance without going through the application, because all the constraints are willy-nilly implicitly applied in the application. Yes, I hate mongodb as much as you do. I want explicit types and explicit invariants. But right now mongodb has useful features that are missing / unloved in SQL. How embarrassing. SQL databases should just add support for this approach to data modelling. Its nice to see that postgres is trying exactly that. In programming, I don't have to choose between javascript and assembly. I have nice languages like rust with good type systems and good performance. We can have nice things.
- housecarpenter 2y ago
  As I understand it, the main advantage of having separate CUSTOMER and ORDER tables rather than just have a field on CUSTOMER which is a list of order IDs is that the latter structure makes it easy for someone querying the database to retrieve a list of order IDs for a given customer ID (just look up the value of the list-of-order-IDs field), but difficult to retrieve the customer ID for a given order ID (one would have to go through each customer and search through the list-of-order-IDs field for a match). The former structure, on the other hand, makes both tasks trivial to do with a JOIN.
  - josephg 2y ago
    What you're describing is a straightforward indexing problem. There's nothing stopping the database building an index which can look up customer IDs for a given order ID. You could even, if the database allows it, build a virtual ORDER table projection that you could query directly.

josephg 2y ago

> You have two tables then CUSTOMER and ORDER and you JOIN them. Why not just put the orders inside of CUSTOMER? Because now you can't query it, because you don't know how many columns will come out and you can't have disparate columns across rows. Why wouldn't you be able to query it? You can query JSON fields that contain lists. Why not SQL fields with strongly typed lists? > Sure, it's a different way of thinking. But its faster, its MUCH safer, the invariants are actually properly specified. Why would it be safer? An embedded list has pretty clear and obvious semantics. And as ekimekim said in another comment, postgres already has partial support. Apparently this works today: 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 ); An embedded list also sounds much faster to me - because you don't have to JOIN. Embedding a list promises to the database "I'll always fetch this content in the context of the containing record". Instead of (fetch row) -> (fetch referencing key) -> (fetch rows in child table), the database can simply fetch the associated field directly. > But I wouldn't dare touch a mongo instance without going through the application, because all the constraints are willy-nilly implicitly applied in the application. Yes, I hate mongodb as much as you do. I want explicit types and explicit invariants. But right now mongodb has useful features that are missing / unloved in SQL. How embarrassing. SQL databases should just add support for this approach to data modelling. Its nice to see that postgres is trying exactly that. In programming, I don't have to choose between javascript and assembly. I have nice languages like rust with good type systems and good performance. We can have nice things.

housecarpenter 2y ago
As I understand it, the main advantage of having separate CUSTOMER and ORDER tables rather than just have a field on CUSTOMER which is a list of order IDs is that the latter structure makes it easy for someone querying the database to retrieve a list of order IDs for a given customer ID (just look up the value of the list-of-order-IDs field), but difficult to retrieve the customer ID for a given order ID (one would have to go through each customer and search through the list-of-order-IDs field for a match). The former structure, on the other hand, makes both tasks trivial to do with a JOIN.
- josephg 2y ago
  What you're describing is a straightforward indexing problem. There's nothing stopping the database building an index which can look up customer IDs for a given order ID. You could even, if the database allows it, build a virtual ORDER table projection that you could query directly.