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 .
lojaynAsked:
Who is Participating?

[Product update] Infrastructure Analysis Tool is now available with Business Accounts.Learn More

x
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

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
Big Business Goals? Which KPIs Will Help You

The most successful MSPs rely on metrics – known as key performance indicators (KPIs) – for making informed decisions that help their businesses thrive, rather than just survive. This eBook provides an overview of the most important KPIs used by top MSPs.

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

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
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
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Databases

From novice to tech pro — start learning today.