need database design help

slinfo

Hi,
I'm a newbie setting up a PHP-MySQL database and I'm just at the table creation stage. I'm worried because I have two tables that are showing the same info so I wonder if my database is truly normalized... Here are the tables I am worred about... so far:

-Instructor-
InstructorID
iFirstname
iLastname
iEmail

-Student-
StudentID
sFirstname
sLastname
sEmail

-Instructor_useraccount-
Instructor_useraccountID
InstructorID
iPassword
iPriviledge
iActivationDate
iExpiryDate
iLastaccess

-Student_useraccount-
Student_useraccountID
studentID
sPassword
sPriviledge
sActivationDate
sExpiryDate
sLastaccess

I'm wondering if I can use one "USERAccount table" and add a foreign key of USERID to both the Student table and the Instruction rather than the way I've done it.

I was also thinking I could add all the user_account info to each of the Instructor and Student tables.

What are the advantages/disadvantages to the various approaches?

I hope help is on the way...I've so much further to go!
thnx,
Sue

bubblenut

Can each user/instructor have more than one account?
Is there any difference in the data which needs to be stored on users and instructors?

Prehapse think in terms of Object Oreintation. Both student and instructor extend user and add their own terms to the base, see?

slinfo

Instructor and student will each have only one account. The instructor accounts will be created by an 'administrator' while the student accounts will be created by the instructor.

At one part in the site, the students go in and sign up for a chat time - for this they don't need a password but we need a class list of students (which is previously inputted by the instructor).

For another part of the site, the students will have access to assignments they have sent in and for this they must 'activate' their accounts by identifying a password.

So... from your response, I'm thinking I should add the following items to each of the Student and Instructor tables:
Password
Priviledge
ActivationDate
ExpiryDate
Lastaccess

Am I understanding things correctly?

Sue

bubblenut

Well, what I'm thinking is do you really need seperate Student and Instructor tables if they both hold the same data?
Why not have one table, Users.

Users
user_id 
UserType - this would be an enum('student', 'instructor');
FirstName
LastName
Email
Password
Priviledge
ActivationDate
ExpiryDate
Lastaccess

If it turns out you need to store specific data for instructors but not for students then create an Instructor table like so.

Instructor
unique_id 
user_id 
extrafield1

Does this make sense?

Roger_Ramjet

Point 1. no need for the seperate tables just to hold peoples' names. If they were going to have multiple accounts then yes, but since they are not then get rid of the excess tables.

It is a matter of choice whether you use 1 table for all or seperate tables for instructors and students at this time. The decision depends upon the uses of the data. I will also predict that as time goes on you will add more fields, and these will be application-specific. Instructors will probably have attributes that students do not, and vice-versa, so seperate tables will end up being the correct normalisation.

slinfo

Thank you both for your help thus far....

If I go with the one table - "Users" and a 'usertype' field to separate the instructors from students, will I end up with problems further down the road when it comes to link students to course sessions and students to chat times... Originally, I was going to have the instructor indentify the Course and Section for which he/she would like to insert a class list (this is how the Student table was going to be populated.)

Here are the tables I believe that would be affected because I will no longer have studentID (but perhaps that won't matter, eh?).

-Enrollment-
EnrollmentID
studentID
offeredsectionID

-Offered_Section-
Offered_sectionID
CourseID
TermID
InstructorID
sectionnumber
year

-Selected_chatID-
Selected_chatID
Chat_timesID
StudentID

At first I am only organizing chat sessions with this database where the students sign up for one of three chat times identified by the instructor but eventually I want to use the same database to accept and update an assignment drop box.

Your time is appreciated!
Sue

bubblenut

I'm not sure if this will help at all but what the hell. I work for an online retailer and we have a products table. We have lots of different types of products with many different sets of details but one, central products table which we can work from. Lingerie has a very different set of attributes to CDs but they still have some common ones and they are heavily related (in the fact that they are both products) so it makes sense to have them in the same generic table (although their attribute sets are extended in other tables). The fact that they're used on completely different sites and in completely different ways is irrelevant because the sections which are concerned with CDs just ignore the Lingerie products.

Your two different types certainly have common attributes but what you have to decide (and no one else realy can) is whether or not they are actually related and if it makes sense to have them in the same table. As Rojer Ramjet says, it's really a matter of choice.

One thing I would ask is, are students and instructors going to need to use the same parts of the site at all? If so it would be easier to have them both in the same table. Consider the case where both students and instructors can use the chat. If they're in seperate tables you're going to need quite a bit (or more complex) code to handle anything relating to the users because you'll need to support access on two different tables.

If this doesn't make sense (which I'm sure it won't) then let me know and I'll have another crack at it.

Cheers
Rob