• Post Reply Bookmark Topic Watch Topic
  • New Topic
programming forums Java Mobile Certification Databases Caching Books Engineering Micro Controllers OS Languages Paradigms IDEs Build Tools Frameworks Application Servers Open Source This Site Careers Other Pie Elite all forums
this forum made possible by our volunteer staff, including ...
Marshals:
  • Campbell Ritchie
  • Jeanne Boyarsky
  • Ron McLeod
  • Paul Clapham
  • Liutauras Vilda
Sheriffs:
  • paul wheaton
  • Rob Spoor
  • Devaka Cooray
Saloon Keepers:
  • Stephan van Hulst
  • Tim Holloway
  • Carey Brown
  • Frits Walraven
  • Tim Moores
Bartenders:
  • Mikalai Zaikin

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

 
Ranch Hand
Posts: 806
MySQL Database Spring Java
  • Mark post as helpful
  • send pies
    Number of slices to send:
    Optional 'thank-you' note:
  • 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.
 
Bartender
Posts: 10336
Hibernate Eclipse IDE Java
  • Mark post as helpful
  • send pies
    Number of slices to send:
    Optional 'thank-you' note:
  • 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
MySQL Database Spring Java
  • Mark post as helpful
  • send pies
    Number of slices to send:
    Optional 'thank-you' note:
  • 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
MySQL Database Spring Java
  • Mark post as helpful
  • send pies
    Number of slices to send:
    Optional 'thank-you' note:
  • 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
Hibernate Eclipse IDE Java
  • Mark post as helpful
  • send pies
    Number of slices to send:
    Optional 'thank-you' note:
  • 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
MySQL Database Spring Java
  • Mark post as helpful
  • send pies
    Number of slices to send:
    Optional 'thank-you' note:
  • 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
MySQL Database Spring Java
  • Mark post as helpful
  • send pies
    Number of slices to send:
    Optional 'thank-you' note:
  • 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.
 
If you open the box, you will find Heisenberg strangling Shrodenger's cat. And waving this tiny ad:
a bit of art, as a gift, the permaculture playing cards
https://gardener-gift.com
reply
    Bookmark Topic Watch Topic
  • New Topic