 |
| |
* BasicLands and some of the early sets have multiple CardArts for a single card, making their names not-unique within a set |
||
So with all that information, the question is how to store it in an efficient way that enables it to be easily queried on any of it's attributes. The first thing I decided was that the most unique way to identify any given card would be:
CardName + SetName + Card# / CardArt
I say Card # / CardArt as earlier sets did not have Card#. So the reason this unique identifier is important is for a card like the following example:
Consider the card Shivan Dragon. I can store all of it's card information and just have all of it's different instances across sets simply point to it, rather then store it's identical info for each set it has been reprinted in. This unique identifier is similar to the gatherer's "multiverseid", which they use to enumerate each card.
So with my uniquely identifiable pieces set out, all I needed now was to build a database structure up in a way that would let me avoid redundancy and allow for easy queries. I decided to split the common fields into their own tables and tie them back to my main card information tables through relational tables. Example:
TypeOfCard is the relational table between Type and Card.
Following that method, I separated the rest of the fields in to what I thought should be in it's own table tied in through a relational table. I ended up with the base table Card, and 5 attribute tables: Type, BaseType, CardArt, Set. I tied these all in to the base table with relational tables TypeOfCard, BaseTypeOfCard, and CardInSet. CardInSet ended up being something that I could almost consider the base table and Card simply provides the info into it as Card in Set combines the unique keys for Card Information, Set Name, and CardArt/CardNumber. So rather then try to describe any more with words, here is the Database relationship view:
Click for larger image
I added an additional table "CardFxn" which has yet to serve any purpose, but I thought it might be a good place to store what base functions or what lower order methods I would need to call if I ever want to have this card run in some sort of game engine. It is simply a placeholder for now. Also Card has two fields dealing with Split Cards, I will touch on that in a future post. Another interesting note is that the images are not stored in this database, rather the ImagePath. This allows the database file to be relatively small allowing for a fast load time when a program needs to load up the database. The images can simply be stored in a subdirectory structure relative to the database file.
So there you have it, all the information can now be stored and queried. If I want all elves with BaseType "legendary" I simply select from BaseType legendary and Type Elf, and join on Card and CardInSet across the relational tables. I output the Full Typeline from the Card table so I don't need to go back and search the Type and BaseType tables a second time to find any additional Types/BaseTypes they might have. (that was my most recent addition to cut down query time).
More on Queries later, first I need to get the data, and for that I need a WebScraper. Next "Magic The Programming" post I will go over the basics of scraping info from the web and storing it.
So there you have it, all the information can now be stored and queried. If I want all elves with BaseType "legendary" I simply select from BaseType legendary and Type Elf, and join on Card and CardInSet across the relational tables. I output the Full Typeline from the Card table so I don't need to go back and search the Type and BaseType tables a second time to find any additional Types/BaseTypes they might have. (that was my most recent addition to cut down query time).
More on Queries later, first I need to get the data, and for that I need a WebScraper. Next "Magic The Programming" post I will go over the basics of scraping info from the web and storing it.