im a newbie..in both complex database design and oracle pl/sql ,and im very unsure/confused about the structure of my tables and how to normalize some info.
ok so heres the situation. i have a company,employees are distributed according to dept and section and category.each category has number of ranks and each rank has a range of grades that come under it. eg: AdministrationCategory of employees have Ranka 1,2,3,4. Rank 1 includes employees that are assigned grades 1 to 4, Rank 2 includes emps assigned grades 4-8 and so on.
Now,each emp,dependin on what category,rank and grade they fall into ,they differ in the number of holidays that they get. eg: annual leave for category1 ,rank1,grades 1-4 have annual leave of 20 days.
how can i incorporate the diff category,rank,grade wit the holiday table so as to retrieve the info that i want?
The tables that i currently have are below wit their column names.
*now how do i relate this to a table tht will define rank and grade dependin on category and the days allowed respectively?
do i create one table that lists the category and the ranks/grades under it? in this case wht would the primary key be? since there is one category and many ranks under it and many grades under one rank.
eg: category 1
here u can see tht i have a reoccuring grade..so i cant keep the grade as the pk either..
so would a table wit the following structure be right? and how would i relate it to the holiday table?
....and so on
So id have to create a new table for each category and list the rank/grade combinations? how would i reference/relate this to determine holiday allowed for employee?
Any guidance/help would be really appreciated
Thanks in advance.