Ugly Solutions Needs Help

Client has database of all licensed physicians in a large metro area.

Each physician is assigned to a Medical Advisory Board (MAB)

“MAB” field was designated as a single choice lookup. Currently the choices are North, South, Both. Null value is allowed. It assumes the physician has no MAB.

The client needs to add a 3rd Region ASAP — new value is “Central”

They are importing about 45000 new physicians that will be associated with Central.

About 1/3 of the new physicians will be associated with one or both existing MABs.

The other 2/3 of the new physicians will only be associated with Central.

There are 875,000 existing physicians — Some will need to be associated with Central in addition to their current MAB. Some will not be associated with Central but will continue to keep their current MAB.

1. The current “MAB” field with choices North, South, and Both can not change. (I can’t go into details why, but trust me, I would if I could. So I can’t touch this existing field, nor can I do any backend scripted data mapping or conversion).

2. Existing physicians may be assigned to Central in addition to their current “MAB”

3. Some physicians will only be associated with Central.

I’ve not had much time to think about this, so if all the ugly solutions I wrote up the one that was the least ugly.

I’m sure you clever folks will think I’d a better solution.

—— An Ugly Solution ——

Add a new Single Selection lookup files called AddOnMAB. Valid choices being: • Central Plus • Central Only • {NULL}

Use AddOnMAB in addition to current MAB field.

Here are the available responses, some logical and others illogical.

Logical Pairs 1. Central Plus ; North 2. Central Plus. ; South 3. Central Plus ; Both 4. Central Only ; NULL 5. {NULL} ; North 6. {NULL} ; South 7. {NULL} ; Both 8. {NULL} ; {NULL}

Illogical Pairs 9. Central Only ; North 10. Central Only ; South 11. Central Only ; Both 12. Central Plus ; {NULL}

Any better ideas??

I need to have a design concept by tomorrow. I just got assigned to it 39 min ago and have meeting abs class until 10pm.

Help & Much Thanks in Advance