One Entity used in many, many-to-one relationships

Question

I have following entities:

class File{
    private $fileId;
    
    private $filePath;
}

class User{
    private $userId;

    private array $userFiles;
}

class Order{
    private $orderId;
    
    private array $orderFiles;
}

I have three entities: File, User, Order. Now I want to link User to File as one2many relationships. One user can have multiple files. And the same with Order - connect Order with File via one2many relationship - one order can have multiple files.

My question is, do I need to create two(one for relation with User, and one for relation with Order) tables for the File entity?

Or I can merge these two tables into one and for instance, add field type in File entity, which will tell us to which entity(User/File) it belongs.

Here you have my uncomplete mapping in xml

<entity name="File" table="file">
    <id name="fileId" type="uuid">
        <generator strategy="NONE" />
    </id>
    <field name="filePath />
</entity>

<entity name="User" table="user">
    <id name="userId" type="uuid">
        <generator strategy="NONE" />
    </id>
    <one-to-many field="userFiles" target-entity="File" mapped-by="herewhat?" >
        <cascade>
            <cascade-persist />
        </cascade>
    </one-to-many>
</entity>

<entity name="Order" table="order">
    <id name="orderId" type="uuid">
        <generator strategy="NONE" />
    </id>
    <one-to-many field="orderFiles" target-entity="File" mapped-by="herewhat?" >
        <cascade>
            <cascade-persist />
        </cascade>
    </one-to-many>
</entity>

I would prefer the second option - everything in one table if it is possible.

Thanks in advance!

Answer 1

I think the best option is to have two separate tables like order_files/user_files instead of stiffing everything in 1 table and relay on something like type(string) to get your collection afterwords. Also now this seems to be very simple table(files I mean), but with the time it can developed more and you may need different fields for the two different file types. So if you take this option you can build the relations b/n the tables as(for example the Order/orderFiles):

...

/**
 * @var Collection|OrderFiles[]
 * @ORM\OneToMany(targetEntity="PathToMyEntity\OrderFile", mappedBy="order")
 */
protected $orderFiles;

Then function you need add remove items in this collection, something like this: ....

/**
 * @param $orderFiles
 *
 * @return $this
 */
public function addOrderFiles($orderFiles)
{
    $this->orderFiles = $orderFiles;

    return $this;
}

/**
 * @param $orderFiles
 *
 * @return $this
 */
public function removeOrderFiles($orderFiles)
{
    foreach ($orderFiles as $orderFile) {
        $this->orderFiles->removeElement($orderFile);
    }

    return $this;
}

/**
 * Add orderFile
 *
 * @param OrderFile $orderFile
 *
 * @return Order
 */
public function addOrderFile(OrderFile $orderFile)
{
    $this->orderFiles[] = $orderFile;

    return $this;
}

/**
 * Remove orderFile
 *
 * @param OrderFile $orderFile
 */
public function removeOrderFile(OrderFile $orderFile)
{
    $this->orderFiles->removeElement($orderFile);
}

/**
 * Get orderFile
 *
 * @return Collection|OrderFile[]
 */
public function getOrderFiles()
{
    return $this->orderFiles;
}

....

Now lets manage this relation on the file class:

/**
 * @ORM\ManyToOne(targetEntity="PathToMyEntity\Order", inversedBy="orderFiles")
 * @ORM\JoinColumn(name="order_id", referencedColumnName="id", nullable=false)
 */
protected $order;

And the get set methods:

/**
 * Get order
 *
 * @return Order
 */
public function getOrder()
{
    return $this->order;
}

/**
 * Set order
 *
 * @param Order $order
 *
 * @return self
 */
public function setOrder(Order $order)
{
    $this->order = $order;

    return $this;
}

This is just a suggestion but it would be nice if some Doctrine guru gives opinion on this matter too :)