I have an application that need to find out the n most recent modified files for a given user id. I started out few tables but still couldn't get what i want, I hope someone get point to some right direction...
See my tables below. #1 won't work, because file_id's timeuuid contains creation time, not the modification time. #2 won't work, because i can't order by a non primary key column(modified_date) #3,#4 although i can now get a time series of modification time of each file belongs to a user, my return list may still not accurate because a single directory could have lot of modification changes. I basically end up pulling out series of modification timestamp for the same directory. Any suggestion? Thanks #1 CREATE TABLE user_file ( user_id uuid, file_id timeuuid, PRIMARY KEY(user_id, file_id) ); #2 CREATE TABLE user_file ( user_id uuid, file_id timeuuid, modified_date timestamp, PRIMARY KEY(user_id, file_id) ); #3 CREATE TABLE user_file ( user_id uuid, file_id timeuuid, modified_date timestamp, PRIMARY KEY(user_id, file_id, modified_date) ); #4 CREATE TABLE user_file ( user_id uuid, modified_date timestamp, file_id timeuuid, PRIMARY KEY(user_id, modified_date, file_id) );
