3 ms·
In some cases they make sense, tags being a good example. But the moment you find yourself writing extra code to do multiple updates in order to keep things in
by petepete 8y ago
In some cases they make sense, tags being a good example. But the moment you find yourself writing extra code to do multiple updates in order to keep things in sync, that's a sign a normalised option might be a better approach.
- blattimwind 8y agoI don't feel like tags are a particularly good example. For example, how do you efficiently get a list of all tags and how many X there are per tag? Easy for a relation (SELECT tags.name, COUNT(posts) FROM tags NATURAL JOIN posts GROUP BY tags.name), not so easy for an array (SELECT x.tag, COUNT(posts) FROM (SELECT DISTINCT tag FROM unnest(posts.tags)) AS x JOIN posts ON posts.tags @> [x.tag] GROUP BY x.tag).
- Inviz 8y agoselect region, count(*) from projects, unnest(region_ids) as region GROUP BY 1;
- blattimwind 8y agoThe issue (iirc) with that sort of query is that it's essentially a join, so you'll get count(projects)*sum(length(regions)) intermediate rows that need to be grouped back together.