Creating a Database for Records of Members & potential Members of an Organization

[enquiries]

The title doesn't exactly cover the complexity of this task - I'm in the process of creating a database that records all members who have applied to this organization and current members of the organization. This database has to include their skill set, previous jobs, qualifications both professional and courses taken from which institute, whether they were appointed in the the organization before etc. It has to allow for efficient searches for appropriate candidates (based on qualifications etc) who are already in the database if a job should arise, searches to notify when an employee's appointment is to end etc. Currently using Microsoft Excel and there are over 500 + records. I'm in the beginning stages of creating an ERD and going thru normalization - my problem is not all attributes can be single valued.......example they want a field that explains the responsibilities held in previous jobs or a field that explains why an employee was dismissed - (it's easy for data that is pre-defined or single value if you know what I mean)......I am wondering if MS Access would be the best choice for this or is there a way I can mix it up? Any advice or opinions much appreciated.

This is my first real database that I'm creating so a little inexperienced so appreciate any advice.



Thanks for any help

[Dal Jeanis]

Access is highly appropriate. If Excel was doing it and no longer is working, then Access is the obvious choice.

In the case of reason for leaving, you'll create a table with general codes, and then give a text place where the user can enter more notes. You'll have to review the data to determine how many standard values (laid off, fired, quit for other job, personal reasons, went back to school, moved, etc) are needed.

If it's multi valued or fuzzy, then it's not really a "field". It's either written notes, or a separate table. Unless there are specific predefined values for previous responsibilities, then it's a text or memo field. If there are specific predefined values - supervisory, auditing, budget, team lead, project lead - then it can be a separate table that links the responsibility to the candidate's job instance where that responsibility was held.

Head over to MVP Roger Carlson's site at rogersaccesslibrary.com and review his tutorials on database design and application design. A couple of hours of simple practice will save you dozens of hours in rework later. Then come back here and ask specific questions about the real-world ambiguities you're trying to resolve.

[June7]

This is data. Access is a tool for managing data.

Need to understand relational database principles, Access functionality, programming concepts, and macro or VBA code.

The fact you know what an ERD and normalization are is a good sign.

It looks like quite a bit of the data could be narrative in nature. You can try to standardize some, such as responsibilities: Supervisory, Crew Lead, Foreman; reason left job: dismissed, quit, promotion. Then have comment field for more info.

[enquiries]

Thanks for that, will go over the tutorials. And yes, revising database principles & working with someone who is good with macros and VBA code. Will update on any questions after. Thanks again.

[orange]

You may get some ideas from this link.

[enquiries]

Thanks, but the page is outdated & doesn't link to anything. I've gotten some of the tables, still working on putting it in 1NF which is why I was questioning whether Access would be the perfect software for it, as I'm finding it hard to normalize the data. E.g.: Employment History Table:
ID
Position Title
Employer
Responsibilities *
Date Started
Date Ended
Level (management or not)

The way I am organising the data is also based on the searches the organization want to do. They want to be able to search the records and based on previous employment history & training, courses etc be able to make recommendations for vacancies etc so for a staff or potential staff can have multiple previous employments.......the responsibilities field however I can't find a way to standardize, I'm not able to pre-define it as it is too wide a range and based on the position held, the employer etc........not able to remove this field as well as it is necessary for searches and recommendations and can not find a way to break it down into another table?

Any suggestions on maybe mixing it up? Or maybe creating exceptions?

Thanks for any help in advanced and I'm happy to read through any links or info you may think will be beneficial.

[orange]

I just tried the link and it is working fine. There are other conceptual model links on the page . The models are meant to provide some ideas/concepts.
I attached a screen capture of the TopLevel model, but there are links on the page to the others.

[enquiries]

Thanks for that, something with filter will have to sort out............I guess my question is, what happens when you know you have more than one field in your different tables that don't have atomic values and can't be standardized? e.g. responsibilities in previous employment history.......... or description of previous convictions etc.........I guess I can generalize it according to the common criteria's that are going to be searched on but that won't be entirely accurate.......Any suggestions please?

Thanks for any advice, much appreciated.

[orange]

...that don't have atomic values and can't be standardized...I guess I can generalize it according to the common criteria's that are going to be searched on but that won't be entirely accurate..

Take a look at the data value you have for such fields. Make a separate table called a LOOKUP table with a strucutre along these lines:

tblResponsibilities_Lkup
ResponsibilityID PK
ResponsibilityDesc

with values such as

01 Supervision
02 ProjectManagement
03 Programming
04 Analysis

but these will depend on the values you find in your data. Make them as mutually exclusive as you can.

As for convictions, similarly, tblConvictions_Lkup
tblConvictions_Lkup
ConvictionID PK
ConvictionDescription

01 Murder
02 Fraud ...... whatever is evident from your data

You can separate all convictions, sort them, remove duplicates, then create a lookup table.
You can google and may find a list
https://www.gov.uk/government/public...l-record-check but you can probably get a more focused list based on your jurisdiction.

[enquiries]

Maybe a different schema design instead of normalization (currently reading up on star schema) or are there some sort of exceptions that I can make?

[enquiries]

^^ sorry posted the above before seeing your reply. I'll do that, Thank you very much for your help.

[Dal Jeanis]

enquiries - It's not about normalization. It's about data conceptualization.

If your data is basically made up of text documents and notes, then you need to think in terms of key word searches.

Put the notes in a note table, linked by key to the candidate, and it's normalized. Done.

You can also create a totally normalized table for job descriptors or candidate descriptors. A word that describes a job ("manager", "supervisor", "accounting") can be attached by key to that job in the job descriptor table. A word that describes the candidate ("manager", "programmer") can be attached by key to that candidate in the candidate descriptor table.

You can integrate an easy ongoing way to crowdsource the descriptors into the workflow - when someone does a search for a particular type of candidate, you can tentatively assign a descriptor to each person (or job) that occurs in the result set. If the descriptor already exists in the table, then you leave it alone. If it doesn't, you add a new record for that descriptor, and set its "Certainty" field to zero.

The user can confirm, deny or ignore the descriptor. For the confirmed ones, you increment the "Certainty" value, up to some maximum. For the rejected ones, you decrement the "Certainty" value, down to some negative number minimum.

That way, over time you can order your search returns on the combined certainty for the various requested search terms.

[enquiries]

Thanks Dal Jeanis, that helps

Just to paraphrase what you're saying and making sure I got it right, you're saying:
In the Responsbility descriptors table have a certainty field that is type int and has a set value range say -5 lowest to +5 highest that is incremented or decremented based on whether the user “confirms, denies or ignores” the descriptor, so we can organise and return the search results accordingly to the most accurate/relative descriptor?

Also think I was too stuck on trying to make it atomic but had I moved onto relationships would've seen to break it down into the lookup table lol - for normalisation "can't always move smoothly from one step to the next"

Thanks again for advice & help
Much appreciated.

[orange]

enquiries,

Can you give us some sample data so we can get some feel for the type of info you are working with? If it's confidential or private, perhaps you can change the names to Porky Pig, Daffy Duck, John Doe etc.
It's difficult to guess what kind of data you have, what sort of questions you expect to ask of the data etc.

If it's really free text, or mostly free text, you may need synonyms, thesaurus capability , spell check-- but we won't know with some sample data.
My understanding was that you were designing/building this database.

Good luck with your project.

[enquiries]

Thanks orange and yes it is confidential & private but will try and get it to the "best state" I can and than will create a scenario and put up the tables and give some sample data. All of the advice given here has been a huge help already and it's beginning to take form.
So thanks again.