Marking 'read/undread'

[Stooney]

I'm working on a website where users submit files to an admin.  Undownloaded files need to be highlighted, and downloaded files needs to be unhighlighted.  What is the best database structure for this?

 

As files are uploaded, they are added to a table known as 'undownloaded_files'.  As files are downloaded, they are removed from this table.  This should keep the table fairly small, as the files are downloaded within a few days of being uploaded.

 

As files are downloaded, they are added to a table known as 'downloaded_files'.  As they are downloaded, I add the file ID here.  This could get to be a really big table over time.

 

I can't just added a has_been_downloaded option to the files table because the highlighting needs to be account specific (multiple admins, often working with the same files).

 

So is there another way I haven't thought of?  Or are one of these two options the way to go?

[Psycho]

What you state does not make sense. You state that the highlighting needs to be account specific (but, you only have one record for the file). Does this mean that if file "foo.bar" has been downloaded by user A that the file will show as non-highlighted for him, but would be shown as highlighted for user B? Or is it that there are multiple users in an account?  Or do you just determine if a file should be highlighted based upon if it has ever been downloaded?

 

In any case, you should not have separate tables for the files. Just one table for the files is needed. I will explain three different scenarios and how you should handle.

 

1. If the highlight is determined based upon if the file has ever been downloaded by anyone

Simply create a column in the files table to indicate if the file has been downloaded

 

2. If the highlight is based upon specific users and if each has ever downloaded the file

Create another table with fileds for userID and fileID. you will then be able to determine whether to show a file as highlighted by joining the two tables

 

3. If the highlight is based upon whether any user from an account (where multiple users belong to an account)

Use the same logic as #2. Create another table with columns for accountID and fileID. Then do an appropriate join based upon the user viewing the page to determine whetehr to highlight the file or not.