How can I improve this database design?

Question

I'm trying to design a data model for my current project, and I'm having trouble. The product owner wants to have Users, Searches, Documents, and Tags. Here's what seems to be well known thus far:

• Users have Searches - (these are not database searches, they are full-text searches that return a bunch of Documents)
• Searches have Documents <- However this need not be modeled. Search results will change over time.
• Users have Documents - any document that they have seen before from any search
• Tags are text strings
• User will be able to mark a Document with any number of Tags, but other Users will not be able to see their Tags

There are two issues I'm trying to manage here:

1. Whenever a User makes a Search, I need to mark the returned Documents differently according to whether or not they've been seen before. How? Is it reasonable to just collect the Documents from the Search (which will be returned 10 at a time) and then issue another SQL query for all Documents belonging to the User which have one of the doc_ids from the Search? Seems clumbsy.
2. Since a User can give a Document several Tags, and since other Users can't see them, how do I represent this in a database? How do I query it reasonably? I'm thinking that the Documents that belong to a User are represented by a join table - UserDocuments. And then several Tags belongTo UserDocuments. But then how do I present the Tags in-line with the Search results? It seems to me that this involves me issuing a Search, finding which Documents I have seen before, and then finding which of those have Tags and then hitting the database again for each of these Documents and gathering their Tags. This seems extreme, no?

One thing that's possibly confounding my understanding here is that I'm thinking through the Ruby on Rails ActiveRecord ORM. I will eventually have to implement whatever solution I find within Rails.

Answer 1

One possible design:

   User               
  1|  \1   
   |   \     
   \   N\      
    \   Search                    Document
     \      \1                    1/    /1
      \      \                    /    /
       \     N\                  /M   /
        \   Search-Returns-Document  /     
         \                          /
          \                        / 
           \N                    M/
            User--Views--Document           Tag
                   1\                      1/
                     \                     /
                      \N                  /M  
                   User-Marks-Document-With-Tag

Answer 2

Hopefully this will help:

1. Have a Documents_Viewed table that contains the user_id, document_id, etc.. then when retrieving the search result join the viewed table on user and document which will let you know if they have read it.

2. I would have another table Document_Tags that contain user_id, document_id, tag since you mentioned tags are user dependant

And example theoretical query would be:

SELECT * FROM Docuemnts d
         LEFT JOIN Document_Viewed dv ON (dv.document_id = d.id AND dv.user_id= ?)
         LEFT JOIN Document_Tags dt   ON (dv.document_id = d.id AND dv.user_id= ?)
         WHERE (search_criteria)

Not sure if that helps you at all