Is my database design ok for the given report?
-
Alo,
My department recieves a compiled data in excel format for recruitment (collected on a daily basis). While the format of the excel remains unchanged for a year or so, they do have the right to add or remove columns. The difficulty i am having now is after we get the excel reports every month, generating annual report is hard cos the data is scattered across 12 excel reports files for each agent associated with the recuirment process. So i am thinking of putting on a single database.
I have tried to design the database but not sure if i am in the right path. Keeping in mind that columns could change over time, how do you think i should approach the project?
E.g. the following is data record for the January, 2017 for recruiter AIME01
P.S. Just noticed the format of the input data and expected output is not been kept. Here is an image and look for the excel file please:
Recruit Action
------------------------------------------------------------------------------------------------------------------------------
Recruited Left
------------------------------------------------------------- -------------------------------------------------------------
DirectApplicant IndirectApplicant Displine ContractOver
50 20 5 2
25 10 1 1Explanation
So, the company does recruitment action for other companies (its clients). A recruit action is therefore getting a recruit (someone new) or the recruit leaving. For each action, there are details.
E.g. if a person is recruited to serve a company, did he directly apply to be an agent or he indirectly came (like recommended by others)
If he stops being an agent for a company, why? Was he fired due to lack of displine or cos his contract with the company is over?
Now the data is typically like this:
number of people. So for e.g. 50 agents were recruited after they applied directly or 5 agents left due to lack of displine.
===========
Now the key thing here is:
1. The structure has remained the same since 2006. But they add/remove/edit columns. For e.g. Instead of Recruited, Left, this year they will add a third Column called Suspended. Then under Suspended they will add two columns: PreviouslySuspsendedTimes FiredAsAresult
And they might do so next year. So the design must be very flexible.
2. The element of reporting is vital. Cos i need to see for e.g. How many new recruitment (e.g. 50+20=70) or how many direct applicants (50+25=75). That sort of thing.
This is all in the database design. I will likely use Casandra or Hadpoop for the database but I dont really care about that part now. I just want a flexible database design that will generate good reports.
Sample Report or expected output is
Recruit Action
------------------------------------------------------------------------------------------------------------------------------
Recruited Left
--------------------------------------------------------------------- ---------------------------------------------------
DirectApplicant IndirectApplicant SubTotal Displine ContractOver Subtotal
50 20 70 5 2 7
25 10 35 1 1 275 30 105 6 3 9
That is a basic report that is just descriptive.
Hope it makes sense.
The question now:
how do you design the database so say next year, they will able to modify it like this:
Recruit Action
----------------------------------------------------------------------------------------------------------------------------------------------------------
Recruited Left Activity
------------------------------------------------------------- ----------------------------------------------- -----------------------------------------
DirectApplicant IndirectApplicant Displine ContractOver RecommendedToFriends RecommendedToFamily
50 20 5 2 1 20
25 10 1 1 5 0I have tried to come up with a design that is flexible enough and it seems to work but i was never able to generate the needed report. My attempt revolved around having a dynamic database where I dont have direct columns:
agents
-------
agentid PK AI INT
agentnametransactiontype -- stores the first row labelled as NEW
----------------
transtypeid PK AI INT
transtypenametransactiondetail --stores Recommendation Or Direct
-----------------
transdetailid PK AI INT
transtypecode INT FK ----------to transactiontype table
transdetailnametransactioncolumns --stores Under18 Above18
------------------
columnid PK AI INT
transdetailcode INT FK --------to transactiondetails table
columnlabelenteries -------to store actual numbers/values
----------------
id PK AI INT
columncode INT FK ---------to transactioncolumns
agentcode INT FK --------to agents table
fordate date -------------------- 2017.01.01 for e.g.
actualvalue int ------------------- the number that was actually entered
agents
-------
agentid PK AI INT
agentnametransactiontype -- stores the first row labelled as NEW
----------------
transtypeid PK AI INT
transtypenametransactiondetail --stores Recommendation Or Direct
-----------------
transdetailid PK AI INT
transtypecode INT FK ----------to transactiontype table
transdetailnametransactioncolumns --stores Under18 Above18
------------------
columnid PK AI INT
transdetailcode INT FK --------to transactiondetails table
columnlabelenteries -------to store actual numbers/values
----------------
id PK AI INT
columncode INT FK ---------to transactioncolumns
agentcode INT FK --------to agents table
fordate date -------------------- 2017.01.01 for e.g.
actualvalue int ------------------- the number that was actually enteredBut the report does not give me the needed output (shown above)
The output is obviously in for each column vertically and i am lost on how to create the correct display output and map it.
1. Is my current approach good enough for the given excel format?
2. How would i possible display the output horizontally for each date just the excel format is for a given month and agent?
3. I will likely use .NET with Crystal Reports if it will make a difference but for now, i am not confident about my database design at all.
Viewing 0 posts
You must be logged in to reply to this topic. Login to reply