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

[orange]

All I'm trying to get at is that -- if you have mainly textual data, free form and input by users, you can expect all sorts of typos, spelling mistakes and phraseology.


If you are setting up tables with standard descriptions, then you have a much better chance of getting consistency.

[Dal Jeanis]

Glad it's starting to take shape. Like orange says, if the basis for the data is text input, then your best case scenario is going to be quite fuzzy. You leave your initial input text there, and then alongside that, build your descriptors, and improve them over time.

I've never built an application like I discussed here, but it seems like a natural extension of the "Captcha" method of verifying text translations, and looks appropriate to your need. It has the virtue of being self-correcting over time, and as long as you keep the work-to-service ratio low for your users, they shouldn't object to the chance to tell the system if it's right or wrong.

[enquiries]

I know it's been a while - but I can say that I have hopefully successfully normalised the tables to 3NF - the tutorials were extremely helpful, and saved a lot of researching. I now have 24 tables, 9 of which are joining tables. As for every entity for the employee like qualifications, core skills, industry experience, previous jobs, trainings & developments, criminal convictions, legal proceedings etc I've created joining tables or lookup tables. I am now in the process of trying to create pre-defined/standardized values for positions, classifications, sub-classifications, qualifications etc.........the 2 main ones that I'm stressed about are: positions - classificaitions - sub-classifications (this defines the role and responsibilities etc) and the qualifications...............So i was wondering if anyone could direct me to an existing database that is relevant to HR information - because I'm wondering how to break it down whether to leave the fields as position (e.g. ACEO, CEO Managing Director) classifications (e.g. Accounting) sub-classifications (e.g. Accounts Payable, Accounts Receivable) or whether to break it down as levels (e.g. have a lookup table that has Management Level - ACEO, CEO, Managing Director, etc).......and than there's the private owned business with their own definitions........so just trying to find the BEST and EASIEST way to store this information where it will be easily searched for. Hope this makes sense. Appreciate any help

[Dal Jeanis]

In general, the rule is "know thy data". It depends on how you're going to use it.

I believe the most effective method would be to think in terms of "optional descriptors". You don't necessarily want every position or search to require an exact management level, etc. As such, you'd just attach the relevant ones to each position, just the way you attached them to the candidates. You'd probably use a "required, highly desired, preferable, nice-to-have" basis for selecting and sorting your returns. Required would give 200 points to a match, highly desireable 100 points, preferable 50 points, nice-to-have 25 points and so on. Add up all the matches to get a weighting for each candidate.

Also, there are many semi-equivalent titles out there, depending on industry. "Managing Partner" at one law firm might be equivalent to "COO" at a corporation or "Principal" at a CPA firm. On the other hand, the CIOs at two different firms might on the one hand be a C-level executive and on the other a middle manager. It's probably too much trouble to assign equivalencies, but if you really wanted to you could say that in any search for "CEO", that a "Principal" or "Partner" would be an 80% match or something, getting 160, 80, 40 and 20 respectively.

Depends on how much trouble you want to go to, and how much value you can give back for how much user effort.

[enquiries]

Thanks Dal Jeanis for your advice - I'm from and currently in the islands and I have great appreciation for the internet that allows me to access forums like this who have people like yourself who help others - appreciated I've compromised with the people I'm helping design the database for to keep it at a more general level for their searches and than they can refer to the hard copy of each person's details that show up in the searches for specific details - it will still be a huge step up from Excel that they're using now and improvement on efficient searching. So we're looking at trying to find a good balance and not trying to get it too specific e.g. Positions field will be: Board Members, C-level Management, Top-level management, middle-level management etc and than have add on extra positions accordingly, also included a lookup table - the people I'm working with are more than willing to go to the trouble of creating a lookup table for the sake of accuracy as there has to be a standard definition/format otherwise the search results will be compromised and there are about 500 records to be added (and will have info added on every now and than with new records). Anyways, looks like the designing phase is coming to an end and than will start implementing it in MS Access. Thanks again, Have a good day.

[enquiries]

Also I was just wondering for a huge database is it normal to have 24 tables? We are of course planning on using forms for the user to input the data (even if we use VBA code to store the inputted data into the relevant tables) but just wondering whether anyone else knows how to deal with a lot of tables?

[Dal Jeanis]

