0

I am building a system that in production will have something like the below profile:

  • mySQL development database (production database may be different)
  • 20-30 tables (depends on extent of normalisation)
  • 'main' 4 tables having 300+ million records (each of which includes a text field that is populated for around 10% of records and can have comments up to around 4000 characters)
  • Around 100 simultaneous users

In order to try and optimise things, I am thinking it would be an advantage to separate out these long text fields where they exist and put them in their own table(s).

  1. Is the above correct practice?
  2. Is the below code to split the Item entity into two the correct way to handle it?

Item entity:

/**
 * @ORM\Entity
 * @ORM\Table(name="Items")
 */
class Item
{
    /**
     * @ORM\Column(type="integer")
     * @ORM\Id
     * @ORM\GeneratedValue(strategy="AUTO")
     */
    private $id;

    /**
     * @ORM\Column(type="string", length=100)
     */
    private $name;

    /**
     * @ORM\Column(type="timestamp")
     */
    private $datetime;

    /**
     * @OneToOne(targetEntity="Item_comment")
     * @JoinColumn(name="comment_id", referencedColumnName="id")
     */
    private $comment;
}

Item Comments entity:

/**
 * @ORM\Entity
 * @ORM\Table(name="Item_comments")
 */
class Item_comment
{
    /**
     * @ORM\Column(type="integer")
     * @ORM\Id
     * @ORM\GeneratedValue(strategy="AUTO")
     */
    private $id;

    /**
     * @ORM\Column(type="text")
     */
    private $comment;
}
Bendy
  • 3,506
  • 6
  • 40
  • 71
  • Maybe [this question](http://stackoverflow.com/q/4286698/1697459) can help you make a decision... – Wilt Sep 15 '16 at 11:47
  • Aside from the fact that your target entity is Shipping instead of Item_comment then I think you pretty much have it. Will splitting the items table actually help? Best to consult a database guru. – Cerad Sep 15 '16 at 12:10
  • haha - thanks for the error @Cerad. Thanks both - I'll keep them distinct in that case - however it seems very awkward though of we have to handle 'sub-entities' as a collection. Is there any functionality I'm missing that we can nest them together to handle them holistically? – Bendy Sep 15 '16 at 12:19
  • The Doctrine 2 ORM does not have any builtin functionality to split a single entity across multiple tables. This is actually one reason that I have moved away from the ORM and gone back to low level sql interactions. But I suspect you might be focusing too much on the implementation details at this point. Make sure you have a good understanding of how you want the your application to function at the business level. – Cerad Sep 15 '16 at 12:46

0 Answers0