Database design using SQL Server 2005,

I have a user table with userid (pk), password, usertype.

I have another table student with stdid (pk), stdname, stdaddress开发者_高级运维.

I have a third table faculty with facid (pk), facname, facaddress.

What I want to do is populate the user table with the pk from either student table or faculty table.

How do I implement this in SQL Server 2005.

Can someone help me?

Edited Below

In my database, the student and faculty table are already implemented as part of another module. I need to implement the authentication part over LAN to be able to be accessed from anywhere, so I can not restructure that part. So I am using the user table with userid (username) and password in that table.

The userid is either stdid or facid and also the pk of the user table.

---

What you have is a bad design. You do not want to use one or the other other PK as the PK in a table. This cannot every work. What happens when you try to insert student 10 (who is Joe Jones) but faculty 10 (Mary Smith) is already in the table. Well, the insert would fail becuse of the unique requirement of a PK.

If all studetns and faculty are always users, then userID is the PK and FacultyId and StudentID are the FKs to that table not the reverse.

---

insert into user_table (select facid as userid from facultaty_table union select stdid as userid from student_table)

this will leave column password and usertype as null otherwise it pumps up userid_table with all the PKs from the others

---

What are the data types of the keys? Is there a guarantee that they're going to be unique across both the student and faculty tables?

This seems like it's going to be a difficult design to work with over time. Maybe try moving the central focus from the "person" tables to the user table and assign roles from there? Something like this:

User
------
Id
Password
UserType
(where's the username?)

UserInfo
------
UserID
Name
Address

Role
------
Id
Name

UserRole
------
UserID
RoleID

(The Role table would have at least two records, one called "Student" and one called "Faculty", but could allow for more roles as well.)

There are a few benefits here:

1. You need only store names and addresses in one place, rather than duplicate the model across different roles.
2. A user can have multiple roles. For example, a faculty member could take some classes and be a "student" for a while. A grad student can teach some classes and be a "faculty member" for a while.
3. New roles can be added without having to change the data model.