How to design database for sales tables in sql ??

Question

Hi,I have a employee table having following data:-

E_ID    E_Name  E_Zone  Username  Password

How do I design the tbltarget where I can keep track of targets for every employee on monthly basis. e.g. we can keep track of E_ID 501 has got how many target in sept month of 2017 and how many he achieved. I have designed the table like this way(below)

tbl_target :-

TID EID Target  month   Achieved
1   504 25     May        17
2   501 28     Jan        15
3   502 29     June       15
4   503 30     April      16
5   503 25     June       17
6   501 29     April      22
7   502 29     June       19
8   504 30     March      20
9   505 25     July       19
10  501 29     May        24

So, there is repetition of rows(E_ID).  Is there any effective method that you guys know that will work out here?? Pls let me know.

Answer 1

Your tbl_target table missing the column name of year. If you include that column name then you can filter the data 501 employee id may month of 2017. or else you will have two 501 record with same month.

 

Another option also you can add one more column Created_Date in that you can get year.

Answer 2

You need to join both the tables and get/track the employee records