Results 1 to 4 of 4
  1. #1
    Join Date
    May 2003
    Posts
    34

    Unanswered: interview question on db design

    Hi All

    a newbie question on db design

    the question is how many tables will be required in a 1:m (1 to many relationship).
    eg

    student(1) : courses(m)

    1 student can enroll for more than 1 course.

    in that scenario after normalisation how many tables would be there.

    how to avoid a scenario if 1 student takes 5 courses then there would be 5 entries in the student table

    Rgrds
    Sushant

  2. #2
    Join Date
    Sep 2002
    Location
    UK
    Posts
    5,171
    Provided Answers: 1

    Re: interview question on db design

    Originally posted by zulu99
    Hi All

    a newbie question on db design

    the question is how many tables will be required in a 1:m (1 to many relationship).
    eg

    student(1) : courses(m)

    1 student can enroll for more than 1 course.

    in that scenario after normalisation how many tables would be there.

    how to avoid a scenario if 1 student takes 5 courses then there would be 5 entries in the student table

    Rgrds
    Sushant
    This is not a 1:m relationship, it is a m:m, since (presumably) a course can have more than 1 student.

    So the answer is 3 tables:

    Student
    Course
    Student_Course

    Student_Course is an "intersection" table that lists all the student/course linkages. It has a FK to Student and a FK to Course.

    A true 1:m relationship requires only 2 tables, with the table at the "many" end having a FK to the other table.

  3. #3
    Join Date
    May 2003
    Posts
    34
    Hi

    Thanx Andrew for your prompt reply. It was very helpful.
    If possible could you ellaborate a bit on the example(i.e the columns they will have)

    say if 1 student opts for 5 courses . How will the data be reflected in the tables. will i have to pass 5 entries in the student table . I just wnat to know how exactly the student - courses mapping will happen

    say student table - sno,sname----
    courses table - cid, cname---
    stud_crs - ?????


    Regrds
    Sushant

  4. #4
    Join Date
    Sep 2002
    Location
    UK
    Posts
    5,171
    Provided Answers: 1
    Originally posted by zulu99
    Hi

    Thanx Andrew for your prompt reply. It was very helpful.
    If possible could you ellaborate a bit on the example(i.e the columns they will have)

    say if 1 student opts for 5 courses . How will the data be reflected in the tables. will i have to pass 5 entries in the student table . I just wnat to know how exactly the student - courses mapping will happen

    say student table - sno,sname----
    courses table - cid, cname---
    stud_crs - ?????


    Regrds
    Sushant
    Using your column names:

    stud_crs - sno, cid

    So a student on 5 courses will have 1 student record and 5 stud_crs records.

Posting Permissions

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