I've got a request for one of my Gemini sites.

We have custom data table (a category look up table).

In the middle of a project, the list of categories change.  Old issues still need to refer to the categories they were created with and are being worked on (issue resolution may ping for quite some time - may be active for months).  New issues must be created using new custom data table.   The project is continuous, so that creating a new project - a "before" and "after" custom data LUT change is not optimal.   Adding new items to the single table is not an option (the range of choices would confuse people during issue creation, and how would you mark "old" items and prevent them being selected?).  Making a new table (and re-naming the old) would modify issues in progress (and archived issues) in undesireable ways.

It seems to me a reasonable (albeit undesireable) course of action is to make copies of all the affected projects (and there are a fair number of them), and modify the security settings on the old ones so no new issues can be created.

A better alternative would be to create a view for this look up table, and select table such that if issue exists, and was created before date, use old table, else use new table....

I'm not much of an SQL programmer (although I can punch my way out of some paper bags) - does this last approach sound reasonable to try?  Any shortcomings I should consider?

Since I can't just "select" this from a view - I need issue instance information - what SPROCs would I want to look at?  (Is this even a reasonable and possible thing to try to do?)

Your input appreciated.

Regards,

Yarko