[RESOLVED] School Database structure needs fixing

[Nitesh]

Hi Guys,

I inherited a VB6 app that used a MySQL 5 database. It was used mainly for student profiling. So there is a users table (UserID, UserName, Password) etc.

There is a student table (StudentID, Firstname, Lastname, DateOfBirth) etc.

There is a Teachers table (TeacherID, Firstname, Lastname, Tel, Cell, UserID) etc.

Now whoever uses the system has to be added to the users table. It used to be just Teachers so I have a fk in the Teachers table(UserID) which links to the Users table UserID column.

Now it has become a requirement that students also have to login and use certain parts of the system.

I'm creating a website where I've implemented a custom membership provider. So it takes care of adding/editing users infomation. Adding them to Roles (Teacher, Student, Administrator).

Now this website is intended to be sort of a social networking one. So i've added a profile table with fields like FirstName, Lastname, DateofBirth, Avatar, UserID(fk). Still to add more fields but I wanted to keep profile info seperate in a single table rather than looking for the firstname, lastname in the students or teacher table based on the roles.

The problem I have now. The students table doesn't have a UserID foreign key but I have an email address field. I will be creating record manually for each student in the users table. Is it a good idea to link these tables based on the email address to username? How should I change my structure if I have to?

[jmcilhinney]

For consistency, you should probably either add a UserID column to the Student table or else take the UserID column out of the Teacher table and add two join tables: TeacherUser and StudentUser. I think it's obvious what columns those tables would contain.

[Nitesh]

thanks JM. Will go with the first option