How would you reconcile lookup table with conflicting requirements?

[Banana]

I've more or less decided on a solution, but wanted to have other pair of eyeball just in case I've gone insane.

Suppose that we have several different funders, and they want us to collect demographic & statistics. No problem!

Further suppose that our users want to report something about a person once and have the common information apply forward to specific reporting requirements of each funders. Erm, no problem?

Still, suppose that each funder had their own list which isn't identical to other.

Let us use ethnicity for example.

Suppose one want us to report ethnicity using one of those value:

Caucasian
African-American
Alaskan Native
Native American
Asian
Pacific Islanders
Hispanic

while another wants this:

Caucasian (Not of Hispanic Origin)
Caucasian (Of Hispanic Origin)
Black
Native Americans
Asian/Pacific Islanders

To be clear, there are already flags in place for 'Others', 'Unknown', and so that is not a problem.

Given the users' requirement that they want to select from one list and have database figure out how this value will apply to a particular report, my solution was to implement only the most narrow definition, while querying for broader definition using something like "If it's that value, it's like this value, so use this value for this report." Brain-dead to set up but hell to maintain (e.g. if funder decided to modify their list, I have to manually redefine the whole thing)

Therefore the list shown to my users would be like:

Caucasian (Not of Hispanic Origin)  'May resolve to 'Caucasian'
Caucasian (Of Hispanic Origin)  'May resolve to Hispanic'
Africian-American  'May resolve to Black
Alaskan Native   'May resolve to Native American
Native American 'No resolution necessary
Hispanic  'May resolve to Caucasian of Hispanic Origin
Asian     'May resolve to Asian/Pacific Islanders
Pacific Islanders   'Ditto

I've considered doing a set (e.g. Asian + Hispanic from a series of checkbox or multi-select listbox), but decided that this may violate the requirement of "just one selection'n'forget it" imposed by the users and make it even more complicated than the previous idea.

Anyone else has a suggestion or opinions on how you would implement this or maybe share if you've had similar requirement and how you implemented this?

 

[FoFa]

Well you could go for the most detail during data entry, then use a cross ref. table to convert one or more to what your clients want. As one way

 

[The_Doc_Man]

First, take your biggest list to all possible ethnicities i.e. the one with the most different choices.

Second, for all other similar lists, determine (where possible) the way that these would map. It might occur that these other lists would include a sub-ethnicity with respect to the first list. I.e. first list says "Native American"; second list says "Navajo", "Cherokee", "Choctaw", (i.e. recognized Native American nations). OK, so here you have to add the finer gradations to the table.

Eventually you will come up with a list of entries that look like this

<agency 1, code 1, "Caucasian">
<agency 1, code 2, "Hispanic">
<agency 1, code 3, "African-American">
...
<agency 1, code 56, "Native American">
<agency 1, code 57, "Native American">
...
<agency 2, code 1, "Caucasian Non-Hispanic">
<agency 2, code 2, "Caucasian Hispanic">
<agency 2, code 3, "Negro">
...
<agency 2, code 56, "Choctaw">
<agency 2, code 57, "Cherokee">

OK, now what you need to do is drive both the drop-down and the reports to know which funding agency (first number of the above list) is in play. Look up "cascading combo boxes" to make the form show the right choices. Then store the code number according to the funding agency/agent. Then on the reports, you have to also know to which agency that report is going.

The key is to subdivide all the possible categories and make an agency/ethnicity mapper.

Now you only have one set of codes for ethnicity, and they always are crafted to mean the same ethnicity. But you retain the ability to change the name you apply to that code.

If I have misunderstood this, then whoops, my bad! One thought that does occur, though, is to have a "pure" list of ethnicities (all possible names and the codes to be associated with them) and a second list of funding agencies and the codes that they want to see. But you still would need to reconcile the "pure" list to the funder lists if you did that.

 

[Banana]

Hmm, looks like the "most details" list is the solution- good to know as I was wondering if there was another way to implement this.

One thing I probably didn't make it clear enough was that there is no guarantee that a report for ethnicity is exclusively for one funder; it's possible that one same person will be reported to different funders at same time, and with the requirement of selecting only one value, details does seems to be logical.

I also considered the "pure" list, but this seems to me just another layer of complexity (as we're perfectly content with either list for our internal reporting).


Thanks for the reassurance.

 

[datAdrenaline]

...hmmm...

Just a brief read so far ... but upon initial impression I would create a column in your ethnicities table ... which, by the way would have ALL the descriptions in it. In that new column it would be a Foreign Key to a new table ... tblEtnicityGroup .... so basically you will group your ethnicities ... then in the Funder table you would add a new field to it as well ... a FK to tblEthnicityGroup ... so as you set up a Funder you define the enthnicity group you want to select from and adjust your combo via code accordingly.