Entities and polymorphic relationships

Question

Lets say that I have a shelf on which I can put items that are shelfable (not sure if that is actually a word but you can understand my point). So I have the following:

class Shelf
{
    /** @var ShelfableItemInterface[] **/
    private $items = [];

    // addItem, getItems...
}

interface ShelfableItemInterface
{
}

/** let's imagine that not all books are shelfable **/
class Notebook extends Book implements ShelfableItemInterface
{
}

class PhotoFrame implements ShelfableItemInterface
{
}

So those are the definitions, and now I can create shelfs with different items on them:

$shelf = new Shelf();
$shelf->addItem(new Notebook(...));
$shelf->addItem(new PhotoFrame(...));
$shelf->addItem(new PhotoFrame(...));

// will return the three items
var_dump($shelf->getItems());

So, this works perfectly while all that data is not persisted and retrieved from storage.

I'm currently using Doctrine ORM which can automatically fetch the related objects and instantiate the objects of their proper classes, but that only works if all the related items extend same class which should be set in the relationship definition.

This is a problem as I don't want to be forced to make the related items extend some class (as we can see the Notebook already extends something), but make the relationship work by interface.

I know that I still would have some mapping strategy (or however we should call that), so for example in the database we'll have a table with the following:

+----+---------+-----------+
| id | shef_id | item_type |
+----+---------+-----------+
|  1 |       5 |         1 |
|  2 |       5 |         2 |
|  3 |       5 |         2 |
+----+---------+-----------+

(additional data in tables per type)

So, I'm confused how to make this work or where to put the process, in order for the ORM (or my logic) to know that when I call getItems(), when fetching the related items, if "item_type" is 2, it should instantiate object from PhotoFrame, 1 for Notebook etc.

I don't know if I stated my problem clearly, but I hope you can understand me.

Update: I see that this is available in java - http://jpaobjects.sourceforge.net/m2-site/main/documentation/docbkx/html/user-guide/ch04s09.html without the class inheritance part, but I would like to learn how I can achieve this in php without such library

Answer 1

The issue here is that the database doesn't care about interfaces. Interfaces are a language/compiler construct and have no real meaning outside of the application. To an outside observer, it's impossible to see if two classes have the same properties because an interface demands it, or because of pure coincidence.

ORMs usually provide a basic handling of inheritance, but the database itself doesn't quite care about inheritance either. The database is interested in tables and keys, nothing more (I've oversimplified it, but the point still stands).

You seem to expect a solution in which you can implement an interface on a class and not have to change the data model to reflect that change. It doesn't work like that. If you want to store a "A is shelved on B" relationship, you will have to define a FK for every possible implementation of A. Or you do away with an FK constraint, which I don't quite suggest.

---

What you have here is a one-to-many relationship. A shelf can have many items, an item can only be on one shelf at a time.

One-to-many relationships dictate that the "many" each store an FK to the "one". Which in this case means that every ShelfableItem must have a ShelfId.

My PHP is a bit rusty. If I remember correctly, interfaces cannot implement properties/fields, only methods. I'm not sure how to do this in PHP syntax, but purely to show a basic example, I'll use C#.

Have the interface include the FK:

public interface ShelfableItemInterface
{
    int ShelfId { get; set; }
}

Implement the interface so that the items actually have a ShelfId:

public class Book : ShelfableItemInterface
{
    public int Id { get; set; }

    //other fields

    public int ShelfId { get; set; }
}

public class Jar: ShelfableItemInterface
{
    public int Id { get; set; }

    //other fields

    public int ShelfId { get; set; }
}

Store this value in your existing database tables:

+-----------------------------+
|            BOOKS            |
+----+---------+--------------+
| id | shelfid | other fields |
+----+---------+--------------+
|  1 |       5 |              |
|  2 |       5 |              |
|  3 |       5 |              |
+----+---------+--------------+

+-----------------------------+
|            JARS             |
+----+---------+--------------+
| id | shelfid | other fields |
+----+---------+--------------+
|  1 |       5 |              |
|  2 |       5 |              |
|  3 |       5 |              |
+----+---------+--------------+

---

(additional data in tables per type)

I understand why you think you need a "shelfableitems" table, but in reality it's more a burden than it is a blessing.

It creates a lot of extra work:

• You need to define item types.
• You need to retrieve items from the correct table based on their item type, which is less-than-obvious to do in a single query.
• You forgo the data integrity of having a FK constraint.
  • Therefore, you run the risk of the referenced item (book/jar) already having been deleted but a "ghost entry" remains in the ShelfableItems table.

ALternatively, you could create an intermediary table that does have (optional) FK constraints, but then you need to make a separate column for every possible FK:

+----------------------------------------------------+
|            ShelfableItems                          |
+----+---------+----------+---------+----------------+
| id | shelfid |  bookid  |  jarid  |  photoframeid  |
+----+---------+----------+---------+----------------+
|  1 |       5 |    1     |  NULL   |   NULL         |
|  2 |       5 |  NULL    |   6     |   NULL         |
|  3 |       5 |  NULL    |  NULL   |   15           |
+----+---------+----------+---------+----------------+

This creates a lot of wasted space. You have N columns, but you can guarantee that in every row, you will have (N-1) columns that are NULL.

But, more importantly, implementing this intermediary table is not necessary for data integrity purposes. Everything works perfectly fine without it, and it takes less effort to develop/maintain the data integrity.