Results 1 to 8 of 8

Thread: table problems

  1. #1
    Join Date
    Mar 2011
    Posts
    4

    Exclamation table problems

    I HAVE A TABLE LIKE THIS......
    EMPLOYEE
    emp_id
    emp_name
    address
    date_of birth
    designation
    date_of entry
    current post hold
    current designation
    current post location
    department name
    .....................
    the things is that i wanna keep all the posting information history of every employee for later. i am thinking of making three table namely Employee, Posting and Department.
    is this wise thing to do. also how do i create relationship.
    plz i am very much in need of help

  2. #2
    Join Date
    Mar 2011
    Posts
    4

    Question relationship

    i am currently doing a project on employee management and having problem will the tables and relationship....
    Employee
    emp_id(pk)
    name
    designation...etc
    .........................
    Post
    post_id(pk)
    emp_id(fk)
    dep_id(fk)
    district
    post hold.....etc
    ......................
    Department
    dep_id(pk)
    dep_name......etc..somthing like this
    My problem is that an employee can be posted to different district. so how do i link between employee table and post.
    or are there any other solution..
    PLZ HELP ME, I AM ONLY A STUDENT AND HAVE SO MUCH TO LEARN
    Attached Thumbnails Attached Thumbnails export.jpg  

  3. #3
    Join Date
    Jun 2003
    Location
    Ohio
    Posts
    12,592
    First, what the hell kind of naming convention are you using?

    Second, depending on your requirements, you can create a history table that stores a history of all the records in your production table. Use a trigger on your production table to populate the history table on inserts, updates, and deletes.
    If it's not practically useful, then it's practically useless.

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

  4. #4
    Join Date
    Nov 2004
    Location
    out on a limb
    Posts
    13,692
    so separate the employee's role from the details of the employee

    you may need to add tables for departments
    you need toi decide if you need the history of the employee's addresses.. if so a separate table is required for that
    you need to know what period a specific role runs fro (eg a start + end date)
    you need to decide if the location is somehtign that should be normalised or is conditional on some other element
    eg it could be that the location is a fnction of department
    if could be the location (say building) is a separate function / table
    I'd rather be riding on the Tiger 800 or the Norton

  5. #5
    Join Date
    Feb 2004
    Location
    In front of the computer
    Posts
    15,579
    You need to add a many-to-many linking table. This is a table that describes an employee assignment to a district, so it has informatoin about that specific assignment such as employee_id, district_id, date_start, date_end, comments, etc. By creating multiple rows in this table, you can assign an employee to multiple districts either at the same time or in sequence (so that one ends before another begins).

    -PatP
    In theory, theory and practice are identical. In practice, theory and practice are unrelated.

  6. #6
    Join Date
    Mar 2011
    Posts
    4

    Question table design

    well i don't exactly know how to put it....but here goes......
    i wanna do a little project on employee service record book which will records all the employee details and his posting activities(transfered to other area)
    My PROBLEMS starts with the POST list for every employee because i wanna keep all the history.
    There is also a POST CREATION LIST from which all the post are filled.
    Next is the DEPARTMENT details(like its location...etc) which is also needed to be keep.
    MAIN CONCERN:
    1.HOW do i make the table structure
    2. IF SO how do i create a relationship with the EMPLOYEE table and POST table OR should i create relationship with POSTING CREATION LIST table
    3.What about the DEPARTMENT table.

    I know it's my task to do all this stuff but it cost me a month to setup all these tables and their relationship BUT can anyone help me solve this problem or gives any suggestions.......................THANK YOU
    Attached Thumbnails Attached Thumbnails ERDIAGRAM.jpg   export.jpg  
    Last edited by zorrs; 03-05-11 at 14:14. Reason: put attachment

  7. #7
    Join Date
    Apr 2002
    Location
    Toronto, Canada
    Posts
    20,002
    Quote Originally Posted by zorrs View Post
    ...it cost me a month to setup all these tables and their relationship
    show us what you've got so far
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL

  8. #8
    Join Date
    Mar 2011
    Posts
    4

    Arrow ER diagram

    Quote Originally Posted by r937 View Post
    show us what you've got so far
    please take a look at my diagram and please tell me where i am wrong.

Posting Permissions

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