1) Your application is a fairly small one. Twenty-four tables is on the right scale. For contacts alone, a properly normalized database could have 10-12 tables, even without any specific functionality (task tracking, call tracking, security, etc).

2) Don't worry too much about how many tables you have. Think in terms of the user's workflow to add the information in sensible chunks. The key is this:

Design the workflow to make it easy on the user (and also on the programmer).

3) The code tables (from which the user will select the Positions, for example) will be pre-loaded, and the user will be able to select an appropriate one from a drop-down.

4) There is a standard way of using forms where the main form has a main record (for example, the candidate record) and different areas on the form are either really subforms showing information from a related table, limited by the main record, or are listboxes that return the results of a query against a table, limited by the main record. Manipulating the subform directly manipulates the underlying table. Manipulating any other control either invokes CODE to manipulate the underlying table, or pops up an interface form that is linked to that underlying table. (Depending on your design choices, the form can be modal and/or dialog, having to be used and dismissed before the main form can be accessed. When the popup is complete, it tells the main form to requery itself so that the data presentation is correct. Openargs is helpful for passing the required information.)

4) Adding a new candidate would be the first step. This step would walk through adding the person and any related tables (phone numbers, addresses, etc). After this step, the candidate exists in the database, and each of the remaining steps would be optional, pretty much in any order.

5A) Add education records.
5B) Add descriptors to the candidate.
5C) Add prior job records.

6) Methods for Adding descriptors at the candidate level. Generally, I'd show this as a combo box to select from, and a list box that shows what the candiate already has. The list box has source SQL that selects all the descriptors from the relation table that have the candidate's ID. The combo box is a list of all available descriptors. The user selects a descriptor, then clicks an "add to list" button, and the button's onclick event inserts a record to the relation table with that candidate's ID and that descriptor, then requeries the list box.

Once you have that basic functionality working for ONE form, you can use the same method for past-job decriptors, open-position descriptors, and so on.

7) Alternate method - you can use two listboxes, the right one with source SQL for what the candidate HAS already, the left one for all available descriptors that aren't in the right one. With this, you allow multiselect on either box, and have four buttons arranged vertically between them. The top one wouldn't generally be used in your application, so should be visible=no, but you should build the code anyway, so you have a solid base for cloning.
>> add all
> add
< remove
<< remove all
On a click on ADD, you would loop through the left listbox, find each selected item, and add a corresponding record to the relationship table, then requery both boxes. On a click on Delete, you'd loop through the right box, find each selected item, and delete the corresponding record from the relationship table, then requery both boxes.

Now, when you have too many descriptors for the left listbox to be manageable, you can add radiobuttons or checkboxes above that limit the descriptors to specific types. I use something similar to this in one of my training databases, for instance to be able to quickly select the correct staff members to assign to a class.

That's some design methods. When you've thought about it, and mocked up some flowcharts and layouts for yourself, feel free to post your questions on the user interface forum for feedback.

[orange]

Further to Dal's advice, I would say that the number of tables (24 vs 34) is not an issue --provided that your tables have been normalized and make sense to you and the people who know the business issue/opportunity. You may have done this already, but if not, write a 3-5 line description of what each table represents. Sometimes, having to put info on "paper so to speak", forces you to identify what/why one thing is different than another. It's also a good way to get various people, who claim to know the business, to agree/refine/accept definitions.

If you have your subjects/entities identified and related, you could run some test scenarios against the model. I recommend you get some of the user community/business people to work through the scenarios and the model. Any glitches should be identified and reconciled --is it the model? Or is it the scenario/data? Reconcile every glitch. Sounds like you have it under control. Perhaps you could show us your model/ERD.
Make sure your tables and relationships support the business before getting too involved in Access specifics.

There is a free video tutorial on the 2 listbox set up that Dal mentioned at this link. It was done in Access 2003, but it's the underlying design/concept that
is important.
Good luck with your project.

[Dal Jeanis]

Awesome pointer to that tutorial, Orange.

[enquiries]

Thanks for all the pointers Dal and Orange I can send you the simple ER model I drew up (I didn't include attributes or whether it was a weak entity and so forth, just the entities and their relations) and the tables that have been normalized if you send me your email accounts (due to confidentiality).

Appreciate both your time and help.