aspose file tools*
The moose likes JDBC and the fly likes How to model a relation where a column has to refer two columns in different tables? Big Moose Saloon
  Search | Java FAQ | Recent Topics | Flagged Topics | Hot Topics | Zero Replies
Register / Login


Win a copy of Spring in Action this week in the Spring forum!
JavaRanch » Java Forums » Databases » JDBC
Bookmark "How to model a relation where a column has to refer two columns in different tables?" Watch "How to model a relation where a column has to refer two columns in different tables?" New topic
Author

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

chaitanya karthikk
Ranch Hand

Joined: Sep 15, 2009
Posts: 806

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 <!-- this should refer both tables -->
timein
timeout

Can anyone guide me. Thanks in advance.


Love all, trust a few, do wrong to none.
Paul Sturrock
Bartender

Joined: Apr 14, 2004
Posts: 10336

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.


JavaRanch FAQ HowToAskQuestionsOnJavaRanch
chaitanya karthikk
Ranch Hand

Joined: Sep 15, 2009
Posts: 806

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.
chaitanya karthikk
Ranch Hand

Joined: Sep 15, 2009
Posts: 806

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
Paul Sturrock
Bartender

Joined: Apr 14, 2004
Posts: 10336

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?


chaitanya karthikk
Ranch Hand

Joined: Sep 15, 2009
Posts: 806

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.
chaitanya karthikk
Ranch Hand

Joined: Sep 15, 2009
Posts: 806

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.
 
I agree. Here's the link: http://aspose.com/file-tools
 
subject: How to model a relation where a column has to refer two columns in different tables?