魔术：收集数据库设计 [英] Magic: The Gathering database design

问题描述

我想为我拥有的MTG卡创建一个数据库。设计将是什么？
我想存储有关每张卡的以下信息：

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.

以下是有关MTG卡的一般信息：

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.

我将在eBay上出售另一套mtg卡。该收藏集将包含价格/条件/日期/是否为立即购买或出价等。

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.

推荐答案

这不是编程问题，而是建模问题。任何进行编程但不建模的人都是编码员，而不是程序员。这只是数据输入的第一步。建模是编程的基本方面，因为它直接处理抽象，而抽象是计算机编程的真正天才。

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));

更详细的解释。

卡表是卡与实际卡的概念。您可以在没有任何实际卡的情况下排成一排。它为所有卡通用的卡的任何细节建模。显然，MTG卡具有很多详细信息（如所提到的一些颜色文字），但是对于这种模型而言，这些信息可能并不重要，因为这是为了收集和销售而跟踪实际的卡。但是，如果希望添加任何其他属性（例如稀有卡片），则可以在卡片表中放置它们。

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.

set表用于套。我不知道什么是布景，只知道这里是什么（也有对系列的随意引用，我不知道它们是否相关）。集具有名称，并用于对卡进行分组。因此，我们有一个设置表。

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.

cardset表是多对多连接器表。由于一个集合可以具有多个卡，并且一个卡可以属于多个集合，因此模型需要某种东西来表示该关系。这是关系数据库中的一种非常常见的模式，但是对于新手来说也是不明显的。

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.

有两个简单的查找表，即condition和saletype表。这两个表是出于规范化目的，允许用户针对这两种数据标准化其术语。他们每个人都有一个别名和一个说明。别名是英文的简短版本：好，差，拍卖，立即购买，而说明则是较长的英文文本差的卡显示磨损，弯曲和擦痕的迹象。显然，出于个人目的这样做的人可能不需要描述，但这是一种习惯。

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.

最后，系统的实质是单张牌桌。单张纸牌表代表您手中的实际卡片。它对使每个实际卡彼此不同的所有特征进行建模。单个卡片不是集合的成员（至少不是从描述中删除），而是一个更高层次的概念（例如它的发布方式-所有英雄：Bartek斧头Wielder卡片都是黑暗的一部分谜团和死亡小丑集等）。因此，单张卡只需要参考其父卡表以及实际的普通卡特征即可。

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.

无法实际强制卡片是ANY集合的成员，或者集合具有任何卡片。那将是一个应用程序逻辑问题。这是多对多联接器表的问题之一。它可以为关系建模，但不能强制执行。

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.

这篇关于魔术：收集数据库设计的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！