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

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

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.

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

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

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

show us what you've got so far

please take a look at my diagram and please tell me where i am wrong.