Page 1 of 2 12 LastLast
Results 1 to 15 of 27
  1. #1
    Join Date
    Jan 2007
    Location
    UK
    Posts
    11,434
    Provided Answers: 10

    Unanswered: Visio: Employees and managers

    Ok, I have the following table
    Code:
    CREATE TABLE employees (
        employee_number char(6) NOT NULL
      , known_as char(20)
      , surname char(20)
      , job_title char(20)
      , manager_number char(6) NULL
      , unique_identifier char(6) NOT NULL PRIMARY KEY
    )
    --unique_identifier is in the format 000123456789
    Now I have a conundrum when trying to create organization charts in Visio, so I figured I'd try reproduce the format that a bunch of walkthroughs suggest, which is with the first column being an int identity(1,1) column as the employeeID with the managerID being an int column also, referencing the employeeID.
    Hope I've not lost you just yet
    So here's what I figured - create a new table with the new integer columns, slap my current data into it and then update the managerID as necessary...

    Except I can't work out the update statement for this!
    Code:
    CREATE TABLE gvVisioTest (
        employeeID int identity(1,1)
      , employee_number char(6)
      , job_title char(40)
      , department_reference char(10)
      , managerID int
      , manager_number char(6)
    )
    GO
    
    INSERT INTO gvVisioTest(employee_number, job_title, department_reference, manager_number)
    SELECT employee_number, job_title, department_reference, manager_number
    FROM   employees
    GO
    
    --Update managerID with relevant employeeID
    GO
    
    DROP TABLE gvVisioTest
    Any ideas?

    Oh and this is legacy so the design is flawed, modified over time (the manager field is a bodge put in 5 years ago), so yeah unfortunately I have to work with what I've got
    George
    Home | Blog

  2. #2
    Join Date
    Feb 2004
    Location
    One Flump in One Place
    Posts
    14,912
    inner join gvVisioTest with employees on employee_number = manager_number and get the new employeeID for the manager. Then join that derived table to gvVisioTest and update managerID. Then drop managerID to loose the third normal form violation.

  3. #3
    Join Date
    Apr 2002
    Location
    Toronto, Canada
    Posts
    20,002
    "loose the 3NF violation?"

    george, what is your original conundrum? what's wrong with the original employees table?

    and pray tell how do you fit 000123456789 into CHAR(6)?
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL

  4. #4
    Join Date
    Jan 2007
    Location
    UK
    Posts
    11,434
    Provided Answers: 10
    Char(12) ***

    The conundrum is that I have the table structure as mentioned above and I have to produce an org chart from it (if possible) and as I understand it Visio dislikes the fact that we use char(6) for our employee numbers...
    For example an "employee number" can contain 012345, 123456, ABC123, and so on.
    So I thought that if I assigned each employee an arbitrary integer value (using an identity field) and then updated a managerID integer field with the corresponding value then Visio might like me a bit more.

    Poots: I keep on trying but can't seem to get close to what's needed here
    George
    Home | Blog

  5. #5
    Join Date
    Jan 2007
    Location
    UK
    Posts
    11,434
    Provided Answers: 10
    Code:
    SELECT e.employeeID
         , m.employeeID As [managerID]
         , e.employee_number
         , e.manager_number
    FROM   gvVisioTest e
     INNER
      JOIN gvVisioTest m
        ON e.manager_number = m.employee_number
    This gives me the relevant value for the managerID, but I can't get it to work in an update statement...
    George
    Home | Blog

  6. #6
    Join Date
    Apr 2002
    Location
    Toronto, Canada
    Posts
    20,002
    Quote Originally Posted by georgev
    ... and as I understand it Visio dislikes the fact that we use char(6) for our employee numbers...
    WTF ????? ************************************

    i say again ************************************

    i don't have visio, and now i don't want it

    if what you say is true, george, then visio is a piece of übershit
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL

  7. #7
    Join Date
    Feb 2004
    Location
    One Flump in One Place
    Posts
    14,912
    I meant lose - as you know.
    And I meant manager_number as you might not have known.

    I also agree that changing the schema to suit visio is somewhat cart and horse confusion

  8. #8
    Join Date
    Jan 2007
    Location
    UK
    Posts
    11,434
    Provided Answers: 10
    Ok, don't take what I said as gospel - I'm very new to Visio and just relaying the impressions I have got from the messages (and results) it's given me.

    Messages such as "Duplicate entry .... "KN" ..." where there are a number of employee numbers starting with KN - but they're all unique.
    George
    Home | Blog

  9. #9
    Join Date
    Jan 2007
    Location
    UK
    Posts
    11,434
    Provided Answers: 10
    Well, Viso is the only software I have available to produce organisation charts and because I won't have to do this often at all, I'm willing to compromise what needs to be done so I get the desired results.

    Sucks, huh?
    George
    Home | Blog

  10. #10
    Join Date
    Apr 2002
    Location
    Toronto, Canada
    Posts
    20,002
    okay, poots, fair enough, i should have automatically corrected your loose spelling

    what 3NF violation plz? how does manager_number violate 3NF?
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL

  11. #11
    Join Date
    Sep 2003
    Location
    The extremely Royal borough of Kensington, London
    Posts
    778
    Oh dear Georgie.

    What kind of "Management" do you have in your company, for who have decided on a new approach to the development of Information Systems: Visio Driven Development (VDD).

    If your data model is wrong, it doesn't matter. What's important is that Visio can read it and create its own interpretation of an "org chart" from it.

    As has been said many a time in the past: you need a proportional share of individuals of sub-standard mental capacity in order to maintain the intelligence balance of society.

    Regarding Visio, it is an application that is developed by Microsoft, and designed for HR Managers who want to create a fancy employee chart for the organisation.

    Regards,
    Last edited by r123456; 11-15-07 at 09:12.
    Bessie Braddock: Winston, you are drunk!
    Churchill: And Madam, you are ugly. And tomorrow, I'll be sober, and you will still be ugly.

  12. #12
    Join Date
    Sep 2003
    Location
    The extremely Royal borough of Kensington, London
    Posts
    778
    Quote Originally Posted by r937
    okay, poots, fair enough, i should have automatically corrected your loose spelling

    what 3NF violation plz? how does manager_number violate 3NF?
    It will cause a violation if manager_number is dependent on manager_ID, and managerID is a non-key attribute in the gVisioTest relation, which I imagine many people would believe it to be.
    Bessie Braddock: Winston, you are drunk!
    Churchill: And Madam, you are ugly. And tomorrow, I'll be sober, and you will still be ugly.

  13. #13
    Join Date
    Jan 2007
    Location
    UK
    Posts
    11,434
    Provided Answers: 10
    It's not VDD, it's VDS (Visio Driven Solutions ).
    I'm open to suggestions, but Visio appears to be all I have to produce pretty diagrams for the organisation.

    Yeah, it doesn't appear to like text identifiers... I just tried running of the divisional structure and it threw up 39 duplicate errors because the first 4 characters of a 6 character code were the same...
    I think you're right, I shouldn't have to change correct data to get visio to do what I want - visio should already be able to do it for me.
    I must be missing a trick here - back to google I go
    George
    Home | Blog

  14. #14
    Join Date
    Jun 2003
    Location
    Ohio
    Posts
    12,592
    Provided Answers: 1
    Gotta second Rudy's opinion here.
    OK, r123456 already seconded it, so I'll "third" it.
    Your original table design looked fine to me. Not "flawed", though I would have used the manager's GUID to link the records if you are going to have that surrogate key in there anyway.
    And I would throw fits if my application/database design was dictated by third-party vendor tools. That's like building a house without using screws because you only have a hammer.
    If it's not practically useful, then it's practically useless.

    blindman
    www.chess.com: "sqlblindman"
    www.LobsterShot.blogspot.com

  15. #15
    Join Date
    Sep 2003
    Location
    The extremely Royal borough of Kensington, London
    Posts
    778
    Perhaps you could suggest that Visio is not capable of producing Org. charts, which it doesn't appear to be?
    Bessie Braddock: Winston, you are drunk!
    Churchill: And Madam, you are ugly. And tomorrow, I'll be sober, and you will still be ugly.

Posting Permissions

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