Create 3 users tables

Hello,
In my web site I have 2 types of users: students and professors.
All users need to login but the personal information for each user type
has different fields.
So I create 3 tables: professors, students and authentication,
"professors" will have all the personal data from professors.
"students" will have all the personal data from students.
authentication will have the username and password from all users.
How can i link authentication table to both professors and students
tables so when the user logins i get the data from that user, either a
professor or student?
And is the right way to do this?
I am using SQL 2005 and Asp.Net 2.0
Thanks,
MiguelOn Wed, 21 Dec 2005 23:10:00 +0000, Miguel Dias Moura wrote:

>Hello,
>In my web site I have 2 types of users: students and professors.
>All users need to login but the personal information for each user type
>has different fields.
>So I create 3 tables: professors, students and authentication,
>"professors" will have all the personal data from professors.
>"students" will have all the personal data from students.
>authentication will have the username and password from all users.
>How can i link authentication table to both professors and students
>tables so when the user logins i get the data from that user, either a
>professor or student?
>And is the right way to do this?
>I am using SQL 2005 and Asp.Net 2.0
>Thanks,
>Miguel
Hi Miguel,
I would use one Persons table to hold information that applies to both
professors and students. Specific information then goes in the students
and the professors tables.
Login information could go in the Persons table, but is probably better
placed in a seperate table (for reasons of security, but also for
flexibility - maybe, someone will someday need two seperate accounts,
then you'll be ready for it).
The tables Persons, Students and Professors all have the same primary
key; this same column is also foreign key in the Students and Professors
table, in both cases referencing the Persons table.
The Logins table has userid as primary key (of course) and has also a
foreign key that referenced the Persons table.
Best, Hugo
--
(Remove _NO_ and _SPAM_ to get my e-mail address)