Professional Web Applications Themes

Proper way to design this problem - MySQL

This seems like a very basic problem but I've never really dealt with databases so here we go. The database has students and doents. A doent can only be created by one student but it can be viewed by many. Whose attribute is this? Does a doent have student IDs that identify people allowed to view it or does a student have doent IDs that they are allowed to view. Also how do I store multiple foreign keys? Would a set work? I'm sure there is something I'm not thinking of but as I said I really don't know much ...

  1. #1

    Default Proper way to design this problem

    This seems like a very basic problem but I've never really dealt with
    databases so here we go.

    The database has students and doents. A doent can only be created
    by one student but it can be viewed by many. Whose attribute is this?
    Does a doent have student IDs that identify people allowed to view
    it or does a student have doent IDs that they are allowed to view.
    Also how do I store multiple foreign keys? Would a set work? I'm sure
    there is something I'm not thinking of but as I said I really don't
    know much about database design. Any help would be greatly appreciated.

    Thanks,
    Ryan Kaskel

    Ryan Guest

  2. #2

    Default Re: Proper way to design this problem

    com says... 

    Each doent has only one authoring student, but students can author many
    doents, so this is a one-to-many relationship with the student as a
    foreign key field in the doents table.

    Each doent can be viewed by many students and each student can view
    many doents, so this is a many-to-many relationship and you should use
    an intersect table.

    How I'd do it (others may slightly vary in design method):

    Table: Students
    Column: StudentID - primary key
    Other columns: name, etc.

    Table: Doents
    Column: DoentID - primary key
    Column: AuthorID - foreign key references StudentID
    Other columns: title, etc.

    Table: ViewPermissions
    Column: StudentID - foreign key references StudentID
    Column: DoentID - foreign key references DoentID
    Multi-column primary key - combination of both StudentID and DoentID

    Geoff M
    Geoff Guest

  3. #3

    Default Re: Proper way to design this problem


    Geoff Muldoon wrote: 
    >
    > Each doent has only one authoring student, but students can author many
    > doents, so this is a one-to-many relationship with the student as a
    > foreign key field in the doents table.
    >
    > Each doent can be viewed by many students and each student can view
    > many doents, so this is a many-to-many relationship and you should use
    > an intersect table.
    >
    > How I'd do it (others may slightly vary in design method):
    >
    > Table: Students
    > Column: StudentID - primary key
    > Other columns: name, etc.
    >
    > Table: Doents
    > Column: DoentID - primary key
    > Column: AuthorID - foreign key references StudentID
    > Other columns: title, etc.
    >
    > Table: ViewPermissions
    > Column: StudentID - foreign key references StudentID
    > Column: DoentID - foreign key references DoentID
    > Multi-column primary key - combination of both StudentID and DoentID
    >
    > Geoff M[/ref]

    Thank you...I see that an intersect table is what I'm looking for.

    Ryan Guest

  4. #4

    Default Re: Proper way to design this problem

    >This seems like a very basic problem but I've never really dealt with 

    Which attribute?
    doent_creator is an attribute of doent (a doent has only
    one creator in your system. I'm not sure that's real-world realistic
    given the possibility of collaboration and co-authorship, but I'll
    accept it.). It references the student id in the students table.
    This is a standard way of handling a many (doents)-to-one (student)
    relationship.

    doent_accessor is a many-to-many relationship. Normally it would
    be represented in another table, say, doent_permissions, containing
    doent_id and student_id, and this combined field is the primary
    key. Presumably, no student may have multiple permissions records
    to access the SAME doent. If there are different types of
    permissions (read online, print copy, etc.) they either go in the
    same record or there's a permission type that is part of the primary
    key. doent_permissions might have ONLY those two fields, or it
    might have more info (say, the serial number of the physical doent
    this student currently has checked out, set to null when he returns
    it).

    Doent_permissions

    Doent_id Student_id
    4 (Joy of Sax) 7 (Joe)
    5 (An Inconvenient Vote) 7 (Joe)
    12 (King Lear) 7 (Joe)
    12 (King Lear) 8 (Betty)
    37 (Betty's Diary) 8 (Betty)

     

    I say neither. A doent_permission has a student and a doent,
    and that student is allowed to read that doent. This is the
    standard way of dealing with a many-to-many relationship.
     

    I'm not sure what you are referring to here, but I think the answer
    is: don't even try. Chances are if you're trying to cram multiple
    ID numbers into a set, the number of students or doents allowed
    is so small you wouldn't need a database, you could just memorize
    it, or at best it's a toy application.
     

    Gordon Guest

Similar Threads

  1. db query/design problem
    By kabbi~thkek in forum Coldfusion Database Access
    Replies: 0
    Last Post: September 30th, 11:50 AM
  2. Design problem
    By Inder in forum PHP Development
    Replies: 4
    Last Post: August 17th, 06:24 AM
  3. Design view problem
    By Mango in forum ASP.NET Building Controls
    Replies: 1
    Last Post: March 11th, 09:11 AM
  4. Design Problem!!
    By Jay in forum ASP.NET Web Services
    Replies: 3
    Last Post: May 27th, 07:01 AM
  5. Replies: 8
    Last Post: July 5th, 08:42 PM

Bookmarks

Posting Permissions

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

1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 31 32 33 34 35 36 37 38 39 40 41 42 43 44 45 46 47 48 49 50 51 52 53 54 55 56 57 58 59 60 61 62 63 64 65 66 67 68 69 70 71 72 73 74 75 76 77 78 79 80 81 82 83 84 85 86 87 88 89 90 91 92 93 94 95 96 97 98 99 100 101 102 103 104 105 106 107 108 109 110 111 112 113 114 115 116 117 118 119 120 121 122 123 124 125 126 127 128 129 130 131 132 133 134 135 136 137 138 139