Design tables and class hierarchies

Question

Hopefully someone can shed light on this issue either with an example or perhaps with some suggested readings. I am wondering what is the best design approach for modeling tables after their class hierarchy equivalents . This can best be described with 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 that I have modeled tables for synchronization with this class hierarchy, I went 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 encountered is figuring out how to associate each Card entry with an entry containing its data from the corresponding table. In particular, how to determine which table I should look for.

Should I add a VARCHAR column to Card to keep the name of the linked table? This is the only solution that my peers have come to, but it seems too "dirty." Saving the design extensibly is the key here, making it easy to add new subclasses .

If someone could provide an example or resources showing a clean way to mirror the class / table hierarchy, that would be very helpful.

Answer 1

Google "relational modeling specialization generalization." You will find some great articles on how to model the gen-spec template using relational tables. The same question has been asked many times in SO, with slightly different details.

The best of these articles will confirm your decision to have one table for generalized data and separate tables for specialized data. The biggest difference will be how they recommend using primary and foreign keys. In principle, they recommend that specialized tables have one column that does the double job. It serves as the primary key for a specialized table, but is also a foreign key that duplicates the PK of a generalized table.

This is a bit difficult to maintain, but it is very enjoyable during pairing.

Also keep in mind that DDL is required when a new class is added to the hierarchy.

Answer 2

In principle, no.

Forget about class hierarchies, storage models, and everything that is specific to your application and your specific application language. If you do not want to use RDb as a simple place to store your files, a dependent slave.

If you need the power and flexibility (in particular, extensibility) of a relational database, you need to model it independently of any application and use the principles of RDb, and not the requirements of the application language. Leave your application context for a while and create the database as a database. Find out about them. Normalize (exclude all duplication). Learn about structures and rules and implement them. When you do this, your queries and your “matching” will be effortless. There will be no "impedance." Use the correct data types and there will be no inconsistencies.

The structure you require is a regular subtype supertype. These are the terms of a relational database that have existed for more than 30 years in RM and more than 23 years in Relational Database products. No need to call them ridiculous new names. Wiki is not an academic reference, except possibly for cancerous tumors.

Given your tables, which are perfectly correct as a starting point (you normalized automatically), you need to:

• Rename the Map. Like Card.CardId

• Remove identifiers for subtypes, they are 100% redundant; CardId is both PC and FK.

• Add discriminator Card.CardType CHAR (1) or TINYINT. This will determine which subtype to join when CardType is unknown.

• It seems that you do not fully understand the concept of foreign keys, so it would be nice to start over. It is implemented here in a simple, ordinary form:

  ALTER TABLE MtgCard ADD CONSTRAINT Card_MtgCard_fk FOREIGN KEY (CardId) REFERENCES Card(CardId) 

• The connection between Card and MtgCard or PokemonCard is always 1 :: 1. The supertype is completed only when there is a card + {MtgCard | PokemonCard} with the same CardId. In your case, there can only be one subtype that is easy to achieve with a simple CHECK constraint.

  • In other cases, more than one subtype is completely legal.

  • Subtypes are Man - Teacher or Person - Student

• In relational databases there is no concept of joining “from” or “to” (either up / down or left / right), these concepts exist only to help us people; You can start with any existing table / key and go to any table you need. Tables between them are required only if there are no relational identifiers (i.e., where there are additional surrogates, ID columns are used as PCs without significant natural keys).

  • In this example, using your terms, you can go directly from “Enrollment in person” (for example, to capture LastName) or “Course” (to capture name) without having to visit intermediate tables; relationship lines are strong.
    ,
• Now class hierarchies ("Is" or "Is a") and everything else are simple and easy. If this doesn't seem easy to you, send a class predicate with a single statement (in English), and I'll put in SQL.

Quick reference system to standard relational database diagrams.