Doubt in self-relational table modeling

Asked

Viewed 40 times

0

I need to make a system where the business rule is this:

  • One card must duel against another card
  • The same duel between two equal cards can happen more than once
  • Each letter should contain only two images of itself, one for the front and one for the back
  • The system must display a rank, counting how many matches all cards won and lost.

Thinking of solving the problem I came to the conclusion of creating a chart table that is self-relative, as in the diagram below:

[![insert image description here][1][1]

Where fk_carta_oponente refers to the id_carta and keeps the ID of the opposing card.

As each card needs to have two images, if I created the fields for these images within the table above, every time there was a new duel I would have to add again the same values of the images in the fields.To solve this I created another table like this:

[![insert image description here][2][2]

But the countryside nome that’s on the table carta would keep repeating the same way. In which table would I have to put this field nome so he wouldn’t keep repeating himself every time there was a new duel between the cards ? Or if I’m going the wrong way, what else better way to solve this problem ?

sql-server modeling sql-server-express

1 answer

1

Bringing to a deferential abstraction, which in my head would be more organized.

  • Letter should keep the letter data.

cards(id, name, img_front, img_coast)

  • Duel would mention the relationship you proposed.

duel(id, id_opnente_1, id_openente_2, id_winner)

  • The ranking would be mounted with something like.

Victory Turn:

SELECT
   C.id,
   COUNT(D.id_vencedor) AS Vitorias 
FROM duelos D
INNER JOIN cartas C 
ON C.id = id_vencedor
GROUP BY
  C.id

View for the defeats:

SELECT
   C.id,
   COUNT(C.id) AS Derrotas 
FROM duelos D
INNER JOIN cartas C 
ON C.id IN (id_openente_1, id_openente_2) 
AND C.id != id_vencedor 
GROUP BY 
  C.id
  • Then join the two views r calculate the balance of the card with Wins - Defeats.

I think it would be something like this in my opinion.

  • I also thought about this possibility, duelo would come out and then arise two more tables, now I ask you, which data would be saved in the auxiliary table and the other table ?

  • It’s actually not two tables. They are two visions that only consolidate separately, victories and defeats, and a third vision that I did not mention there, that would represent the ranking. But as a modeling scope, it would be cards and duels.

  • I edited the publication, explaining better what I meant,.

  • I saw there that you created an approach similar to the one I talked about, starting from there, no longer what to model at the level of data, no more native tables of mass of data to be created, everything is right. All you have to do is assemble accurate views, Wins Defeats and Rankink, or all together showing the balance of the cards.

  • But the relationship will get N x N, it would be wrong to proceed so right ?

  • 1

    In my opinion no, a letter/delegation can duel several times with other letters/delegations, hence it is natural that if the relationship can be repeated, this is N. A relationship N x N is not wrong if it is necessary.

Show 1 more comment

Browser other questions tagged sql-server modeling sql-server-express

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