How to model a relation where a column has to refer two columns in different tables?

Hi all, I don't know whether I can ask a database design issue here. But I am giving a trial. Please forgive me if this isn't the correct forum to discuss.

I have a table which tracks the attendance details of faculty and students in a school. I have two different tables, one for student and other for faculty and common attendance table for both.

How can I refer to both primary keys from one column? How should I design this. Should I separate the attendance table, one for students and other for faculty?

Attendance
================
transid PRIMARY KEY
pid 
timein
timeout

Can anyone guide me. Thanks in advance.

This can't really be done in a relational database, at least not in a way that actually uses a constraint. You can do it without using a constraint though this is obviously unsafe. What you could do is use two attendance entities, one for each type. Or you could use two key columns in your attendance table and a constraint to prevent values appearing in a row for both.

Paul Sturrock wrote:Or you could use two key columns in your attendance table and a constraint to prevent values appearing in a row for both.

Hi Paul, could you please explain how to achieve this? I dint understood this phrase.

Thanks in advance.

Is this a right approach?

Person
=========
pid BIGINT PRIMARY KEY

Student
=========
studid BIGINT PRIMARY KEY
pid BIGINT FOREIGN KEY

Staff
=========
staffid BIGINT PRIMARY KEY
pid BIGINT FOREIGN KEY

Attendence
=========
transid BIGINT PRIMARY KEY
pid BIGINT FOREIGN KEY
inttime TIMESTAMP
outtime TIMESTAMP

Students present for the current day
=============================
select s.studid from student s,attendance a,person p where a.date(intime)=current_date and a.pid=p.pid and p.pid=s.pid

How do you define the FK on pid for attendance? Like I said, it shouldn't be possible to do this; how does the database know how to enforce this constraint?

Before adding a new student/staff to the database a new id will be inserted into person table, then student/staff will be added to the appropriate table. In the same way attendance table will also refer to person table. Indirectly it is referring to student and staff tables.

Ok the schema goes like this, just now created and tested, worked fine.

CREATE TABLE person(pid BIGINT PRIMARY KEY);

CREATE TABLE students(sid BIGINT PRIMARY KEY,sname VARCHAR(32) NOT NULL, FOREIGN KEY(sid) REFERENCES person(pid));

CREATE TABLE staff(sid BIGINT PRIMARY KEY,sname VARCHAR(32) NOT NULL, FOREIGN KEY(sid) REFERENCES person(pid));

CREATE TABLE attendance(transid BIGINT PRIMARY KEY,pid BIGINT NOT NULL, timein TIMESTAMP NOT NULL, FOREIGN KEY(pid) REFERENCES person(pid));

Selection
======
SELECT s.sname FROM students s, attendance a, person p WHERE date(a.timein)=CURRENT_TIMESTAMP and a.pid=p.pid AND p.pid=s.sid;

Hope I am doing the right thing. Thank you Paul for your replies.