Solved

database structure

Posted on 2012-04-07
17
424 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
  • 6
  • 6
  • 3
  • +1
17 Comments
 
LVL 1

Assisted Solution

by:jakubbauman
jakubbauman 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:jakubbauman
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
 
LVL 76

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 76

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 76

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
Better Security Awareness With Threat Intelligence

See how one of the leading financial services organizations uses Recorded Future as part of a holistic threat intelligence program to promote security awareness and proactively and efficiently identify threats.

 

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 76

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 24

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 76

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 24

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 76

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 24

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

Why You Should Analyze Threat Actor TTPs

After years of analyzing threat actor behavior, it’s become clear that at any given time there are specific tactics, techniques, and procedures (TTPs) that are particularly prevalent. By analyzing and understanding these TTPs, you can dramatically enhance your security program.

Join & Write a Comment

In today’s complex data management environments, it is not unusual for UNIX servers to be dedicated to a particular department, purpose, or database.  As a result, a SAS® data analyst often works with multiple servers, each with its own data storage…
Using SQL Scripts we can save all the SQL queries as files that we use very frequently on our database later point of time. This is one of the feature present under SQL Workshop in Oracle Application Express.
Video by: Steve
Using examples as well as descriptions, step through each of the common simple join types, explaining differences in syntax, differences in expected outputs and showing how the queries run along with the actual outputs based upon a simple set of dem…
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…

760 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

Need Help in Real-Time?

Connect with top rated Experts

22 Experts available now in Live!

Get 1:1 Help Now