É uma boa prática construir o maior número possível de chaves estrangeiras?

Outra abordagem é adicionar player_id à tabela de instâncias como uma chave estrangeira. Agora os jogadores podem consultar diretamente da tabela de instâncias. Consulta mais curta, mas mais uma coluna para uma chave estrangeira.

IMO, esta será uma abordagem ruim. Esta é uma espécie de referência circular.

Como você tem uma relação de um para muitos, a tabela de mapeamento será separada, masmorra, instância e jogador, cada um terá uma tabela mestre e uma tabela de mapeamento definindo sua relação um para muitos.

    CREATE TABLE instances (
    instance_id INT PRIMARY KEY
    ,instance VARCHAR(100) NOT NULL
    )

CREATE TABLE dungeons (
    dungeon_id INT PRIMARY KEY
    ,dungeon VARCHAR(100) NOT NULL
    )

CREATE TABLE player (
    playerid INT PRIMARY KEY
    ,player VARCHAR(100) NOT NULL
    )


CREATE TABLE dungeon_instance (
    dungeon_instance_id INT identity(1, 1)
    ,dungeon_id INT NOT NULL
    ,instance_id INT NOT NULL CONSTRAINT FK_instances FOREIGN KEY (instance_id) REFERENCES instances(instance_id)
    ,CONSTRAINT FK_dungeon FOREIGN KEY (dungeon_id) REFERENCES dungeons(dungeon_id)
    ,PRIMARY KEY (
        dungeon_id
        ,instance_id
        )
    )

CREATE TABLE player_dungeon (
    player_dungeon_id INT NOT NULL
    ,playerid INT NOT NULL
    ,dungeon_id INT NOT NULL
    ,CONSTRAINT FK_player FOREIGN KEY (playerid) REFERENCES player(playerid)
    ,CONSTRAINT FK_dungeon FOREIGN KEY (dungeon_id) REFERENCES dungeons(dungeon_id)
    ,PRIMARY KEY (
        playerid
        ,dungeon_id
        )
    )

se você tiver uma consulta muito frequente entre o jogador e a instância em que não há necessidade de tabela de masmorras na saída e o volume de dados é alto, você pode optar pela desnormalização.

A criação player_instance mapping tableou não depende do requisito acima.

Mas é desejável alcançar a Desnormalização viaindex view

Idealmente , as entidades não devem ter chaves estrangeiras e devem ser consideradas mestres . Assim player, dungeone instancesão mestres que possuem informações/atributos/campos relacionados às suas próprias características. Você pode ter uma tabela separada para Entidade-Relações . Então você teria player_dungeone dungeon_instance(ambos um para muitos conforme o PS). Aqui se você quiser saber instancespertencer a um player, você precisa juntar essas duas tabelas.

Agora vem o famoso debate: normalização vs desnormalização. A primeira abordagem é pelo livro e funcionaria. Mas é difícil dimensionar à medida que as tabelas crescem (apesar de ter índices adequados). Então você tenta desnormalizar a solução acima dependendo do volume de consultas que estão acontecendo nessas tabelas.

Um playerde cada instancepode ser acessado através da dungeontabela.

Como você especificou uma consulta aqui, estou assumindo que é uma consulta frequente (gargalo). Se houver muitas consultas desse tipo, você pode ter outra tabela de relacionamentos player_instance. Agora resolvemos o problema da latência, mas demos origem a um problema maior:

Consistência : Agora existem duas maneiras de encontrar a instancespertença a um arquivo player. Uma é diretamente por player_instancetabela, outra por junção player_dungeone dungeon_instance. Devemos sempre obter a mesma resposta dessas duas abordagens. Para garantir que agora precisamos fazer nossas gravações sofrerem. Como?

• Além de cada mapeamento de jogador-masmorra, além de entrar em nossa player_dungeonmesa, também precisamos inserir na player_instance tabela (para a qual temos que consultar dungeon_instancepara ver todas essas instâncias mapeadas para essa masmorra em particular).
• Além de um novo dungeon_instance, precisamos mapear todos os jogadores existentes para essa instância que mapeamos para essa masmorra em particular. Não soa bem, não é?

Ainda não terminamos. Todas essas operações devem ser atômicas (ou seja, em uma transação) ou então haverá inconsistência por um curto período de tempo.

Finalmente, precisamos fazer uma troca. Você quer tornar as leituras do seu banco de dados lentas ou as gravações sofrerem? Você precisa tornar seu sistema responsivo/disponível ou consistente? A decisão é sua.

Por favor, leia o teorema CAP para detalhes.