I'm designing a PostgreSQL database that will contain information about photos uploaded by my users. All users will have at least one main photo and optionally one or more public and/or private photos. My first schema is as follows:
user ---- id (PK) photo ----- id (PK) user_id (FK to user) photo_id (unique identifier such as "aK1q9") type ("main" or "other") access ("public" or "private")
The query that will get run the most is:
SELECT p.photo_id FROM photo p INNER JOIN user u ON p.user_id = u.id WHERE p.type = 'main' AND u.id = (some user id);
The next most popular query will be:
SELECT p.photo_id FROM photo p INNER JOIN user u ON p.user_id = u.id WHERE p.type = 'other' AND p.access = 'public' AND u.id = (some user id);
The problem I forsee is that the Photo table will become extremely large over time as it will contain all public and private photos uploaded by all users. Since my most popular query will only be looking for the main photo IDs, would it make more sense to break my photo table up into three tables?
main_photo ---------- id (PK) user_id (FK to user but in a one-to-one relationship to user) photo_id other_public_photo ------------------ id (PK) user_id (FK to user) photo_id other_private_photo ------------------- id (PK) user_id (FK to user) photo_id
I would think this latter schema would be preferable because 1) each photo's type and access information is made explicit by where it's stored thus eliminating additional ANDs in my queries; and 2) my queries would run faster since they would be run against one of the three smaller tables rather than one huge table. Which is the optimal approach from a performance standpoint?