Magic: Gathering Database Design

Question

I would like to create a database for my own MTG cards. What will be the design? I would like to save the following information about each map:

1. Name of card. 2. Set the card belongs to. 3. Condition of card. 4. Price it sold for. 5. Price I bought it for. 

Here is some information about MTG cards in general:

 1. Every card has a name. 2. Every card belongs to a set. 3. A card may have a foil version of itself. 4. Card name, set it belongs to, and whether it foil or not makes it unique. 5. A card may be in multiple sets. 6. A set has multiple cards. 

The trick is that in my collection I may have several copies of the same card, but with different conditions or the purchase price, or the sale price may differ.

I will have another collection of mtg cards that were sold on eBay. This collection will have a price / condition / date / whether it’s Buy Now or Bid, etc.

The idea is to find out what price I should sell my cards based on the eBay collection.

Answer 1

This is not a programming issue, it is a modeling issue. Anyone who programs, but does not model, is an encoder, not a programmer. This is just a step above data entry. Modeling is a fundamental aspect of programming, as it is directly related to abstraction, and abstraction is a true genius of computer programming.

Normalizing and designing a database is a great way for someone to become better at programming in general, since normalization is also an abstraction process.

Abstraction is perhaps the most difficult aspect of computer programming, especially because computer programming requires that a person be especially pedantic and literal (in order to work properly with the stubborn stubbornness and idiocy that is a computer), and also process and work at a very high level and abstract space.

For example, arguments in project meetings do not match the language syntax.

So that is said. I updated the circuit in small ways to fix the changes.

 create table card ( card_key numeric not null primary key, name varchar(256) not null, foil varchar(1) not null); -- "Y" if it foil, "N" if it is not. create table set ( set_key numeric not null primary key, name varchar(256) not null); create table cardset ( card_key numeric not null references card(card_key), set_key numeric not null references set(set_key)); create table condition ( condition_key numeric not null primary key, alias varchar(64), description varchar(256)); create table saletype ( saletype_key numeric not null primary key, alias varchar(64), description varchar(256)); create table singlecard ( singlecard_key numeric not null primary key, card_key numeric not null references card(card_key), condition_key numeric not null references condition(condition_key), purchase_date date, purchase_price numeric, saletype_key numeric references saletype(saletype_key), sell_date date, sell_price numeric, notes varchar(4000)); 

More detailed explanation.

A card table is a concept of a card versus a real card. You may have a number of cards without having real cards. It simulates any map details that are common to all maps. Obviously, MTG cards have a lot of details (colored text, as mentioned above), but they are probably not important for this type of model, since it is tracking the actual cards for collection and sale. But if there was a desire to add any other attributes, for example, rarity of the map, the map table would be the place of their placement.

The table of sets for sets. I don’t know what the set is, only what is set here (there is also a random link to the series, I don’t know if they are connected or not). Sets have a name and are used to group cards. So we have the table "set".

The map table is a many-to-many table of tables. Since a set can have several cards, and a card can belong to several sets, the model needs something to represent these relationships. This is a very common relational database model, but it is also not obvious to beginners.

There are two simple lookup tables, a state and a saletype table. These two tables are for normalization and allow the user to standardize their terms for these two categories of data. Each of them has a "nickname" and a "description". The alias is the short English version: Good, Bad, Auction, Buy Now, and the description is a longer text in English. "Bad cards show signs of wear, bending, and marking." Obviously, someone doing this for their own purpose most likely does not need a description, but it is there as a habit.

Finally, the meat of the system is a one-card table. A single card table is an actual card in hand. It simulates all the characteristics that make each real card different from each other. An individual card is not a member of the set (at least not from the description), but rather that a higher-level concept (for example, as it was published) is all the cards "Hero: Bartek the Ax Wielder" are part of the "Dark Mysteries" and "Clowns of Death", or something else). Thus, a single card only needs a link to its parent card table with the actual characteristics of the common card.

This single card has links to the status of the card and how it was sold through foreign keys to the corresponding tables. It also contains other data such as dates and prices that were needed.

Based on what has been given, this should satisfy a basic need.

It would be a good exercise to remake it yourself. Start with your most basic needs and the best model you understand. Then compare it with what I wrote here, and then use this book to perhaps try to understand how your simple design would not turn into this design.

Note that there is no way to actually ensure that the card is a member of ANY set, or that the set has cards. This will be an application logic problem. This is one of these problems with many carpentry tables. He can model relationships, but he cannot enforce it.

Answer 2

Well, this is not a programming issue per se; This is a very high level, and you did not indicate which database you will use and what you have tried.

However, to give you a few points, the first list (in your question) almost certainly represents most of the information you need to store in your database.

What you need to find out is what combination of these fields can be used to uniquely mark a card from another.

If, as you say, the purchase date and price may differ in the database of your choice, you will need to create an index based on these fields; this will give you the ability to store more than one instance of the same card.

I would read Relational Databases. If you are truly lost, I suggest putting together a copy of “SQL for Dummies,” SQL is the language that most database providers use, and it contains step-by-step instructions and guides for creating your own databases.

Answer 3

I suggest you look at the data file from www.mtgjson.com

Just by seeing which types of fields they have selected and reading the comments and documentation, you are likely to avoid many caveats.

For example, you will see how they handle duplicate names, cards that are related to each other, for example, flipped or rotated or combined with a version of another and many other tunes.