Professional Web Applications Themes

One to many relationship question - MySQL

What is the best way to have a one to many relationship without addition additional fields? This is what I need: I am representing school course sections. Each section can occur on one or more days, can occur for more than one term and have more than one teacher. At the moment there is a fixed number of schedulable days and terms so I could add extra fields to represent those but the number of teachers is variable. When I was given requirments they stated that a course could have between 1 and at the moment 8 teachers and I ...

  1. #1

    Default One to many relationship question

    What is the best way to have a one to many relationship without
    addition additional fields?

    This is what I need:

    I am representing school course sections. Each section can occur on
    one or more days, can occur for more than one term and have more than
    one teacher. At the moment there is a fixed number of schedulable days
    and terms so I could add extra fields to represent those but the
    number of teachers is variable. When I was given requirments they
    stated that a course could have between 1 and at the moment 8 teachers
    and I would like to avoid adding useless fields. I was originannly
    thinking of having one term, one teachers and one daysof week field.
    With in each field I was going to represent the "many" by using a
    colon as a separator (teacher1eid: teacher2eid...). However the more I
    thought I am not sure if there will be a consistent easy way to
    search/sort by day of week, term or teacher. If anyone could offer
    another way to design the table(s) or a link to the solution of a
    similar problem I would really appreciate it. Or if you think this
    approach is ok please help me figure out the best way to do searches
    and sorts. If it makes a difference the storage engine is innodb (I
    needed referential integrity between the course sections and the
    actual course).

    Thanks for taking the time.

    Mister Guest

  2. #2

    Default Re: One to many relationship question

    == Quote from Mister Joe (com)'s article 

    there is a good package called centre that runs on postgresql; however, you can
    use its schema to see how they are doing this.

    here's the links: http://www.miller-group.net

    download the package, unzip it and then look at the sql creation script.
    --
    POST BY: lark with PHP News Reader
    lark Guest

  3. #3

    Default Re: One to many relationship question

    On 11 Jul, 12:57, Mister Joe <com> wrote: 

    What is wrong with the standard normalised table approach for 1 to
    many using foreign keys?

    Captain Guest

  4. #4

    Default Re: One to many relationship question

    On Jul 11, 8:29 am, Captain Paralytic <com> wrote: 



    >
    > What is wrong with the standard normalised table approach for 1 to
    > many using foreign keys?[/ref]

    Thanks guys I think I have a better idea of how I will approach the
    problem.


    Mister Guest

  5. #5

    Default Re: One to many relationship question

    Mister Joe <com> wrote in news:1184155071.217324.51480
    22g2000hsm.googlegroups.com:
     

    I see you've decided on an approach already (in a later post), but yes, a
    good rule of thumb is that any time you want to store multiple values in a
    single column with a separator of some sort, it usually means it's time for
    an additional one-to-many table.
    Good Guest

Similar Threads

  1. many to many to many to many relationship
    By Alan in forum FileMaker
    Replies: 4
    Last Post: February 28th, 08:32 PM
  2. Replies: 4
    Last Post: November 20th, 08:48 AM
  3. Replies: 2
    Last Post: November 19th, 02:53 AM

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