Results 1 to 11 of 11
  1. #1
    Join Date
    Jul 2013
    Posts
    4

    Building a new HR system, need some help

    Dear all,

    I am new to databases, I know the basics, but it ain't my profession. I was asked at work to design and build a small HR (human resources) system. Since my skills are limited, it will be based probably on Access, perhaps rapped with a simple .Net application, we'll see.

    I need your help with designing the DB, what looks very complicated (up to impossible) for me, might look simple and immediate to you. I am trying to build an ERD and can't figure out how.

    The DB should contain all the data regarding the company's employees and advisers, but mainly employees.

    For each employee I need the DB to save:

    basic data: name, ID number, D.O.B, gender, marital status, address, phone number, bank name, bank account number, email

    education: degree - BA/MA/PHD,... institution of education: name of college / university, specialization

    work history: previous employers, job description, inner ascription department in company, date of arrival, manager's name, subordinate employees

    employment conditions: wages, employment % (100%, 50%,...), insurance, #of holiday days left, pension fund, advanced study fund,...

    Inner ascription: Department, date of beginning work, manager's name, employees under management

    employment chronology: holiday without pay including dates, birth holiday including dates, work accidents including dates, active/non active

    Now for the tricky part. Some employees move after some time from one department to another, or they change a name (after marriage or divorce), or maybe the employment conditions change (better wages). I need to keep record of history for several parameters...

    I will also need to restrict the authorization for using the system for a limit number of employees, but I guess I can do that later in the raping layer.

    I don't know if I should put all this in one big table, or to divide it, if so, how ? I also don't know if I should use some kind of inheritance, making a person and then adviser and employee inherit some attributes ( I understand the theory, but don't know how to implement it in Access, but that's a later stage).

    I would appreciate your guidance in how to build an ERD of this complicated problem, I can always shove everything in one big table and make it like an Excel sheet, but that's not the idea here, I want something more efficient.

    Thank you !

  2. #2
    Join Date
    Feb 2004
    Location
    New Zealand
    Posts
    1,423
    Thats a BIG BIG job

    Did u ask uncle google

    Do konw what a PK is
    hope this help

    See clear as mud


    StePhan McKillen
    the aim is store once, not store multiple times
    Remember... Optimize 'til you die!
    Progaming environment:
    Access based on my own environment: DAO3.6/A97/A2000/A2003/A2007/A2010
    VB based on my own environment: vb6 sp5
    ASP based on my own environment: 5.6
    VB-NET based on my own environment started 2007
    SQL-2005 based on my own environment started 2008
    MYLE
    YOUR PASSWORD IS JUST LIKE YOUR TOOTHBRUSH DON'T SHARE IT.

  3. #3
    Join Date
    Jul 2013
    Posts
    4
    Yes, I know it's a BIG work. Just a clarification, I am not asking anyone here to sit and design me an ERD. I am looking for just some guiding lines, maybe suggestions for which tables to create, how to relate them, and most of all, how to deal with the history problem.

    I do know the basic stuff, like PK, foreign key, one to one, one to many,...I have designed basic and smaller DB's in the past, but nothing like this.

    I did look for ready DB's in google, didn't find any yet

  4. #4
    Join Date
    Jun 2003
    Location
    Toronto, Canada
    Posts
    5,516
    For ways of maintaining history, you may want to read about Slowly changing dimension - Wikipedia, the free encyclopedia
    ---
    "It does not work" is not a valid problem statement.

  5. #5
    Join Date
    Feb 2004
    Location
    New Zealand
    Posts
    1,423
    Hay here a idea some to ad
    Each emploper has a reporttoid which is the id or the that empid of the emploper table now u have created a flow chart of your company
    hope this help

    See clear as mud


    StePhan McKillen
    the aim is store once, not store multiple times
    Remember... Optimize 'til you die!
    Progaming environment:
    Access based on my own environment: DAO3.6/A97/A2000/A2003/A2007/A2010
    VB based on my own environment: vb6 sp5
    ASP based on my own environment: 5.6
    VB-NET based on my own environment started 2007
    SQL-2005 based on my own environment started 2008
    MYLE
    YOUR PASSWORD IS JUST LIKE YOUR TOOTHBRUSH DON'T SHARE IT.

  6. #6
    Join Date
    Sep 2009
    Location
    Ontario
    Posts
    1,057

  7. #7
    Join Date
    Feb 2004
    Location
    New Zealand
    Posts
    1,423
    Remember you pay for what u get.

    Broxi keep thinking and doing you will get there
    hope this help

    See clear as mud


    StePhan McKillen
    the aim is store once, not store multiple times
    Remember... Optimize 'til you die!
    Progaming environment:
    Access based on my own environment: DAO3.6/A97/A2000/A2003/A2007/A2010
    VB based on my own environment: vb6 sp5
    ASP based on my own environment: 5.6
    VB-NET based on my own environment started 2007
    SQL-2005 based on my own environment started 2008
    MYLE
    YOUR PASSWORD IS JUST LIKE YOUR TOOTHBRUSH DON'T SHARE IT.

  8. #8
    Join Date
    Jul 2013
    Posts
    4
    Thanks everyone, I got a small progress with it.

    Myle, not sure I understood your tip about a flow chart

  9. #9
    Join Date
    Feb 2004
    Location
    New Zealand
    Posts
    1,423
    If you have a reportingtoid in your emp table and that id point to the empid you have just created a org flow chart ie

    If shane had a id of 12
    You had a id of 15
    I had a id of 21

    If i had 15 in my reporttoid
    That mean i report to u
    And if you had 12 in your reporttoid u report to shane

    If someone had myid in there reportingid they would report to me

    Do u see it now
    Last edited by myle; 07-28-13 at 07:31.
    hope this help

    See clear as mud


    StePhan McKillen
    the aim is store once, not store multiple times
    Remember... Optimize 'til you die!
    Progaming environment:
    Access based on my own environment: DAO3.6/A97/A2000/A2003/A2007/A2010
    VB based on my own environment: vb6 sp5
    ASP based on my own environment: 5.6
    VB-NET based on my own environment started 2007
    SQL-2005 based on my own environment started 2008
    MYLE
    YOUR PASSWORD IS JUST LIKE YOUR TOOTHBRUSH DON'T SHARE IT.

  10. #10
    Join Date
    Feb 2012
    Posts
    76
    I suggest you start with the Database Design Resources sticky thread.

  11. #11
    Join Date
    Jul 2012
    Posts
    40
    Hi

    I have half-a-dozen Kick-Start Employee Data Models listed half-way down on the left-hand side of this page of my Database Answers Web Site :-
    http://www.databaseanswers.org/data_models/index.htm

    Take a look and see if they help you.

    Regards

    Barry

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •