Foreign key for multiple tables

I have 3 tables, student, parent, and staff with PK studentid, parentid, staffid respectively. I want to create a member table, with PK memberid, username, password. One member can be a student or parent or staff. I want to join this member table and member related table (if member is student, student table and so on) with a foreign key.

Currently I'm using another column for member type and put it as

1 for student, 
2 fo开发者_如何学Gor parent and 
3 for staff, 

and doing program with php.

But I need another efficient way to create tables using relational structure.

I assume you are using MySQL, but other DBMS may be similar.

There is no way to do what you are asking with a single column or table. You have a few options to implement this using relational databases:

Option 1: Have your member table include three columns with relational constraints to the staff, parent and student tables. A user's type is implied by which column is filled (leave the others NULL)

Option 2: Put the data you want to store on each member on the staff, parent and student tables (like the username and password)

Each has its own downsides, the first being you will have to check each column to find out what type of member the current individual is and join the correct table, or search three tables when you need log someone in. There is also not single pool of memberid's for your members. (You could do something similar to what @Sherif suggested)

In the student, parent, staff tables, add a "fk_memberID" column which points to the associated record in the member table.

When creating a new member, you first insert the member data, and get the memberid of the new record.

Then when you insert into the specific type table, you put the memberid in at the end.

For the member table memberID and memberType will be composite key.

For JOIN you have to join on both of the keys at a time.

E.G.

To get the list of all the Students

SELECT m.* FROM
    student s INNER JOIN
    member m ON ( m.memberID = s.studentID AND m.memberType = 1 )

Create Table

 CREATE TABLE userid(counter int(11));

Insert into this table 1 record with counter = 100001 for example

Now create a procedure getnextid(newid)

 DELIMITER $$
 USE `your_database`$$
 DROP PROCEDURE IF EXISTS `getnextid`$$
 CREATE DEFINER=`username`@`%` PROCEDURE `getnextid`(OUT newid INT)
 BEGIN
SELECT counter INTO newid FROM userid FOR UPDATE;
UPDATE userid SET counter = counter+1;
END$$
DELIMITER ;

Now everytime you want to insert in any of these students .. you can call getnextid() and set his id to this number...

In this way you will have to force the foreign key constraints in your queries but it works .. Everyone has an id from the same pool (: