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