Database Design Ideas

[Heythere82]

Hello everyone,

I am trying to design an Access Database to track/store employee history. Right now, we have 2,000+ employee folders that have to be retrieved and refiled each time a document has to be added.

I mentioned this task to a database manger from another department and he didnt think access would be able to do this.

I'm just trying to figure out if this is possible and what it should look like.



An overview of what it has to do:

- Around 1,000 active employees at a given time, each with an employment history folder

-Each employee would have to show a current disciplinary status and reason (probation, suspended until 3/17/16,etc)

-Each 'folder' would need to be able to hold attendance documentation, disciplinary/appeal documentation,

-Administrators will need to be able to easily add this documentation to folders. They must be able to view all the documentation specific to an employee at the same time. (a dashboard? for each employee)



The way that I'm envisioning it now is a table for each type of documentation (Attendance Doc table, Disiplinary Doc Table, Appeal Doc table, etc.)

Users would add new docs through forms.

My first issue is figuring out how to show multiple docs related to a specific employeeID on the same form. An employee might have anywhere from 2 to 40+ docs in the file.

Secondly, we currently print out email correspondence that we have with each employee. The only solution I can think of is to allow a Note sections on each form to copy email text into. Im concerned because this would remove all formatting and make it very difficult to navigate.


So is this possible or is there a better alternative?

 

[Minty]

You wouldn't have a a table for each document type. You would have a document type field which would allow you to have a list of documents with types that could be easily added to. Think vertically for data (Database layouts) not horizontally(Excel thinking).

Anything you are currently printing out could be printed to PDF, and stored in a structure that is easily searched and retrieved.

Better still, moving the idea forwards, generate the documentation from the database, and you may not have to store it as the important data would already be stored and available.

 

[Heythere82]

Hi Minty thanks for the reply!

The reason I thought it had to be a Table for Doctype was because I couldnt figure out a unique key that would make sense for the table youre referring to.

Just to be sure, are you talking about a table with all employees showing a doctype field for each employee? This might pose a problem because:

1) Since each employee could have several documents of the same type (for different days) I would have to also have some sort of docID as well, right?


2) Because I am unsure how many docs each employee would have, I could not predetermine how many fields to add to this table. If i had to also have a docID, I would be essentially doubling all fields in the table.

3)If I only have docType and docID, where would the specific information contained in the doc be stored?




I dont think I did a good job of explaining the layout.

So lets say I have an attendance form. When someone is late to work, an admin will fill out the attendance form with information regarding that specific occurrence(employee name, late time, reason, day, etc).


There will be several document forms that will work in the same way as above. (a disciplinary form, an appeal form, etc.)

At some point, an Admin would need to be able to view all docs that one employee has in the record as well as the specific information that was imputed in each form.

 

[Minty]

You need to step back and create a simple list of the various items you want to store data about and the type of data they contain. don't worry about the detail, keep it simple. To use the database jargon - these are your entities.

Now draw out (on paper with a pen ;0 ) the relationship between these entities. It should be simple, no multiple round robin links. Send us that drawing and let us have a look.

 

[CJ_London]

adding to Mintys comments about what to do, what Access can do (among other things):

• have a query which interrogates a windows folder and lists everything in that folder
• that query can also be filtered to show files for one employee and/or doctype etc
• have simple code to open a document if query is a recordsource to a form or a rowsource to a list or combobox
• have code to create new folders
• copy files
• email files
• have code which collects files from an email folder or another folder and stores them in the folder with correct naming convention
• interrogate individual files to identify and/or extract certain information

Note: personally (as indicated above) I would not store documents in the database but in a windows folder and use Access for file management/retrieval etc

to make searching easier you would need an enforced file naming convention - file type is not important

might be something like

empcode_empname_docType_date_enumerator

use underscores to separate each component. The naming convention should be designed in a way for easy sorting and definition of hierarchy e.g. docType would be perhaps include 'COE' (contract of Employment), 'SR' (Salary Review), 'WR' (Written warning) etc.

with the above you do not necessarily need one folder per employee but probably better to do so