Hi,
I am trying to develop in Java card game similar to Magic the Gathering. I am planning to use database, but since im new to databases, im not so sure about my solution. It would be great if you could correct me.
I have also troubles with table Card, since Card can be spell, monster or resource, but they are different objects with different attributes. Should I just add every possible attribute to table and then leave some blank? Or is it good idea to save it as whole object?
- User
----------
- user_id
- deck_id
- e-mail
- Deck
----------
- deck_id
- card_id
- Card
-----------
- card_id
- type
- ??????
Help with database design.
- User
----------
- user_id
- deck_id
- e-mail
- Deck
----------
- deck_id
- card_id
- Card
-----------
- card_id
- type
- Attributes_to_Card
-----------
- card_id
- attr_id
- Attribute
-----------
- attr_id
- attr_type
Since there could be many attributes to one card, what I've done here is to make a table that holds a card id and it's associated attr id. In your query you'll have to do a query from card table and inner join to the attributes_to_card table and inner join again to the attribute table to get all the attributes for that card.
Someone else may have a cleaner or more refined solution than mine.
----------
- user_id
- deck_id
- Deck
----------
- deck_id
- card_id
- Card
-----------
- card_id
- type
- Attributes_to_Card
-----------
- card_id
- attr_id
- Attribute
-----------
- attr_id
- attr_type
Since there could be many attributes to one card, what I've done here is to make a table that holds a card id and it's associated attr id. In your query you'll have to do a query from card table and inner join to the attributes_to_card table and inner join again to the attribute table to get all the attributes for that card.
Someone else may have a cleaner or more refined solution than mine.
(Excuse my magic slang, but it kicked in :D)
I would create one table per card type (monster, instant, enchantment, etc...):
-Monster
-------
-monster_id
-name
-cost
-attack
-defense
-Scorcery
-------
-scorcery_id
-name
-cost
Now it can be a bit tricky to store the actual instants (actions) those monster can do. But this can be done by creating one table per action type. For example, anything that does damage could be inserted into the following table:
-Damage Instants
--------
-instant_id
-dmg
-dmg_type
The last thing is to store the mapping of monster to an instant, which often is a many-to-many relationship: A monster can have instants which are described in the instants table, however several monster could posses the same instant and a monster can have to instant at all.
-Monsters table: monster_id, etc...
-Instants table: instant_id, etc...
-Mapping table: monster_id, instant_id
When retrieving stuff, you can then select a specific monster and ALL instants and ALL permanent effects (and many more) with a single statement and some joins.
I would create one table per card type (monster, instant, enchantment, etc...):
-Monster
-------
-monster_id
-name
-cost
-attack
-defense
-Scorcery
-------
-scorcery_id
-name
-cost
Now it can be a bit tricky to store the actual instants (actions) those monster can do. But this can be done by creating one table per action type. For example, anything that does damage could be inserted into the following table:
-Damage Instants
--------
-instant_id
-dmg
-dmg_type
The last thing is to store the mapping of monster to an instant, which often is a many-to-many relationship: A monster can have instants which are described in the instants table, however several monster could posses the same instant and a monster can have to instant at all.
-Monsters table: monster_id, etc...
-Instants table: instant_id, etc...
-Mapping table: monster_id, instant_id
When retrieving stuff, you can then select a specific monster and ALL instants and ALL permanent effects (and many more) with a single statement and some joins.
I would introduce a separate deck-card mapping table - this ensures that your foreign keys are pointing to the parent object in the ownership relationships.
This assumes a player can have more than one of each type of card, and several decks - you could also store a quantity rather than having a mapping-table row for each individual card.
- User
----------
- user_id * (Primary Key)
- e-mail
- Deck
----------
- deck_id *
- user_id F (Foreign Key)
- name
- description
- "Cards In Deck" Mapping Table
-----------
- deck_id F
- cardindeck_id *
- card_id F
- Card
-----------
- card_id *
- type
- ??????
Regarding storing different "classes" of object - the approach Alpha has suggested is known as an "Entity-Attribute-Value" model. It is flexible and adding new cards should be fairly straightforward; the downside to this approach is in performance and in ease of writing queries against the data - if you want to do any reporting or generate gameplay statistics, the queries can become rather ugly.
I would recommend a more "flat", relational data model. A couple of links discussing this problem and some potential solutions:
AskTom
AskTom again
StackOverflow
This assumes a player can have more than one of each type of card, and several decks - you could also store a quantity rather than having a mapping-table row for each individual card.
- User
----------
- user_id * (Primary Key)
- Deck
----------
- deck_id *
- user_id F (Foreign Key)
- name
- description
- "Cards In Deck" Mapping Table
-----------
- deck_id F
- cardindeck_id *
- card_id F
- Card
-----------
- card_id *
- type
- ??????
Regarding storing different "classes" of object - the approach Alpha has suggested is known as an "Entity-Attribute-Value" model. It is flexible and adding new cards should be fairly straightforward; the downside to this approach is in performance and in ease of writing queries against the data - if you want to do any reporting or generate gameplay statistics, the queries can become rather ugly.
I would recommend a more "flat", relational data model. A couple of links discussing this problem and some potential solutions:
AskTom
AskTom again
StackOverflow
Just a nitpick.
Why do I often see the table name repeated as the primary key?
monster
-------
monster_id
instead of:
monster
-------
id
Is there some advantage to it?
I always find it rather confusing to type.
Why do I often see the table name repeated as the primary key?
monster
-------
monster_id
instead of:
monster
-------
id
Is there some advantage to it?
I always find it rather confusing to type.
Quote:Original post by Madhed
Just a nitpick.
Why do I often see the table name repeated as the primary key?
monster
-------
monster_id
instead of:
monster
-------
id
Is there some advantage to it?
I always find it rather confusing to type.
It makes it clear what you're referring to, prevents occasional problems when not fully qualifying column parameters, and makes foreign key relations look a lot better (and easier to spot broken ones).
It seems you are conflating the two completely different concepts of an abstract card (with a name and mechanical details; in MtG they are listed in the Gatherer search engine and the Oracle) and a card instance (belonging to a player and possibly part of a deck; in MtG they are the physical pieces of paper you buy).
The mechanical description of cards might be buried in program code; attributes for card instances might be:
card_instance_id (PK)
card_id (FK to abstract card list)
owner_id (FK to players)
deck_id (FK to decks, optional)
maybe various items about the card's acquisition by that player
The one to many deck <-> card instance relationship obviously replaces the deck <-> abstract card many to many relationship and the corresponding table.
Attributes for abstract cards might be:
card_id (PK)
name
type_id (FK to a table that enumerates card types)
text
illustration (BLOB or asset name)
set (FK to a table that enumerates sets of cards)
any other things that will be displayed to the player in-game
In MtG there's another layer you might not want: different printings and alternate illustrations of the same abstract card. Card instances would reference an abstract "printing" that would contain some details (illustration, set) and reference, in turn, the abstract card definition.
The mechanical description of cards might be buried in program code; attributes for card instances might be:
card_instance_id (PK)
card_id (FK to abstract card list)
owner_id (FK to players)
deck_id (FK to decks, optional)
maybe various items about the card's acquisition by that player
The one to many deck <-> card instance relationship obviously replaces the deck <-> abstract card many to many relationship and the corresponding table.
Attributes for abstract cards might be:
card_id (PK)
name
type_id (FK to a table that enumerates card types)
text
illustration (BLOB or asset name)
set (FK to a table that enumerates sets of cards)
any other things that will be displayed to the player in-game
In MtG there's another layer you might not want: different printings and alternate illustrations of the same abstract card. Card instances would reference an abstract "printing" that would contain some details (illustration, set) and reference, in turn, the abstract card definition.
This topic is closed to new replies.
Advertisement
Popular Topics
Advertisement