We need to stabilize the database scheme as soon as possible. The most annoying requirement for this purpose is #2 (A list of files by copyright holder should be obtainable), but this is too important to be dropped in my opinion.
Options:
- Add an fileprops entry for every revision and join fp_rev_id against page_latest. Requires an index on page_latest, but no schema changes to revision
- Add a boolean fp_latest to fileprops and add rev_fileprops_id to revision, which is a reference to fp_id. fp_latest needs to be included in all indices. If fp_id is taken equal to the revision that changed the fileprops entry, there is no need for an extra index to revision.
- Add a boolean fp_latest to fileprops and add an fileprops entry for every revision. This requires no schema change to existing tables. fp_latest needs to be included in all indices.
- Add img_fileprops_id to image, which is a reference to fp_id, which is the revision id of the revision that changed the fileprops entry. img_fileprops_id needs to be indexed.
I need a bit of thinking before I state my preference, but those 4 are the options available that I see.