0
\$\begingroup\$

So there's another question about storing information about a trading card game, but in my situation I have to store not only raw tournament information but a lot of statistics I've extracted from it:

Web Scraper > SQL Database

Tournaments

  • Format (different collections of cards available to use)
  • Competition Type (is it a knock-out tournament?)
  • Date it took place

Decks

  • Cards in them (each deck has two groups of cards: mainboard and sideboard)
  • Tournament it happened in
  • Wins and losses

SQL Database > Data Analysis > SQL Database

Archetypes (group of similar decks, also referred to as clusters)

  • Cards that show up very frequently throughout the archetype
  • Significant matchups against other archetypes, aka decks that it usually wins or loses against
  • The deck that is most representative of the whole archetype
  • Similar archetypes

Cards

  • Archetypes it's most commonly in
  • How many decks, regardless of archetype, use it
  • Cards it commonly co-occurs with

The main difficulty of my database model for this is that there are numerous traps of many-to-many relationships that I needed to avoid. Without further ado, here's the code:

CREATE TABLE tournaments (
    id SERIAL PRIMARY KEY,
    format VARCHAR,
    gametype VARCHAR,
    date DATE
);

CREATE TABLE decks (
    id SERIAL PRIMARY KEY,
    tournament INTEGER REFERENCES tournaments(id) ON DELETE CASCADE,
    mainboard VARCHAR[],
    sideboard VARCHAR[],
);

CREATE TABLE games (
    winner INTEGER REFERENCES decks(id) ON DELETE CASCADE,
    loser INTEGER REFERENCES decks(id) ON DELETE CASCADE,
);

CREATE TABLE clusters (
    id SERIAL PRIMARY KEY,
    mainboard VARCHAR[],
    sideboard VARCHAR[],
    highlights VARCHAR[],
    size INTEGER
);

CREATE TABLE significantmatchups (
    winner INTEGER REFERENCES clusters(id) ON DELETE CASCADE,
    loser INTEGER REFERENCES clusters(id) ON DELETE CASCADE,
    winrate DECIMAL
);

CREATE TABLE similarclusters (
    clustera INTEGER REFERENCES clusters(id) ON DELETE CASCADE,
    clusterb INTEGER REFERENCES clusters(id) ON DELETE CASCADE,
    distance INTEGER
);

CREATE TABLE highlightcards (
    cluster INTEGER REFERENCES clusters(id) ON DELETE CASCADE,
    card INTEGER REFERENCES cards(id) ON DELETE CASCADE,
    frequency DECIMAL
);

CREATE TABLE cards (
    id SERIAL PRIMARY KEY,
    name VARCHAR PRIMARY KEY,
    decks INTEGER,
    usedin INTEGER[]
);

CREATE TABLE similarcards (
    carda INTEGER REFERENCES cards(id) ON DELETE CASCADE,
    cardb INTEGER REFERENCES cards(id) ON DELETE CASCADE,
    cooccurences INTEGER
);

Did I miss any redundancy? Is there anything I could improve on?

\$\endgroup\$
4
  • \$\begingroup\$ Welcome to Code Review! The post mentions SQL Server yet the postgresql tag is used- should the sql-server tag be added (instead)? \$\endgroup\$ – Sᴀᴍ Onᴇᴌᴀ Apr 26 at 23:12
  • \$\begingroup\$ Sorry if it was unclear- by sql server I just meant the provider for the sql database. I'll clarify. \$\endgroup\$ – Poultryghats Apr 26 at 23:58
  • \$\begingroup\$ Okay thanks ... what do you mean by "Did I miss any redundancy?"? Does the code work to the best of your knowledge? \$\endgroup\$ – Sᴀᴍ Onᴇᴌᴀ Apr 27 at 0:00
  • \$\begingroup\$ Yes, but things like many-to-many relationships can easily work while still being difficult to maintain, and I'm just a beginner with sql, so I wanted to know if I was missing any best practices kinds of things. \$\endgroup\$ – Poultryghats Apr 27 at 0:03

Your Answer

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

Browse other questions tagged or ask your own question.