Win a copy of Programmer's Guide to Java SE 8 Oracle Certified Associate (OCA) this week in the OCAJP forum!
  • Post Reply
  • Bookmark Topic Watch Topic
  • New Topic

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

 
chaitanya karthikk
Ranch Hand
Posts: 806
Java MySQL Database Spring
  • Mark post as helpful
  • send pies
  • Quote
  • Report post to moderator
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.
 
Paul Sturrock
Bartender
Posts: 10336
Eclipse IDE Hibernate Java
  • Mark post as helpful
  • send pies
  • Quote
  • Report post to moderator
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.
 
chaitanya karthikk
Ranch Hand
Posts: 806
Java MySQL Database Spring
  • Mark post as helpful
  • send pies
  • Quote
  • Report post to moderator
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
Posts: 806
Java MySQL Database Spring
  • Mark post as helpful
  • send pies
  • Quote
  • Report post to moderator
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
Posts: 10336
Eclipse IDE Hibernate Java
  • Mark post as helpful
  • send pies
  • Quote
  • Report post to moderator
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
Posts: 806
Java MySQL Database Spring
  • Mark post as helpful
  • send pies
  • Quote
  • Report post to moderator
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
Posts: 806
Java MySQL Database Spring
  • Mark post as helpful
  • send pies
  • Quote
  • Report post to moderator
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.
 
  • Post Reply
  • Bookmark Topic Watch Topic
  • New Topic