0

I have two tables:

Games(
    name,
    current_step, #foreign key to Steps table
    #other fields...
)

Steps(
    name,
    game_id, #foreign key to Games table
    #other fields...
)

In other words what I want to achieve is a Steps table where each step is linked to its game and in some way I want to keep track of current step. Which is the best design to do this? Is circular foreign key acceptable?

0

2 Answers 2

4

Don't put the step in the games table, it's unnecessary.

In your steps table, make a bit column like current_step, and add a unique filtered index on (game_id, current_step) WHERE current_step = 1.

This will prevent duplicates and prevent the circular reference and data integrity issues that are sure to ensue.

1
  • I did not thought about it because current step is an information I quite always need when I retrieve a game. However I think I'll use the design you suggested, thanks.
    – Sirion
    Apr 28, 2014 at 20:15
0

I would not use a circular reference to model your case. I would rather use a function to check if your current step belongs to the Steps table and add check constraint to the Games table on current_step field. A mock up is as below.

Games Table
-----------------
Games(
    name,
    current_step,
    #other fields...
)


Step Table
----------------
Steps(
    name,
    game_id, #foreign key to Games table
    #other fields...
)


checkStep Function
---------------------
function checkStep( @game_id ... )
returns ...
as
begin
    if exists (select * from Steps where game_id = @game_id)
        return 'True'
    else
        return 'False'
end


Check Constraint
----------------------
alter table games add constraint ck_current_step
check (checkStep(current_step)) = 'True')
3
  • Why do we need to have the current step in the games table to begin with?
    – JNK
    Apr 28, 2014 at 19:49
  • I guess the questioner intends to preserve the state of the game through current_step attribute. Just a guess... Apr 28, 2014 at 19:51
  • Yes, having current_step inside game table would be useful because when I retrieve a game I quite always need the current step, however I think that @JNK approach is the cleanest and probably I will use it. Thank you anyway.
    – Sirion
    Apr 28, 2014 at 20:13

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

Not the answer you're looking for? Browse other questions tagged or ask your own question.