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-map...
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.
> 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).
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
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.
