design to select combinations of multiple values

[Guest]

I work for a social services agency that brokers supports for people with
disabilities. These contractors are called "providers." I have a table in my
database for these providers called tblProviders. It currently contains
demographic information and is linked to an invoice table though the key
field [ProviderID]. What I want to do is create something that allows my
users to search for Providers with particular skills, e.g. "Spanish
speaking";"able to lift more than 100 pounds";"experience with Autism."

The qualification list is comprehensive witih at least a hundred defined
skills at this point. I want users to be able to query the db for multiple
skills, e.g. a provider who speaks Spanish AND has experience with autism AND
lives in a certain city. Ideally they would be able to choose these skills
via checkboxes or dropdowns. I have no idea how to go about designing the
tables, relationships, and queries to make this happen. Any direction and/or
resources would be greatly appreciated.

 

[Guest]

Whatever your structures, make sure you understand normalization. You have a
table of providers with assuming a ProviderID field as the primary key. You
also need these tables:
tblSkills (one record per skill)
SkillID autonumber primary key
Skill values like "Spanish speaking", "Knows ASL", ...

tblProviderSkills (1 record per provider per skill)
ProviderID
SkillID

You can then create queries based on these three tables searching for
matches of skills. If you need a provider to have 3 separate skills, the
query would need to return 3 records for a provider to determine if they have
all required skills.

 

[David F Cox]

The problem with defined skills is that there will be a tendency for the
system to need constant change as new requirements are discovered. Your
first decision has to be how many of the skills are to be defined, and which
skills, if any, require a freeform search capability. Your first decision is
unlikely to be perfect.

The defined skills can be catered for with dropdown lists, combo boxes,
check boxes and option groups. I would suggest that there is also a memo
field which covers "other skills" which can be searched by LIKE "*" & [enter
search text 1] & "*" and a table which records such search requests. In this
way you will be able to discover which skills are being searched for and be
able to tailor the system accordingly. If it were I, I would also enter the
defined skills into this memo field. So if the check box "K96G certified"
was ticked the string "K96G" would be appended to the memo field and could
be searched for either way.

Careful design and standard setting is the key to success for such
applications. Good luck.