Hopefully someone can shed some light on this issue through either an example, or perhaps some suggested reading. I'm wondering what is the best design approach for modeling tables after their class hierarchy equivalencies. This can best be described through an example:
abstract class Card{
private $_name = '';
private $_text = '';
}
class MtgCard extends Card{
private $_manaCost = '';
private $_power = 0;
private $_toughness = 0;
private $_loyalty = 0;
}
class PokemonCard extends Card{
private $_energyType = '';
private $_hp = 0;
private $_retreatCost = 0;
}
Now, when modeling tables to synchronize with this class hierarchy, I've gone with something very similar:
TABLE Card
id INT, AUTO_INCREMENT, PK
name VARCHAR(255)
text TEXT
TABLE MtgCard
id INT, AUTO_INCREMENT, PK
card_id INT, FK(card.id)
manacost VARCHAR(32)
power INT
toughness INT
loyalty INT
TABLE PokemonCard
id INT, AUTO_INCREMENT, PK
card_id INT, FK(card.id)
hp INT
energytype ENUM(...)
retreatcost INT
The problem I'm having is trying to figure out how to associate each Card record with the record containing it's details from the corresponding table. Specifically, how to determine what table I should be looking in.
Should I add a VARCHAR column to Card to hold the name of the associated table? That's the only resolution that my peers and I have come to, but it seems too "dirty". Keeping the design extensible is the key here, allowing for the easy addition of new subclasses.
If someone could provide an example or resources showing a clean way of mirroring class/table hierarchies, it would be most appreciated.
