Database "design" using Symfony2 and Doctrine2

Question

I'm working on an application, which tracks expenses. I have users, and each user can create his categories for the expenses (like Food, Bills, Transport, Drinks, Clothes) and then create expenses - each expense has one category (OneToMany relationship).

For example, the expense Banana is in the category Food for the user Faery. I also have months for each user. For example, for March 2013, user Faery has a budget of 500. For March 2013, user SuperCoolUser has a budget of 900 and so on.

Here are my entities:

User.php

/**
 * @ORM\Entity
 * @ORM\Table(name="fos_user")
 */
class User extends BaseUser
{
    /**
     * @ORM\Id
     * @ORM\Column(type="integer")
     * @ORM\GeneratedValue(strategy="AUTO")
     */
    protected $id;

    /**
     * @ORM\OneToMany(targetEntity="Category", mappedBy="category")
     */
    protected $categories;

    /**
     * @ORM\OneToMany(targetEntity="Expense", mappedBy="expense")
     */
    protected $expenses;

    public function __construct()
    {
        parent::__construct();

        $this->categories = new ArrayCollection();
        $this->expenses = new ArrayCollection();
    }
    // ...
}

Category.php

/**
 * @ORM\Table(name="category")
 * @UniqueEntity(fields={"name", "user"})
 */
class Category
{
    /**
     * @ORM\Id
     * @ORM\Column(type="integer")
     * @ORM\GeneratedValue(strategy="AUTO")
     */
    protected $id;

    /**
     * @ORM\OneToMany(targetEntity="Expense", mappedBy="expense")
     */
    protected $expenses;

    public function __construct()
    {
        $this->expenses = new ArrayCollection();
    }

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

    /**
     * @ORM\ManyToOne(targetEntity="User", inversedBy="fos_user")
     * @ORM\JoinColumn(name="fos_user_id", referencedColumnName="id")
     */
    protected $user;
    // ...
}

Expense.php

/**
 * @ORM\Table(name="expense")
 */
class Expense
{
    /**
     * @ORM\Id
     * @ORM\Column(type="integer")
     * @ORM\GeneratedValue(strategy="AUTO")
     */
    protected $id;

    /**
     * @ORM\ManyToOne(targetEntity="User", inversedBy="fos_user")
     * @ORM\JoinColumn(name="fos_user_id", referencedColumnName="id")
     */
    protected $user;

    /**
     * @ORM\ManyToOne(targetEntity="Category", inversedBy="category")
     * @ORM\JoinColumn(name="category_id", referencedColumnName="id")
     */
    protected $category;

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

    /**
     * @ORM\Column(type="string")
     */
    protected $description;

    /**
     * @ORM\Column(type="float")
     */
    protected $price;

    /**
     * @ORM\Column(type="date")
     */
    protected $date;
    // ...
}

Month.php

/**
 * @ORM\Table(name="month")
 */
class Month
{
    /**
     * @ORM\Id
     * @ORM\Column(type="integer")
     * @ORM\GeneratedValue(strategy="AUTO")
     */
    protected $id;

    /**
     * @ORM\Column(type="string")
     */
    protected $name;

    /**
     * @ORM\ManyToOne(targetEntity="User", inversedBy="fos_user")
     * @ORM\JoinColumn(name="fos_user_id", referencedColumnName="id")
     */
    protected $user;

    /**
     * @ORM\Column(type="float")
     */
    protected $budget;
    /...

}

All of this looked fine to me, but I noticed that category, month, expense, all of these has a connection to User. I made this for the queries to be more convenient, but isn't it becoming too duplicated?

I have really little experience with databases and with programming, but I want the application to become nice and the code to be of good quality.

I want to know if this code is too problematic and connected and duplicated, or it's allowable. And if it's not please give me some advice or ideas on how to implement this better.

Answer 1

IMO this is great, it's nicely normalised. You're separating things logically into models.

The only thing that's puzzling me is...what is your 'Month' entity for? None of your other Entities reference it?

I don't know if:

/**
 * @ORM\OneToMany(targetEntity="Expense", mappedBy="expense")
 */
protected $months;

Is meant to reference months? I'm guessing it is.

The only thing I can think of is that you're using one model to satisfy two different entities : the categories model.

If I were you I would split it into to things:

User_Categories

and

Expense_Categories

This is something I often do, and I've found it better than having one abstract category entity. Having more tables has almost no downsides from a programatic point of view.

The only other thing I can think of really is, even if (as above) you meant to have a month entity, what is it for? It's actually a budget entity, not a month entity.

Date/time models are already abstracted quite well in doctrine itself (just have a column [or two] specifying the date range), so having a budget entity makes much more sense, then you can specify arbitrary budget time periods instead of the monthly constraint.

Hope this helps!