Solved

database structure

Posted on 2012-04-07
17
492 Views
Last Modified: 2012-04-10
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
Comment
Question by:lojayn
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 6
  • 6
  • 3
  • +1
17 Comments
 
LVL 1

Assisted Solution

by:Jakub Bauman
Jakub Bauman earned 50 total points
ID: 37820090
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
 

Author Comment

by:lojayn
ID: 37820101
no, tables student and advisor are not similar.
0
 
LVL 1

Expert Comment

by:Jakub Bauman
ID: 37820133
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
 [eBook] Windows Nano Server

Download this FREE eBook and learn all you need to get started with Windows Nano Server, including deployment options, remote management
and troubleshooting tips and tricks

 
LVL 79

Expert Comment

by:arnold
ID: 37820493
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
 

Author Comment

by:lojayn
ID: 37821756
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
 
LVL 79

Expert Comment

by:arnold
ID: 37821904
Not sure what you are asking.
0
 

Author Comment

by:lojayn
ID: 37821966
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
 
LVL 79

Assisted Solution

by:arnold
arnold earned 50 total points
ID: 37821973
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
 

Author Comment

by:lojayn
ID: 37822052
would you check the attached file,
0
 

Author Comment

by:lojayn
ID: 37822059
would you check the attached file,
database-strucyure.docx
0
 
LVL 79

Expert Comment

by:arnold
ID: 37822135
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
 
LVL 25

Accepted Solution

by:
Tomas Helgi Johannsson earned 50 total points
ID: 37822829
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
 

Author Comment

by:lojayn
ID: 37823233
how would I define the roleid column?
0
 
LVL 79

Expert Comment

by:arnold
ID: 37823275
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
 
LVL 25

Expert Comment

by:Tomas Helgi Johannsson
ID: 37823890
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
 
LVL 79

Expert Comment

by:arnold
ID: 37824319
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
 
LVL 25

Expert Comment

by:Tomas Helgi Johannsson
ID: 37824748
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

Featured Post

[Live Webinar] The Cloud Skills Gap

As Cloud technologies come of age, business leaders grapple with the impact it has on their team's skills and the gap associated with the use of a cloud platform.

Join experts from 451 Research and Concerto Cloud Services on July 27th where we will examine fact and fiction.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

When table data gets too large to manage or queries take too long to execute the solution is often to buy bigger hardware or assign more CPUs and memory resources to the machine to solve the problem. However, the best, cheapest and most effective so…
Lotus Notes has been used since a very long time as an e-mail client and is very popular because of it's unmatched security. In this article we are going to learn about  RRV Bucket corruption and understand various methods to Fix "RRV Bucket Corrupt…
Polish reports in Access so they look terrific. Take yourself to another level. Equations, Back Color, Alternate Back Color. Write easy VBA Code. Tighten space to use less pages. Launch report from a menu, considering criteria only when it is filled…
This is a high-level webinar that covers the history of enterprise open source database use. It addresses both the advantages companies see in using open source database technologies, as well as the fears and reservations they might have. In this…

635 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question