Question about data design, circular foreign keys

I'm simplifying this a bit but we have a User table and a TestResult table in a large existing sql2008 database used by a large mature .net project. The users are given many tests and these are accessed a lot so to speed things up the User table contains a CurrentTestResult and a PendingTestResult for the last test and a test in progress respectively. Also you can't just look at the date to find the the current test as sometimes a test may be invalidated, same with pending.

TestResult of course, contains a foreign key for UserID and User has 2 foreign keys pointing to TestResult. It's been this way a long time, without too much problem. Occasionally som开发者_JAVA百科ehow a test gets pinned to a user even though it's not their test, so clearly it's not perfect, and I need to track down the hole anyway.

We are moving to a disconnected model and they are telling me we must remove all circular keys. They don't want to temporarily drop and re-add the keys because they won't know what to do if that process fails. Not being familiar with the process I'm not sure if that makes sense, but that's a question for later after I figure out the cons of me fixing the references.

I can see several solutions to this problem for me, all of which have their drawbacks.

A) The simplest of which is just dropping the 2 foreign keys. I don't have a huge problem with this because the FK constraint is only half the picture, unless it's linked to the right user it's still invalid. I have to weigh the risk vs code changes for keeping the keys.

B) I could add a crossreference table to hold the link to current and pending. I think this will give me basically the same protection I have now, with only slight performance difference. I think the circular key problem goes away because they can insert the xref last. I will have a ton of code changes.

C) I could add 2 columns to TestResults and flag the current and pending records. The problem here is now I have to make sure only 1 flag is set per user and I now have to do an index look up every time I access my data. So I've lost on protection, performance and I still have a ton of code changes.

D)?

I'm sure this is a fairly common pattern, is there a "correct" solution?

---

Your structure isn't clear to me, but you might be able to hide some or all of the changes behind an updatable view.

I have a feeling option B is going to be your best bet. There you're essentially talking about a table like this, right? (Air code)

create table xref (
    user_id integer not null references users (user_id),
    current_test_result whatever not null references tests (test_id),
    pending_test_result whatever references tests (test_id),
    primary key (Hmmmmm)
);

Does pending test result have to be nullable? I guess that doesn't matter much--if it has to be nullable, it must already be nullable in the existing table.

---

Are you sure that option A is actually going to be a performance issue? If you have an index on your test table which contains the user FK and then something descending chronological - either an IDENTITY column or a test timestamp then this index will be very helpful when you do a select TOP 2 from the TEST table where user_id = X. You don't have to worry about your circular foreign keys getting out of whack.