Results 1 to 2 of 2
  1. #1
    Join Date
    Oct 2004
    Posts
    1

    Unanswered: newbie - database design

    I'm new to database design. Hope someone may able to help me.
    I'm try to design a database where students can take class, and teachers teach classes. The tables are: users, enroll ( to connect users table and class table), class, course, and roles (to determine the privilege of the user whether is a student or teacher).

    Because of the security reason, I would like to seperate the Roles from within the Users table, but then I run into trouble when designing the "roles" table.

    Users table: User_ID (PK), User_Name, etc...
    Roles table: User_ID (PK), Roles_Teacher, Role_Student

    1st question: How can I link these two tables together?
    2nd question: Is it better to seperate students into Students table and teachers into Teachers table, or just put all of them into Users table to simplify the design?

    Thank you very much for any help,

  2. #2
    Join Date
    Apr 2002
    Location
    Toronto, Canada
    Posts
    20,002
    this is homework, right?

    tip: create some sample rows for each and every table, using values other than 1,2,3, e.g number your students 13,14,15, your teachers 56,57,58, your classes 101,102,102, your courses 330,331,332, and so on

    you should be able to see how the tables are related by looking at the sample rows, where the primary and foreign key values should actually link the sample rows in a consistent manner

    when you get to the roles table, you will have a much better feel for what columns it requires
    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
  •