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?

  • Why do you need the relation to go both ways? – JNK Apr 28 '14 at 19:36
  • Because, given a game, only one step at a time is the current one. – Sirion Apr 28 '14 at 19:40
  • Then why not just put it in the STEPs table and not in games? – JNK Apr 28 '14 at 19:46
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.

  • 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 '14 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')
  • Why do we need to have the current step in the games table to begin with? – JNK Apr 28 '14 at 19:49
  • I guess the questioner intends to preserve the state of the game through current_step attribute. Just a guess... – user353gre3 Apr 28 '14 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 '14 at 20:13

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

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