Database table relation

Questions about the MySQL, PostgreSQL, and most other databases, as well as using it with PHP can be asked here.

Moderator: General Moderators

Post Reply
aquilina
Forum Commoner
Posts: 30
Joined: Wed Sep 21, 2011 1:23 am

Database table relation

Post by aquilina »

I'm looking for someone who can look at my table that i sketch before i creating the database on phpmyadmin based on the case. I not really good in creating database that involve too much table since i going mixed up and confuse with foreign key.. i attached with the screenshot of my case. when i searching around this things that i going to build is nearly same with moodle its like e-learning...

From what i understand from the task, there are 3 types of users admin, lecturer and students. This 3 users have different kind of level access. For example admin are able to add new course, delete existing course, admin are able to assign which course are that lecturer belong to.. Mean 1 lecturer can have many courses in one time.. Admin also can send mass messages to all users(lecturers and students).

The 2nd user is lecturer, they able to upload/delete/edit the notes/assignment/announcements. Lecturer are able to view students who enroll in their subject.. Means students who enrolled that course can view the materials such as notes/assignment / announcements. Lecturer also can send and receive individual messages from enrolled students and also able to mass messages to all students who enrolled in the course that lecturer assigned. Lecturer able to create a mini forum for student who enrolled for discussion.

The 3rd user is student.. Before student are able to view the notes/assignment/announcement, they must enroll themself to the course. Student able to submit their assigment via link provided by lecturer. Student able to enroll and unenroll from the course. Student also able to send and receive messages to individual or mass messages. Student able to participate in forum that created by lecturer.

This what i understand briefly... but for me new to this database especially it involve alot of table.. it might be confused for me...
Screenshot_2.png
Screenshot_1.png

Code: Select all

    Users
    -user_id(pk)
    -user_name
    -user_pass
    -email
    -forum_notification

    courses
    -course_id(pk)
    -user_id(fk)
    -course_name
    -description

    Messages
    -msg_id(pk)
    -user_id(fk)
    -msg
    -msg_date

    Notes
    -note_id(pk)
    -user_id(fk)
    -course_id
    -note_name
    -note_date


    Assignments
    -assg_id(pk)
    -user_id(fk)
    -course_id(fk)
    -assg_name
    -assg_date


    announcements
    -ann_id(pk)
    -user_id(fk)
    -course_id(fk)
    -ann_name
    -ann_description
    -ann_date


    categories
    -category_id(pk)
    -category_title
    -category_description
    -last_post_date
    -last_user_posted

    topics
    -topic_id(pk)
    -category_id(fk)
    -topic_title
    -topic_creator
    -topic_last_user
    -topic_date
    -topic_replay_date
    -topic_views

    posts
    -post_id(pk)
    -category_id(fk)
    -topic_id(fk)
    -post_creator
    -post_content
    -post_date

User avatar
requinix
Spammer :|
Posts: 6617
Joined: Wed Oct 15, 2008 2:35 am
Location: WA, USA

Re: Database table relation

Post by requinix »

- The user_id in the courses table: I assume that's for the lecturer? It shouldn't be for a student...
- Need a field in the users table indicating what type of user they are (ie, admin, lecturer, or student).
- Need an enrollment table.

While you're in there, consider your naming scheme. Doing "table_" prefixes is alright but try to do it consistently. And don't restrict yourself to making your foreign key fields be named the same as the field being referenced; if courses.user_id is for the lecturer then it really should have a name like "lecturer_id" or something to indicate that it's not just any user.

Something to think about: a person being both a lecturer and a student. Not unheard of. I'm sure you don't have to account for something unexpected like that, but at least consider what it would mean for your schema.
aquilina
Forum Commoner
Posts: 30
Joined: Wed Sep 21, 2011 1:23 am

Re: Database table relation

Post by aquilina »

users table mean i should put an attribute such as user_level ? 0=admin, 1=lecturer and 2=student... so the enrollment table will be linked to student? course link to enrollment and enrollment link to student?.. should i get start only by login(as admin,lecturer and student) and then admin will assign a course for lecturer? and student will try to enroll any of course.. means i gonna start with 3 types of table users,enrollment and course?
User avatar
requinix
Spammer :|
Posts: 6617
Joined: Wed Oct 15, 2008 2:35 am
Location: WA, USA

Re: Database table relation

Post by requinix »

aquilina wrote:users table mean i should put an attribute such as user_level ? 0=admin, 1=lecturer and 2=student...
Sure. There are ENUM types that restrict the field to a very explicit set of values, so you could still use strings ("admin", "lecturer", "student"), or you can go with the simpler route of numbers.
If you do numbers then you should have another table that maps those numbers to useful values

Code: Select all

id | identifier |     name
---+------------+---------
1  |      admin |    Admin
2  |   lecturer | Lecturer
3  |    student |  Student
In this example, "identifier" is something you can use in code to refer to the access level (because having to look at numbers would suck) and the "name" is something you could actually display to someone.
aquilina wrote:so the enrollment table will be linked to student? course link to enrollment and enrollment link to student?..
Enrollment has foreign keys to both a student and a course. A pairing of the two. Course has just a key to the lecturer.
aquilina wrote:should i get start only by login(as admin,lecturer and student) and then admin will assign a course for lecturer? and student will try to enroll any of course.. means i gonna start with 3 types of table users,enrollment and course?
...Yes? Not sure what you're asking about.
Post Reply