Hot answers tagged schema
I talked to someone who helped me find the answer. The correct syntax is, for anyone in future reference. The documentation mentions it, although it can be easy to miss. START TRANSACTION; DROP SCHEMA IF EXISTS replays CASCADE; CREATE SCHEMA replays; CREATE ROLE admins WITH PASSWORD 'changeme'; GRANT SELECT ON ALL TABLES IN SCHEMA replays TO PUBLIC; ...
Approach 1 is the cleanest with no redundancy. However I can understand your worries about scanning through a huge table just to count a post votes. So approach 2 which features some "caching" seams fair. The downside is that you add some redundancy but this is not even the main issue. You also lose consistency. It's possible to have a post with 500 votes ...
How large do you expect the data to grow? No harm in keeping older data in the same table. You could plan to use partitioning and implement date range partitions (per year). Using WHERE statements is completely natural in SQL so don't consider this as "messy" at all.
Only top voted, non community-wiki answers of a minimum length are eligible