#1
  1. No Profile Picture
    Contributing User
    Devshed Newbie (0 - 499 posts)

    Join Date
    Oct 2009
    Posts
    31
    Rep Power
    5

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


    Hi all, I am Chaitanya, I am working on a project and is in design stage now. The application is all about tracking the attendance details of a school. Obviously schools have staff and students. We are supposed to track the attendance details for both actors.

    We first deploy a huge RFID based network, which consists of many RFIDs and RFID switches. All these are in a LAN.

    There is a possibility that a student/staff can swipe his/her card from any where in the school.

    We created two different tables, one for student and one for faculty. Because both have different attributes. But both the actors perform the same operation, that is swiping the cards and enrolling attendance (in,out) for a day.

    We created a table named attendance whose structure is like this

    ATTENDANCE
    ================
    trans_id BIGINT PRIMARY KEY
    pid BIGINT //this column has to refer both student and staff table
    timein TIMESTAMP
    timeout TIMESTAMP

    Can anyone tell me how to model such kind of relations? Thank you all in advance.
  2. #2
  3. SQL Consultant
    Devshed Supreme Being (6500+ posts)

    Join Date
    Feb 2003
    Location
    Toronto Canada
    Posts
    27,120
    Rep Power
    4274
    Originally Posted by kittu_564
    Can anyone tell me how to model such kind of relations?
    use a supertype/subtype structure -- persons table as the supertype, with students and staff tables as subtypes

    the attendance table will then refer (via pid) to the supertype table
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL
  4. #3
  5. No Profile Picture
    Contributing User
    Devshed Newbie (0 - 499 posts)

    Join Date
    Oct 2009
    Posts
    31
    Rep Power
    5
    Hi r937, can you please explain in details? Thanks in advance
  6. #4
  7. SQL Consultant
    Devshed Supreme Being (6500+ posts)

    Join Date
    Feb 2003
    Location
    Toronto Canada
    Posts
    27,120
    Rep Power
    4274
    Originally Posted by kittu_564
    Hi r937, can you please explain in details?
    no thanks, i'm too busy

    have a look here -- http://lmddgtfy.com/?q=supertype%2Fsubtype
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL
  8. #5
  9. No Profile Picture
    Contributing User
    Devshed Newbie (0 - 499 posts)

    Join Date
    Oct 2009
    Posts
    31
    Rep Power
    5
    Hi r937, did you mean this kind of sturcture?

    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
  10. #6
  11. No Profile Picture
    Contributing User
    Devshed Newbie (0 - 499 posts)

    Join Date
    Oct 2009
    Posts
    31
    Rep Power
    5
    Thank you r937, can you please make a look at my reply when you free.
  12. #7
  13. SQL Consultant
    Devshed Supreme Being (6500+ posts)

    Join Date
    Feb 2003
    Location
    Toronto Canada
    Posts
    27,120
    Rep Power
    4274
    that structure is almost correct

    each of the subtype tables should have pid as the primary key, not their own separate primary keys
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL
  14. #8
  15. No Profile Picture
    Contributing User
    Devshed Newbie (0 - 499 posts)

    Join Date
    Oct 2009
    Posts
    31
    Rep Power
    5
    Hi r937, did you mean the schema should look like this?

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

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

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

    Attendence
    =========
    transid BIGINT PRIMARY KEY
    pid BIGINT FOREIGN KEY
    inttime TIMESTAMP
    outtime TIMESTAMP
  16. #9
  17. SQL Consultant
    Devshed Supreme Being (6500+ posts)

    Join Date
    Feb 2003
    Location
    Toronto Canada
    Posts
    27,120
    Rep Power
    4274
    why does student have pid same as person, but staff have staff_id instead of pid?

    also, BIGINT is too big
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL
  18. #10
  19. No Profile Picture
    Contributing User
    Devshed Newbie (0 - 499 posts)

    Join Date
    Oct 2009
    Posts
    31
    Rep Power
    5
    Hi r937, this is the schema

    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;

    Its working right as expected. Please tell me whether this is right or not.
  20. #11
  21. SQL Consultant
    Devshed Supreme Being (6500+ posts)

    Join Date
    Feb 2003
    Location
    Toronto Canada
    Posts
    27,120
    Rep Power
    4274
    Originally Posted by kittu_564
    Please tell me whether this is right or not.
    it is not right

    obviously you have not tested it on actual data

    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL
  22. #12
  23. No Profile Picture
    Contributing User
    Devshed Newbie (0 - 499 posts)

    Join Date
    Oct 2009
    Posts
    31
    Rep Power
    5
    Originally Posted by r937
    it is not right

    obviously you have not tested it on actual data

    I tested it r937, after testing only I posted it on the forum.
  24. #13
  25. SQL Consultant
    Devshed Supreme Being (6500+ posts)

    Join Date
    Feb 2003
    Location
    Toronto Canada
    Posts
    27,120
    Rep Power
    4274
    Originally Posted by kittu_564
    I tested it r937, after testing only I posted it on the forum.
    how do you explain this --
    Code:
    WHERE date(a.timein)=CURRENT_TIMESTAMP
    this will be correct only at midnight

    i say again, you did not test this

    Comments on this post

    • kittu_564 agrees
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL
  26. #14
  27. No Profile Picture
    Contributing User
    Devshed Newbie (0 - 499 posts)

    Join Date
    Oct 2009
    Posts
    31
    Rep Power
    5
    Oops!!! I am really sorry r937, thats

    where date(a.timein)=date(current_timestamp)

    This is working fine.

    I executed with where date(a.timein)=current_timestamp

    it dint work. Thanks for correcting me.
    Last edited by kittu_564; June 4th, 2011 at 06:57 AM.

IMN logo majestic logo threadwatch logo seochat tools logo