Storage of List-like variables in an SQL-Server database

Asked

Viewed 541 times

2

Good Evening Everyone,

I am developing an application that makes a relationship between a Player and one or more games(online games), the problem is that a game can have one more different skills, I would like to know if there is a way to leave the record of new dynamic skills, because the same may have many or few (depends on each game), so I would like to know if you have any way to save a list or an array in Sql-databaseServer so that when a game with more or less skills was registered did not need to keep generating fields in my table Game. In the case I would generate a single field in the table that would be this list and that could be called skills for example and in it would save the skills of each game that the user registers

c# sql database sql-server

  • Suggestion, you would have to have a table with COD_H and SKILL, and another table with fields COD_D, COD_H, Cod_player, Qtd. Then, at the time of requesting show the all data related to a single player.

  • From a read on this reply I don’t know if it would be best for the case, but it’s something very interesting.

  • @RORSCHACH by his logic then I would have a table only with the skills codes, but the problem is that it would be kind of invalid I create a table only for the skills because maybe I never use the same, because each game has its specific skill, for example in Counter Strike I would register the player’s patent and only, however if the player played League of Legends I would have to register his Link which is equivalent to patent, the level and the position he plays for example,

  • @Barbetta I didn’t think about the possibility of working with a nonrelational bank because I and the members of my group have no knowledge of it

  • @Giovannipreivatti Have you read the question? there is an alternative with relational bank, I do not think it is ideal for your case, however worth reading. As for your question, I think it would be better if you add the modelling done, so the community can help you better about it.

  • @Barbetta thanks, I’ll do it

Show 1 more comment

1 answer

2


Solution 1:

You can have 4 tables:

Jogadores : Stores the player.

Jogos : Stores the games.

Habilidades: Stores all the skills of a game.

Relacao : Lists what abilities a player has, these in turn relate to the game.

Example:

inserir a descrição da imagem aqui

Perks:

  1. Simple query to get results.

Disadvantages:

  1. It is not possible to relate a player to a game without informing a skill at least.
  2. Unable to reuse skill registration for other games.

Solution 2:

5 tables...

Jogadores : Stores the player.

Jogos : Stores the games.

Habilidades: Stores the skills.

Relacao : Lists which skills and which game a player has.

Jogos_Habilidades : Lists which skills have a game. This table only serves to load which skills are registered for a game.

Example:

inserir a descrição da imagem aqui

Perks:

  1. You can use the same skill record in n games.

  2. It is possible to relate a player to a game without reporting any skill.

Disadvantages:

  1. A little more complicated query.

I hope it will at least give you an idea of how you can do it. I, would go for solution 2.

Solution 2 Template Script:

CREATE SCHEMA [public];

CREATE TABLE [public].habilidades ( 
    id                   int NOT NULL   IDENTITY,
    nome                 varchar(200)    ,
    CONSTRAINT Pk_habilidades PRIMARY KEY ( id )
 );

CREATE TABLE [public].jogadores ( 
    id                   int NOT NULL   IDENTITY,
    nome                 varchar(200)    ,
    CONSTRAINT Pk_jogador PRIMARY KEY ( id )
 );

CREATE TABLE [public].jogos ( 
    id                   int NOT NULL   IDENTITY,
    nome                 varchar(200)    ,
    CONSTRAINT Pk_jogos PRIMARY KEY ( id )
 );

CREATE TABLE [public].jogos_habilidades ( 
    jogo                 int NOT NULL   ,
    habilidade           int NOT NULL   ,
    CONSTRAINT pk_jogos_habilidades PRIMARY KEY ( jogo, habilidade )
 );

CREATE INDEX idx_jogos_habilidades ON [public].jogos_habilidades ( habilidade );

CREATE INDEX idx_jogos_habilidades ON [public].jogos_habilidades ( jogo );

CREATE TABLE [public].relacao ( 
    jogador              int NOT NULL   ,
    jogo                 int NOT NULL   ,
    habilidade           int NOT NULL   ,
    valor                varchar(100)    ,
    CONSTRAINT Pk_relacao PRIMARY KEY ( jogador, habilidade, jogo )
 );

CREATE INDEX idx_relacao ON [public].relacao ( jogador );

CREATE INDEX idx_relacao_0 ON [public].relacao ( habilidade );

CREATE INDEX idx_relacao_1 ON [public].relacao ( jogo );

ALTER TABLE [public].jogos_habilidades ADD CONSTRAINT fk_jogos_habilidades FOREIGN KEY ( habilidade ) REFERENCES [public].habilidades( id ) ON DELETE CASCADE ON UPDATE CASCADE;

ALTER TABLE [public].jogos_habilidades ADD CONSTRAINT fk_jogos_habilidades_jogos FOREIGN KEY ( jogo ) REFERENCES [public].jogos( id ) ON DELETE CASCADE ON UPDATE CASCADE;

ALTER TABLE [public].relacao ADD CONSTRAINT fk_relacao_jogadores FOREIGN KEY ( jogador ) REFERENCES [public].jogadores( id ) ON DELETE CASCADE ON UPDATE CASCADE;

ALTER TABLE [public].relacao ADD CONSTRAINT fk_relacao_habilidades FOREIGN KEY ( habilidade ) REFERENCES [public].habilidades( id ) ON DELETE CASCADE ON UPDATE CASCADE;

ALTER TABLE [public].relacao ADD CONSTRAINT fk_relacao_jogos FOREIGN KEY ( jogo ) REFERENCES [public].jogos( id ) ON DELETE CASCADE ON UPDATE CASCADE;

Browser other questions tagged c# sql database sql-server

You are not signed in. Login or sign up in order to post.