Results 1 to 4 of 4
  1. #1
    Join Date
    May 2012
    Posts
    2

    Unanswered: New database design for my new project

    Hi,

    Please can any one review my design before going for coding.

    ----Requirement for DB------

    1.student and class relation : single student can enroll in a single class at a time, a class can have many students
    2.class and exam relation : single class can have many exams(examid is formed based on the day of the exam) in a year, the same examid(nothing but a

    date) that means same examid can be for more than one class.
    3.The exam can have many subjects, and a subject can have many exams(i.e examid's).
    4.Every student can have a marks in given subject, in given exam,in given class.

    5.student and parent relation : single student can have many parents and single parent can have many students
    6.subject and teacher relation: single teacher can teach many subjects and single subject can be tought by many teachers.

    -----sample ERD-----------

    1.create a student(studentID,classID,sname...etc) table with studentID as Pkey,classID as Fkey
    create a class(classID,...etc) table with classID as Pkey

    2,3.create a class(classID,...etc) table with classID as Pkey
    create a exam(examID,...etc) table with examID as Pkey
    create a subject(subjectID,...etc) table with subjectID as Pkey
    create a class_exam_subject(class_exam_subjectID,classID,
    examID,subjectID) table with class_exam_subjectID as pkey,classID,examID,subjectID as Fkey

    4.create a marks(studentID,class_exam_subjectID,marks...etc) table with studentID as Pkey

    5.create a parent(parentID,pname.......etc) table with parentID as Pkey
    create a student_parent(parentID,studentID) table with studentID,parentID as Fkeys

    6.create a teacher(teacherID,...etc) table with teacherID as Pkey
    create a class_subject_teacher(class_subject_teacherID,clas sID,
    subjectID,teacherID) table with class_subject_teacherID as Pkey,classID,subjectID,teacherID as Fkey

    i appreciate your help and time

    Thanks,
    Sekar

  2. #2
    Join Date
    Apr 2002
    Location
    Toronto, Canada
    Posts
    20,002
    Quote Originally Posted by drsr.111 View Post
    Please can any one review my design
    sure

    where is it?
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL

  3. #3
    Join Date
    May 2012
    Posts
    2
    Hi r937,

    Thanks for your quick replay, I've coded the tables yet, but i've given the requirements and pseudo code table creation above, I just want to be sure whether the no. of tables and the relations shown above are accurate or whether i need to do any modifications in relations.

  4. #4
    Join Date
    Apr 2002
    Location
    Toronto, Canada
    Posts
    20,002
    if what you're saying is that you've designed all the tables and those are the designs in post #1, i think you have more work to do

    you can't just create entities called "xxxx" and give them primary keys called "xxxx_id" all over da place

    in the early stages data modelling, nothing should have an "id" surrogate column, despite the fact that you might want to create lots of these in physical design

    you need to focus on the natural keys that entities have
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL

Posting Permissions

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