Magic: The Gathering database design

Question

I would like to create a database for MTG cards I own. What would the design be? I would like to store the following information about each card:

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 a little info 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's foil or not makes it unique. 
5. A card may be in multiple sets.
6. A set has multiple cards. 

The gimmick is that in my collection I may have several copies of the same card but with different conditions, or purchased price, or sold price may be different.

I will have another collection of mtg cards that have been sold on eBay. This collection will have the price/condition/date/whether it was a "Buy It Now" or Bid, etc.

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

Answer 1

It's not a programming question, it's a modeling question. Anyone who is programming but not modeling, is a coder, not a programmer. That's just a step above data entry. Modeling is a fundamental aspect of programming as it deals directly with abstraction, and abstraction is the real genius of computer programming.

Normalization and database design is an excellent way for someone to become better at programming in general as normalization is also an abstraction process.

Abstraction is arguably the most difficult aspect of computer programming, particularly since computer programming requires a person to both be especially pedantic and literal (in order to properly work with the steadfast stubbornness and idiocy that is a computer) as well as handle and work in a very high level and abstract space.

For example, the arguments in design meetings are not over language syntax.

So, that said. I have updated the schema in minor ways to address 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's 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));

A more detailed explanation.

The card table is the concept of the card vs an actual card. You can have a card row without having any actual cards in hand. It models any details of the card that are common to all cards. Obviously MTG cards have very many details (color text as some one mentioned), but these are likely not important to this kind of model, since this is to track actual cards for the sake of collecting and sale. But if there was any desire to add any other attributes, like card rarity, the 'card' table would be the place to put them.

The set table is for the sets. I don't know what a set is, only what is posited here (there is also casual reference to a series, I don't know if they are related or not). Sets have a name, and are used to group cards. So, we have a 'set' table.

The cardset table is the many-to-many joiner table. Since a set can have several cards, and a card can belong to several sets, the model needs something to represent that relationship. This is a very common pattern in relational databases, but it is also non-obvious to novices.

There are two simple lookup tables, the condition and saletype table. These two tables are here for normalization purposes and let the user standardize their terms for these two categories of data. They each have an 'alias' and a 'description'. The alias is the short english version: 'Good', 'Poor', 'Auction', 'Buy it now', while the description is the longer english text 'Poor cards show sign of wear, bending, and rub marks'. Obviously someone doing this for their own purpose likely do not need the description, but it's there as a habit.

Finally, the meat of the system is the singlecard table. The singlecard table represents an actual, in your hand card. It models all of the characteristic that make each actual card different from each other. An individual card is not a member of a set (at least not from the description), rather that's a higher level concept (such as how it was published -- all "Hero: Bartek the Axe Wielder" cards are part of the "Dark Mysteries" and "Clowns of Death" sets, or whatever). So, the single card needs only reference its parent card table, with the actual common card characteristics.

This single card has the references to the card's condition and how it was sold via the foreign keys to the appropriate tables. It also has the other data, such as the dates and prices that were necessary.

Based on what was given, this should meet the basic need.

It would be a good exercise to remodel this yourself. Start with the your most basic needs, and the best model that you understand to make. Then contrast it to what I've written here, and then use that book to perhaps try and understand how whatever your simple design may have been becomes this design.

Note that there is no way to actually enforce that a card is a member of ANY set, or that a set has any cards. That will be an application logic problem. This is the one of this issues with many-to-many joiner tables. It can model the relationship, but it can not enforce it.

Answer 2

Ok, this isn't really a programming question as such; it's very high-level and you haven't indicated what database you'll be using and what you've tried.

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

What you need to figure out is which combination of those fields can be used to mark a card uniquely from another.

If as you say, the Date of Purchase and the Cost can vary then in the database you choose, you would need to make an index based upon those fields; this will give you the ability to store more than one instance of the same card.

I would read up on 'Relational databases'. If you're really lost, I suggest picking up a copy of 'SQL for dummies', SQL is the language that most database providers use and it has step by step instructions and tutorials for building your own databases'.