Page 1 of 4 123 ... LastLast
Results 1 to 15 of 56
  1. #1
    Join Date
    Mar 2007
    Location
    010101010110100
    Posts
    803

    Can I use 2 relationships in parent/child tables

    I have an employee and employee performance table. I am trying to track the performance of employees. The performance table will be about an employee and his performance. I also want to log the employee doing the performance but I can't model this for some reason.

    Code:
    Create table employee_Performance (
    	employee_Seq Int NOT NULL,
    	type Char(20) NOT NULL,
    	date Date NOT NULL,
    	notes Blob NOT NULL,
    	performance_Seq Int NOT NULL AUTO_INCREMENT,
    	UNIQUE (performance_Seq),
     Primary Key (performance_Seq)) ENGINE = InnoDB;
    
    Create table employee (
    	f_Name Char(20) NOT NULL,
    	l_Name Char(50) NOT NULL,
    	address1 Char(20) NOT NULL,
    	address2 Char(10) NOT NULL,
    	ssn Char(11) NOT NULL,
    	employee_Seq Int NOT NULL,
    	UNIQUE (employee_Seq),
     Primary Key (employee_Seq)) ENGINE = MyISAM;
    
    Create table employee_Performance_Type (
    	type Char(20) NOT NULL,
    	UNIQUE (type),
     Primary Key (type)) ENGINE = MyISAM;
    
    
    Alter table employee_Performance add unique Alter_Key4 (employee_Seq,type,date);
    Alter table employee add unique Alter_Key3 (l_Name,ssn);
    
    
    Alter table employee_Performance add Foreign Key (employee_Seq) references employee (employee_Seq) on delete  restrict on update  restrict;
    Alter table employee_Performance add Foreign Key (type) references employee_Performance_Type (type) on delete  restrict on update  restrict;
    Can I create a second relationship between the employee and performance tables for the employee doing the performance and rename the PK column?

    Sometimes a pic is worth a thousand words. I have changes the rolename to manager in the performance table.

    Thanks,

    Frank
    Attached Thumbnails Attached Thumbnails export.jpg  
    Last edited by Frunkie; 09-02-07 at 17:56.

  2. #2
    Join Date
    Jan 2007
    Location
    UK
    Posts
    11,434
    I'm confused... how does an employee "do performance"?
    Do you mean the person "appraising" the employee?

    If the appraiser can only be another employee then store the appraisers "employee_Seq" against the performance record for the employee (this is assuming that an employee can only have one appraiser?)

    If I've got the wrong end of the stick let me know!!
    George
    Home | Blog

  3. #3
    Join Date
    Mar 2007
    Location
    010101010110100
    Posts
    803
    Quote Originally Posted by georgev
    I'm confused... how does an employee "do performance"?
    Do you mean the person "appraising" the employee?

    If the appraiser can only be another employee then store the appraisers "employee_Seq" against the performance record for the employee (this is assuming that an employee can only have one appraiser?)

    If I've got the wrong end of the stick let me know!!
    Hey George.

    "Evaluator" would be a better word. So.. an Evaluator or in real life terms, manager will be doing an assessment of an employee.

    I'm kind of lost on your suggestion tho..

  4. #4
    Join Date
    Jan 2007
    Location
    UK
    Posts
    11,434
    Can one employee have one evaluator per performance record?
    George
    Home | Blog

  5. #5
    Join Date
    Mar 2007
    Location
    010101010110100
    Posts
    803
    Quote Originally Posted by georgev
    Can one employee have one evaluator per performance record?
    Yes, only one.

  6. #6
    Join Date
    Jan 2007
    Location
    UK
    Posts
    11,434
    Then store the evaluators unique identifier against the performance record
    Simple as that!
    George
    Home | Blog

  7. #7
    Join Date
    Mar 2007
    Location
    010101010110100
    Posts
    803
    Quote Originally Posted by georgev
    Then store the evaluators unique identifier against the performance record
    Simple as that!
    Uhh.. Thats what I thought I did. Did you look at the pic I uploaded? It is the "manager" FK. I just changed the rolename from employee_Seq to manager. I just want to know if this is acceptable or if there a better way to do this?

  8. #8
    Join Date
    Jan 2007
    Location
    UK
    Posts
    11,434
    I only read the code, missed the pic!
    I also note your performance type table... Should it not have an auto incrementing identifier used as the foreign key in the performance table?
    I just want to know if this is acceptable or if there a better way to do this?
    It's the way I'd do it... You can easily retrieve any information about the evaluator using a LEFT OUTER JOIN to the employee table.
    George
    Home | Blog

  9. #9
    Join Date
    Mar 2007
    Location
    010101010110100
    Posts
    803
    Quote Originally Posted by georgev
    I only read the code, missed the pic!
    I also note your performance type table... Should it not have an auto incrementing identifier used as the foreign key in the performance table?

    It's the way I'd do it... You can easily retrieve any information about the evaluator using a LEFT OUTER JOIN to the employee table.
    The performance_Type table is just a table that will hold the value of the performance. I was going to just use the value itself as the PK. That was always a question I wanted to ask but never got around to doing.

    I remember blindman once said something about not changing the column name for some reason or another, but I can't find the thread. He said that it was incorrect.

  10. #10
    Join Date
    Jan 2007
    Location
    UK
    Posts
    11,434
    So the type table is a lookup?
    George
    Home | Blog

  11. #11
    Join Date
    Mar 2007
    Location
    010101010110100
    Posts
    803
    Quote Originally Posted by georgev
    So the type table is a lookup?
    No. The only lookup table I have is the performance_Type table. I just went with changing the column name. It seems to work rather well.

    I pulled out one of my old database books and I was able to find one of those generic models in the back of the book and found in one of the models that the designer had done the same thing. I read the DDL and the column name was changed also so I think I am ok.

  12. #12
    Join Date
    Apr 2002
    Location
    Toronto, Canada
    Posts
    20,002
    Quote Originally Posted by georgev
    Should it not have an auto incrementing identifier used as the foreign key in the performance table?
    noooooooooooooooo.............

    shock! dismay!

    oh grasshopper, you were coming along so nicely...
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL

  13. #13
    Join Date
    Apr 2002
    Location
    Toronto, Canada
    Posts
    20,002
    frank, as a data modelling exercise, could you take a moment and draw another nice picture, this time without using "seq" columns as your PKs
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL

  14. #14
    Join Date
    Jan 2007
    Location
    UK
    Posts
    11,434
    Quote Originally Posted by r937
    Quote Originally Posted by georgev
    Should it not have an auto incrementing identifier used as the foreign key in the performance table?
    noooooooooooooooo.............

    shock! dismay!

    oh grasshopper, you were coming along so nicely...
    So a char(20) lookup value is a good thing?
    George
    Home | Blog

  15. #15
    Join Date
    Apr 2002
    Location
    Toronto, Canada
    Posts
    20,002
    Quote Originally Posted by georgev
    So a char(20) lookup value is a good thing?
    of course it is why wouldn't it be?
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •