Storing Variables of type List in a SQL Server Database

Question

Storing Variables of type List in a SQL Server Database

Navigation

#1 by (2 votes)

2

Good Night Personal,

I'm 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 a one more different skills, I wonder if you have a way to leave the new dynamic skills since they can have many or few (depends on each game), with this I would like to know if there is any way to save a list or a vector in Sql-Server database so that when a game with more or less skills was not required to be generating fields in my Game table. 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 was saving the abilities of each game that the user registered

sql sql-server c#

asked by anonymous 09.05.2018 / 03:42

1 answer

Redirect to new page when error 404 occurs using Laravel 5.6 Check if date is between two dates in the 30 min interval

score 2 · Accepted Answer

Solution 1:

You can have 4 tables:

Jogadores : Stores the player.

Jogos : Stores the games.

Habilidades : Stores all the skills of a game.

Relacao : It relates what skills a player has, which in turn relates to the game.

Example:

Advantages:

Simplequerytogettheresults.

Disadvantages:

Itisnotpossibletorelateaplayertoagame,withoutreportingaskillatleast.

Itisnotpossibletoreusetheskillregisterforothergames.

Solution2:

5tables...

Jogadores:Storestheplayer.

Jogos:Storesthegames.

Habilidades:Storesskills.

Relacao:Listswhichskillsandwhichgameaplayerhas.

Jogos_Habilidades:Listswhichskillsagamehas.Thistableonlyservestoloadwhichskillsareregisteredforagame.

Example:

Advantages:

You can use the same skill register in% with% games.

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

Disadvantages:

Query a little more complicated.

I hope you 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;