Results 1 to 4 of 4
  1. #1
    Join Date
    Mar 2003
    Posts
    130

    Question Unanswered: Schema Design Question

    Let me explain what I'm trying to do and see if I can get any suggestions. I have some tables in a database to track "Required Reading" so that users who login must read documents by a certain date.

    My first idea was to have a table called Reading that would store each UserID and every DocID from the Documents table. Then when the User logged in I would do a SELECT * FROM Reading WHERE UserID = 'User1' to filter down to all of the documents for the current user. So if I have 10 users and 10 documents then I would have 100 records in the Reading table.

    The only problem with that is that every user logged in would have the Reading table open at the same time but no two users should ever be editing the same record. The user would have an exclusive recordset based on his UserID. I just don't know if this would cause a conflict.

    Documents
    -----------------
    DocID nvarchar(16) (PrimaryKey)
    Revision nvarchar(8)
    RevisionDate smalldatetime
    DueDate smalldatetime

    Reading
    ----------------
    UserID nvarchar(16) (PrimaryKey)
    DocID nvarchar(16) (PrimaryKey)
    Read bit
    ReadDate smalldatetime

    The second idea is to have the same Documents table but when a user logs in, create them a unique table. So if User1 logs in I would check to see if a User1 table exists and if not create it. The same for User2 etc.

    User1
    ------------------
    DocID nvarchar(16) (PrimaryKey)
    Read bit
    ReadDate smalldatetime

    User2
    ------------------
    DocID nvarchar(16) (PrimaryKey)
    Read bit
    ReadDate smalldatetime


    Which would be a better schema design?

    What would be the best way to index the tables?

  2. #2
    Join Date
    Mar 2004
    Location
    L.A
    Posts
    19
    If I understood you correctly table Ready should not necessarily have 100 records (10 UserID * 10 DocID). Instead that table should have as many records as necessary to have a record for each pair User/Document.

    In other words, if User1 must read Doc1 and Doc2 and User2 must read Doc1 and Doc3 then table reading would have:

    UserID DocID
    -------- -------
    User1 Doc1
    User1 Doc2
    User2 Doc1
    User2 Doc3

    and in this case there won't be any conflict since each user sill have his own set of records (User/Doc) representing his must-read list.

  3. #3
    Join Date
    Mar 2003
    Posts
    130
    Each user is required to read every Document so the number of records in Reading would be (number of users) * (number of documents)

    UserID DocID
    -----------------
    User1 Doc1
    User1 Doc2
    User1 Doc3
    User2 Doc1
    User2 Doc2
    User2 Doc3

    etc.

    So as each new user logs in I have to populate the Reading table with a UserID and DocID for every Document.

    But I just wanted to make sure there wouldn't be any problems using the same table for all Users since they each would have a unique recordset.

  4. #4
    Join Date
    Mar 2004
    Location
    L.A
    Posts
    19
    Since each user has a unique recordset defined by (UserID) in the Reading table, there shouldn't be any problem reteiving/writing data.

    The database engine should be able to handle multiple calls from different users simultanously.

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •