Database Design

[Cieci]

I want to create a project which I can keep track with my
Employees from the beginning(apply the job) until he/she
quit the job.

Could anybody please tell me how can I design my database
for this case.

Should I create two different table? One is for Candidate
and One is for Employees--- what I need is when I fill out
their application on the database. The candidate that I
select will carry their info from Candidate to Employee. I
don't know if I can duplicate the data from Candidate
Table to Employee table. If so, how can I do that. On the
other hand if I create only one table and use for both
case Candidates and Employees. There will not be a problem
for carrying data from one to another table, but it will
have another problem occur which is an Employee will be
able to apply a job only once since their ID will not be
unique anymore

Thank you,
Cieci

 

[Jeff Boyce]

Cieci

I'm not so sure you need to do all that extra work to move copies of data
from table to table. And duplicating data is rarely necessary, nor a good
idea.

From your description, it sounds like your database design might benefit
from a look into the topic of normalization.

It sounds like you are describing a Person, with attributes (firstname,
lastname, DOB, phone number), a status (Candidate, Employee) and some
information about timing of the person's (?multiple?) status events (i.e.,
candidate on this date, employed on this date, quit/fired on this date,
....).

Consider giving the table structure a bit more thought (and normalization)
before you build it.