4 ms·
Relations are cool, but SQL DBs either prohibit or make it hard to present relations inside relations, which is one of the most common ways of structuring data
by LudwigNagasena 2y ago
Relations are cool, but SQL DBs either prohibit or make it hard to present relations inside relations, which is one of the most common ways of structuring data in everyday programming life. You can see people suggesting writing SQL functions that convert rows to json or using ORM simply to query a one-to-many relationship, that's crazy: https://stackoverflow.com/questions/54601529/efficiently-mapping-one-to-many-many-to-many-database-to-struct-in-golang https://stackoverflow.com/questions/54601529/efficiently-map...
- bawolff 2y agoAny tool can be used incorrectly... Im not sure what relations in relations mean. Do you just mean M:N?
- LudwigNagasena 2y agoI 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 agoNo. 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 agoStoring 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 agoMake 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 agoIf SQL were flexible, I wouldn’t need to create a whole view for a single query.
- liontwist 2y agoYou’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 agoPRAGMA 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).