TCG Database

The purpose of this assignment was to utilize a semester's worth of database training and SQL practice to create a simple SQL database using MySQL Workbench that could be used to query from a significant amount of data entries. The project also introduced the process of creating a set of business and user requirements - core functions of any database design project. Our team chose to create a mock-up of the back-end for a trading card game (TCG).


Part 1: Scenario

My group and I first had to come up with a scenario for the database's usage. We focused on a hypothetical app-based trading card game (TCG) that would allow users to play with friends based on a cards attack points, power level, and more. We aimed to keep things simple for the sake of process, and wanted our database to return an example image and a card's stats when queried.


Part 2: Functional Rules

My team and I then laid out the functional requirements of the database, which were as follows:

  1. Users (players) can contribute to the data in the database
  2. User can view and find information of each trading card
  3. Data must be approved by moderator(s) before entering the database
  4. Database returns image of the cards that will be 250 by 350 pixels in size

We disregarded functional requirements related to security, availability of the database, portability, and interface, as those would fall outside of our scope; though we acknowledged their importance in design.


Part 3: Business Rules

The team then went about establishing business rules of the design job, as follows:

  1. The database cannot return more than one image of a card at one given moment
  2. A player must have a Player_ID to access the database
  3. A player must have a password updated every 3 months
  4. A duel  has to be played by two or more players
  5. A card must contain one and only one card group (monster, trap, spell)

These rules follow the rules of the card game, but also restrictions in place by the owners of the company that makes the game. These became important considerations to keep in mind later as they define the working structure of the database.


Part 4: Diagramming

Once we established our restrictions, we set to work creating the SQL framework that would power all of this. We found ourselves working through several iterations, especially after discovering some fallbacks of MySQL Workbench, and really utilizing the skills we had learned over the course of the semester. 

The diagramming itself was straightforward by this point, and we found many of our mistakes through producing it and refining it along the way.

We eventually found a good working order for the database and set about finalizing the scripts we wanted to submit as well as the final structure.

Our finished network diagram utilizing crows feet notation.

Part 5: The Scripts

Much like the rest of our process, our scripts tested our skills and underwent much refinement through the project duration.


I used my skills of design thinking much more than I thought I might throughout this project, which turned out to be a major asset to our team. Additionally, we all employed some semblance of rapid (and I do mean rapid) prototyping when our database got wiped out by our college's cPanel for mysterious reasons. I came away from the project feeling confident in my SQL abilities, effectively having gone from 0 to 60 (maybe 55) throughout the course.