Sign in to follow this  
MrProper

Help with database design.

Recommended Posts

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
- ??????

Share this post


Link to post
Share on other sites
- 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.

Share this post


Link to post
Share on other sites
(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.

Share this post


Link to post
Share on other sites
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

Share this post


Link to post
Share on other sites
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.

Share this post


Link to post
Share on other sites
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).

Share this post


Link to post
Share on other sites
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.

Share this post


Link to post
Share on other sites

Create an account or sign in to comment

You need to be a member in order to leave a comment

Create an account

Sign up for a new account in our community. It's easy!

Register a new account

Sign in

Already have an account? Sign in here.

Sign In Now

Sign in to follow this