• Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 570
  • Last Modified:

database structure

I am developing an application that let adviser to assign advising times and let the student to  schedule to meet the advisor at the assigned times.
I have 5 tables advisor, student, advisor_schedule, student_queue and advisor_office_hr.
I need to create the log in page .
 username:
password:
role: student/ advisor
would you advise me how to structure the data to create the log in page do I need another table for role, how to let the application decide whether the advisor or the students is logging in .
0
lojayn
Asked:
lojayn
  • 6
  • 6
  • 3
  • +1
3 Solutions
 
Jakub BaumanCommented:
If structures of tables advisor and student are similar it will be better to use one table "users" instead. That will prevent problem with user names uniqueness.  You can mark the user's role using a field "role".
0
 
lojaynAuthor Commented:
no, tables student and advisor are not similar.
0
 
Jakub BaumanCommented:
You may add option buttons (radio buttons) to your login form. The application will decide who is logging in upon selected option.

You can also use separate queries to check in which table the user exist.
Anyway you do not need to have a table for role.
0
Improve Your Query Performance Tuning

In this FREE six-day email course, you'll learn from Janis Griffin, Database Performance Evangelist. She'll teach 12 steps that you can use to optimize your queries as much as possible and see measurable results in your work. Get started today!

 
arnoldCommented:
You would extract data from users/advisors to create a login table that will have username/password and will refer to the appropriate table I.e. have a column referencing the advisor table's I'd and similarly a second column referencing the user table.
Presumably a user who needs an advisor in one subject might be an advisor in another?
0
 
lojaynAuthor Commented:
what would be the referencing  columns. Are they the student_id and advisor_id?
the student is assigned one advisor for the whole semster.
0
 
arnoldCommented:
Not sure what you are asking.
0
 
lojaynAuthor Commented:
IN YOUR POST YOU SAID ' have a column referencing the advisor table's I'd and similarly a second column referencing the user table' . WHAT WOULD THE COLUMNS BE?
0
 
arnoldCommented:
You pick the name one will reference the advisorid if this is a user that is an advisor while the other will be null or empty. If this is a login for a user, the reverse will be the case

Alternatively you can have login accounts and then have two separate relationship tables advisor_logins
Advisorid, loginid
User_logins
Userid,loginid

The only issue is see with this a deletion of the relationship will leave you with orphan records in the login table.
0
 
lojaynAuthor Commented:
would you check the attached file,
0
 
lojaynAuthor Commented:
would you check the attached file,
database-strucyure.docx
0
 
arnoldCommented:
Yes it can be this way. The login can only have the advisor_id or the user_id reference unless you have a person that appears in both users and advisers lists.

consolidating the personal information into a single table, might be better.
i.e. users includes all users and advisers (index on username to make sure there are no duplicates)

Then you would have a table that defines their rights/roles which is where they will be distinguished.

But since you have an existing setup that you need to add login, the single login table will do.

If you wish you can work on adjusting it in the future.
0
 
Tomas Helgi JohannssonCommented:
Hi!

I would create a user table (login table) like this
usertable (
username,
password,
roleid,
)
And create a view like this where you join

create login_v as
select username,password
from usertable u, advisortable a
where u.roleid = a.advisorid
union
select username,password
from usertable u, studenttable a
where u.roleid = a.studentid

and use that view when you query the db for username and passw.

Regards,
     Tomas Helgi
0
 
lojaynAuthor Commented:
how would I define the roleid column?
0
 
arnoldCommented:
It will be the advisorid or the userid depending on whose login it is, it would be a an integer not sure you can use foreign key constrain to two tables.

There is an example discussion

http://stackoverflow.com/questions/668921/foreign-key-refering-to-primary-keys-across-multiple-tables
Do you have an ERP tool?
Graphically setup
What database are you using?
0
 
Tomas Helgi JohannssonCommented:
Hi!

The roleid should be of the same datatype as advisorid and studentid. I suggest the datatype integer.
As with the username and passord it should be varchar(20) which should be sufficient.

Regards,
     Tomas Helgi
0
 
arnoldCommented:
The difficulty is avoiding orphan records. Not sure whether a two table contraint the other issue, what is to prevent the same number appearing in both advisorid and userid?  Based on the prior discussion, those two are separate tracks/lists.
0
 
Tomas Helgi JohannssonCommented:
The usertable with roleid as I mentioned earlier should have same numbers as in advisorid and studentid
otherwise you can't join the username with either a student or advisor.
To prevent that both student and advisor could have the same username you should use the view I suggested
and query it when a user creates his/her username.

If your app adds either to both advisor and usertable or student and usertable at the same time or
makes sure that an advisor or student has zero to one relationship to the usertable then you are fine.  

Regards,
    Tomas  Helgi
0
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

Join & Write a Comment

Featured Post

Improve Your Query Performance Tuning

In this FREE six-day email course, you'll learn from Janis Griffin, Database Performance Evangelist. She'll teach 12 steps that you can use to optimize your queries as much as possible and see measurable results in your work. Get started today!

  • 6
  • 6
  • 3
  • +1
Tackle projects and never again get stuck behind a technical roadblock.
Join Now