Database configuration

27 May 2011

Database tables

MySQL tables to store all the information for the “Registering a company”:

I created a new repository on Github.com for the project. There you will be able to find the initial SQL I used to create the tables: https://github.com/lcf/BasicCRM/blob/master/data/dump.sql

Migrations

For simplicity I’m not going to use any migrations mechanism. I’ll be adding new SQL queries to the end of dump.sql. For non-demo projects I personally prefer to use the migrations extension for Doctrine 2: https://github.com/doctrine/migrations

Mapping

In order for the data to be saved into the database, domain model has to be mapped to the tables structure.

namespace Domain;

use Doctrine\Common\Collections\ArrayCollection;

/** @Entity @Table(name="companies") */
class Company
{
    /**
     * @Id @Column(type="integer")
     * @GeneratedValue(strategy="AUTO")
     */
    protected $id;

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

    /** @ManyToOne(targetEntity="Domain\Subscription") */
    protected $subscription;

    /** @Column(name="is_activated", type="boolean") */
    protected $isActivated;

    /** @OneToMany(targetEntity="Domain\User", mappedBy="company", cascade={"all"}) */
    protected $users;

    public function __construct($name, Subscription $subscription, User $admin)
    {
        $this->users = new ArrayCollection();
        if (!$name) {
            throw new \DomainException('Company name cannot be empty');
        }
        $this->name = $name;
        $this->subscription = $subscription;
        $this->isActivated = false;
        if (!$admin->isAdmin()) {
            throw new \DomainException('User must be a new admin in order to create a company');
        }
        $this->addUser($admin);
    }

    protected function addUser(User $newUser)
    {
        foreach ($this->users as $existingUser) {
            if ($existingUser == $newUser) {
                throw new \DomainException('User is in the company already');
            }
        }
        $newUser->setCompany($this);
        $this->users[] = $newUser;
    }
}

You can find the rest in the Git repository. You may want to test the mapping using the doctrine’s console tool, running doctrine orm:validate-schema. In order to do that you’ll need to create cli-config.php in the application directory. Follow the official doctrine documentation or check out my Github repository.