Member Avatar for oramars

hello all..

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.

Employee Table
empno(pk), fname,lname,gender,dob,doh,country,address,mobileno,homeno,
deptid,sectionid,qualification,designation.

Department Table
deptid, deptname

Section Table
sectionid, sectionname

Holiday Table
holiday_id,holiday_name,
*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
Ranks Grades
1 1-4
2 4-8
3 8-12

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?

Rank/Grade Category
R1G1 cat1
R1G2 cat1
R1G3 cat1
R1G4 cat1
R2G1 cat1
....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.

  • 2 Contributors
  • 1 Reply
  • 135 Views
  • 1 Month Discussion Span
  • Latest Post Latest Post by vimotaru
Member Avatar for vimotaru

I think the best way is creating a fact table that has all the information. To relate that information all fields must be equals.

The table would be:
cathegory|rank|grade|days_holidays|other_info_you_may_need
1|1|1
1|1|2
1|1|3
1|1|4
1|2|4
...

and so on. I put only numbers but you can use the content of the original fields. Be sure that the fields in origin tables has a foreign key to this table.

The days can be accesed querying an outer join.

Hope it helps.

Be a part of the DaniWeb community

We're a friendly, industry-focused community of developers, IT pros, digital marketers, and technology enthusiasts meeting, networking, learning, and sharing knowledge.