Thread:Talk:Files and licenses concept/Database schema/reply (5)

A possible other option:

#5: Add rev_fileprops_id to mw_revision, which is a reference to fp_id. fp_id is equal to the revision that changed the fileprops entry, there is no need for an extra index to mw_revision.

To get a list of all files that are currently by a certain author or with a certain license we'd join page_latest with revision and file_props. The advantage is that we don't need an extra "fp_latest", downside is that such a join may not be very fast depending on where the indexes are (We need mw_page to get all files and the current revision id, then from revision the current fp_id).

I think in terms of effeciency #5 is probably not going to make it and thus a quick and fast fp_latest in mw_file_props would help. However let's not forget that there's no direct link from a set of file_props to a page. One would still need a join to page for the titles etc. So perhaps #5 isn't bad after all.