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

    Join Date
    Apr 2013
    Posts
    6
    Rep Power
    0

    Help with data insertion


    Hi,

    For part of a project I need to write a SQL statement which lists all the employees working on a site(housing development).. Im creating the db for it at the moment on phpmyadmin, and because there will be multiple employees working on one site, how will i show this on the database? I have one field for employee_id and you cant duplicate that field in order to enter another persons employee id.

    Any help will be appreciated.

    Tanks, BIG Laz
  2. #2
  3. Did you steal it?
    Devshed Supreme Being (6500+ posts)

    Join Date
    Mar 2007
    Location
    Washington, USA
    Posts
    14,066
    Rep Power
    9398
    How about you create the database and tables first and tell us what they look like? Then we can see what to do next.
    If you'd like advice on creating them we can totally help there - just let us know what kind of data and relationships you need to have.
  4. #3
  5. No Profile Picture
    Registered User
    Devshed Newbie (0 - 499 posts)

    Join Date
    Apr 2013
    Posts
    6
    Rep Power
    0
    Originally Posted by requinix
    How about you create the database and tables first and tell us what they look like? Then we can see what to do next.
    If you'd like advice on creating them we can totally help there - just let us know what kind of data and relationships you need to have.
    Well i created a table called (development) and i need to assign two employees to that table with the column -employee_id- , as two employees are working on that single development, so how do i go around doing this?

    I tries adding two separate columns for the same development, but thats just duplicating data and i am sure there must have a way around this........
  6. #4
  7. Did you steal it?
    Devshed Supreme Being (6500+ posts)

    Join Date
    Mar 2007
    Location
    Washington, USA
    Posts
    14,066
    Rep Power
    9398
    Excellent observation. There is a better way: a third table.

    To be clear, you have a table for employees and a table for developments, right? Now make a third table that relates the two. It only needs two columns, one for the employee ID and one for the development ID. (You could have other columns if you wanted, like maybe the start and end dates the employee worked on the development, but the table only needs the two IDs.)
    Two employees on the same development means there will be two rows in this table.
  8. #5
  9. No Profile Picture
    Registered User
    Devshed Newbie (0 - 499 posts)

    Join Date
    Apr 2013
    Posts
    6
    Rep Power
    0
    Originally Posted by requinix
    Excellent observation. There is a better way: a third table.

    To be clear, you have a table for employees and a table for developments, right? Now make a third table that relates the two. It only needs two columns, one for the employee ID and one for the development ID. (You could have other columns if you wanted, like maybe the start and end dates the employee worked on the development, but the table only needs the two IDs.)
    Two employees on the same development means there will be two rows in this table.
    Yes i have a table called employee and a table called development.

    Okay, thanks for that, but i am still confused how the new table will show the 2 employees working for 1 development in 1 row of the table
  10. #6
  11. Did you steal it?
    Devshed Supreme Being (6500+ posts)

    Join Date
    Mar 2007
    Location
    Washington, USA
    Posts
    14,066
    Rep Power
    9398
    Example:
    Code:
    employee
    
    id | firstname | lastname
    ---+-----------+---------
     1 | John      | Doe
     2 | Jane      | Smith
    
    
    development
    
    id | name
    ---+----------------------------------
     1 | is a development like a building?
     2 | or something else?
    
    
    the table relating employees to developments
    
    employee id | development id
    ------------+---------------
              1 |              1
              1 |              2
              2 |              2
    - John Doe is working on the building development
    - He's also working on the other development
    - Jane Smith is working on the other development with him
  12. #7
  13. No Profile Picture
    Registered User
    Devshed Newbie (0 - 499 posts)

    Join Date
    Apr 2013
    Posts
    6
    Rep Power
    0
    Originally Posted by requinix
    Example:
    Code:
    employee
    
    id | firstname | lastname
    ---+-----------+---------
     1 | John      | Doe
     2 | Jane      | Smith
    
    
    development
    
    id | name
    ---+----------------------------------
     1 | is a development like a building?
     2 | or something else?
    
    
    the table relating employees to developments
    
    employee id | development id
    ------------+---------------
              1 |              1
              1 |              2
              2 |              2
    - John Doe is working on the building development
    - He's also working on the other development
    - Jane Smith is working on the other development with him
    Thank you very much for taking out your time for this, but I have one more question in this topic.

    I now underhand how to show an individual employee being assigned to a development, but how do I show two employees being assigned to one development?

    I see on the third table (that relates the employee and development) every column only shows one ID number representing one development, as either 1 or 2 is in each column so how does that relate two employees to one development?

    Sorry for the slow knowledge gain, but SQL ain't my strong area of the road I'm following
  14. #8
  15. No Profile Picture
    Registered User
    Devshed Newbie (0 - 499 posts)

    Join Date
    Oct 2012
    Posts
    1
    Rep Power
    0
    They refer to the other databases.

    For the first row in the third table shows the employee with employee id 1 (John Doe) is working on the development with development id 1 (is a development like a building?).

    Second row, employee id 1 (John Doe) and development id 2 (or something else?).

    Row three, employee id 2 (Jane Smith) and development id 2 (or something else?).
  16. #9
  17. No Profile Picture
    Registered User
    Devshed Newbie (0 - 499 posts)

    Join Date
    Apr 2013
    Posts
    5
    Rep Power
    0
    Hello LazDeMan,

    I may be a bit late for your project, but thought I'd give an answer to your last question.

    With your third table, you have the ability to find either all developments assigned to an employee:

    Code:
    SELECT development.name
    FROM dev_emp, development
    WHERE dev_emp.employee_id =1
    AND development.id = dev_emp.development_id
    Emp 1(John Doe) is assigned to development 1 and 2, which are the ids for 'is a development like a building?' and 'or something else?'.


    OR you can have it display all employees assigned to a specific development.
    Code:
    SELECT employee.firstname, employee.lastname
    FROM employee, dev_emp, development
    WHERE dev_emp.develpoment_id = development.id
    AND employee.id = dev_emp.employee_id
    AND dev_emp.development_id =2
    Development 2 has both John Doe and Jane Smith assigned to it, which will be the result of the above query on the third table.

    This is because the third table allows a many to many relationship in regards to employee ID and development ID.

IMN logo majestic logo threadwatch logo seochat tools logo