5 ms·

I mean 'tables' inside 'tables', 0NF. If I have a list of restaurants with their reviews, naturally, I have a table of restaurants, each of which contains a tab

I mean 'tables' inside 'tables', 0NF. If I have a list of restaurants with their reviews, naturally, I have a table of restaurants, each of which contains a table of reviews. If I have a table of nested comments, naturally, I have a table of comments, each of which recursively contains a table of child comments. Those are some of the most common use cases for data presentation; and SQL-based DBs are not flexible enough to handle them in a straightforward way.

liontwist 2y ago
No. This is binding your data structure to a single access pattern. You can get all the reviews for a restaurant. Now what if you want all the reviews left by a particular user? I invite you to read the original relational database paper which addresses this exact use case: A Relational Model of Data for Large Shared Data Banks
- LudwigNagasena 2y ago
  Storing such data structure would be binding to a single access pattern, I agree. But a query to retrieve such structure is simply convoluted for no reason to the point of many people resorting to hacks like json_agg.
  - liontwist 2y ago
    Make a SQL view for the tree and a function in the client application which can read a tree in its linear projected form.
    - LudwigNagasena 2y ago
      If SQL were flexible, I wouldn’t need to create a whole view for a single query.
      - liontwist 2y ago
        You’re telling me there are two presentations of reviews (by restaurant and by user) but it’s too much work to define a query for those two views. I guess your app can just not support that feature and keep the tree? What do you want to hear?
yellowapple 2y ago
PRAGMA foreign_keys = ON; > If I have a list of restaurants with their reviews, naturally, I have a table of restaurants, each of which contains a table of reviews. CREATE TABLE restaurants (id INTEGER PRIMARY KEY, name); CREATE TABLE reviews (timestamp, restaurant REFERENCES restaurants(id), stars, message); INSERT INTO restaurants (name) VALUES (...); INSERT INTO reviews (timestamp, restaurant, stars, message) VALUES (...); SELECT rv.timestamp, rv.stars, rv.message FROM reviews AS rv, restaurants AS rs WHERE rv.restaurant = rs.id AND rs.name = "Foo's Bar-B-Q"; > If I have a table of nested comments, naturally, I have a table of comments, each of which recursively contains a table of child comments. CREATE TABLE comments (id INTEGER PRIMARY KEY, parent REFERENCES comments(id), body); INSERT INTO comments (parent, body) VALUES (...); WITH RECURSIVE tree AS ( SELECT id, parent, body, CAST(id AS TEXT) AS sequence FROM comments WHERE parent IS NULL UNION ALL SELECT c.id, c.parent, c.body, (sequence || '-' || c.id) AS sequence FROM comments AS c JOIN tree AS t ON t.id = c.parent ) SELECT t.sequence, t.body FROM tree AS t LEFT JOIN comments ON t.parent = comments.id ORDER BY t.sequence; Point being: what one "naturally" has is a matter of perspective. Trees can always be flattened into tables that reference other tables (or even themselves).

liontwist 2y ago

No. This is binding your data structure to a single access pattern. You can get all the reviews for a restaurant. Now what if you want all the reviews left by a particular user? I invite you to read the original relational database paper which addresses this exact use case: A Relational Model of Data for Large Shared Data Banks

LudwigNagasena 2y ago
Storing such data structure would be binding to a single access pattern, I agree. But a query to retrieve such structure is simply convoluted for no reason to the point of many people resorting to hacks like json_agg.
- liontwist 2y ago
  Make a SQL view for the tree and a function in the client application which can read a tree in its linear projected form.
  - LudwigNagasena 2y ago
    If SQL were flexible, I wouldn’t need to create a whole view for a single query.
    - liontwist 2y ago
      You’re telling me there are two presentations of reviews (by restaurant and by user) but it’s too much work to define a query for those two views. I guess your app can just not support that feature and keep the tree? What do you want to hear?

yellowapple 2y ago

PRAGMA foreign_keys = ON; > If I have a list of restaurants with their reviews, naturally, I have a table of restaurants, each of which contains a table of reviews. CREATE TABLE restaurants (id INTEGER PRIMARY KEY, name); CREATE TABLE reviews (timestamp, restaurant REFERENCES restaurants(id), stars, message); INSERT INTO restaurants (name) VALUES (...); INSERT INTO reviews (timestamp, restaurant, stars, message) VALUES (...); SELECT rv.timestamp, rv.stars, rv.message FROM reviews AS rv, restaurants AS rs WHERE rv.restaurant = rs.id AND rs.name = "Foo's Bar-B-Q"; > If I have a table of nested comments, naturally, I have a table of comments, each of which recursively contains a table of child comments. CREATE TABLE comments (id INTEGER PRIMARY KEY, parent REFERENCES comments(id), body); INSERT INTO comments (parent, body) VALUES (...); WITH RECURSIVE tree AS ( SELECT id, parent, body, CAST(id AS TEXT) AS sequence FROM comments WHERE parent IS NULL UNION ALL SELECT c.id, c.parent, c.body, (sequence || '-' || c.id) AS sequence FROM comments AS c JOIN tree AS t ON t.id = c.parent ) SELECT t.sequence, t.body FROM tree AS t LEFT JOIN comments ON t.parent = comments.id ORDER BY t.sequence; Point being: what one "naturally" has is a matter of perspective. Trees can always be flattened into tables that reference other tables (or even themselves).