Работа с yii2: проектирование базы данных
Постановка задачи
Как я говорил в прошлой части, мне нужен сервис для сбора статистики по расходам и доходам. Но чем полнее будет описана задача, тем точнее будет её выполнение. И эта серьезная ошибка прошлой части, я не обозначил рамки своей задачи. В рамках прошлой части это не так критично, т.к. кроме развертки yii я ничего толком сделать не успел, но все равно, ай-ай-ай мне за такой просчет. Но сегодня я приступаю к процессу проектирования базы данных, поэтому мне необходимо максимально полно и точно описать все свои хотелки. Итак, я хочу сервис который:
- Позволяет записывать расходы
- Позволяет записывать доходы
- Позволяет к каждой позиции доходов и расходов добавлять текстовое описание
- Позволяет тегировать (расставлять теги) доходы и расходы. Желательно не ограничивать количество тегов к каждой записи.
Собственно на первом этапе это всё. Если я буду работать со своим сервисом один, мне должно хватить. Но если заглянуть чуть дальше, то вскроется еще один слой. А если я захочу вести бухгалтерию своей трудовой деятельности. Ну как бухгалтерию? Все те же самые расходы и доходы, только мне бы как-то отделить семейный финансовый поток от трудового. Понятно, что из трудового всё в семью-всё в семью, но всякие налоговые отчисления, страховые и тд. Вот и следующая хотелка:
- Необходима возможность создания досок в неограниченном количестве, назовем их финансовыми досками.
Ну мы же смотрим в будущее, поэтому надо, чтобы сервисом могли пользоваться несколько пользователей, как совместно так и раздельно. Отсюда получаем:
- Возможность регистрации нескольких пользователей
- Каждый пользователь может создавать свои приватные финансовые доски
- Пользователь может делиться своими досками с другими участниками системы
Вот кажется и все требования.
Проектирование базы данных
Есть множество приложений и сервисов для проектирования БД. Мне нравится dbdesigner.net. Пару лет назад на него наткнулся, меня он устраивает более чем. Я не настаиваю, можете использовать то, что нравится, хоть листочик. По поводу листочков. Когда мы въехали в предыдущий офис, то от старых владельцев осталась структура БД размером метров 5 в ширину на 2 в высоту, всё это было склеено из листов или А3, или А4, и размер шрифта что-то ближе к 12. Надеюсь у них был электронный вариант =)
Ладно, поменьше лирики, работаем дальше. В общем, посидев пару часов, была нарисована такая структура БД
Я понимал, что в зависимости от фрэймворка набор полей для таблицы пользователей, а скорее всего и количество самих таблиц будет изменено. Всякие служебные данные, миграции и т.д. Но тут я хотел отобразить только то, что относится непосредственно к моему сервису.
От таблицы пользователей мне надо только id пользователя.
board_rel — это связь пользователей и финансовых досок, я же захотел, чтобы было несколько досок и возможность поделится, обе задачи решаются этой таблицей.
fin_board — это собственно доски, они особо ничего не хранят кроме имени и какого-нибудь описания.
transaction_list — а вот это основная таблица, которая будет хранить все данные. Изначально включил только 2 поля в эту таблицу dsc — описание и val числовое значение, плюс поле связи с финансовой доской и поле связи с типом добавляемой информации. О первичных ключах не говорю, они есть везде. Пользователя я планировал получать через финансовые доски, но потом меня посетила мысль о совместном редактировании. А как узнать кто и когда добавил транзакцию? Соответственно добавил еще 2 поля: date_add и user_id — ну и до кучи добавил поле для фиксации даты последнего изменения. Конечно, в идеале выделить отдельную таблицу для полного лога изменений, но пока мне это не нужно. Думаю, что масштабирование в эту сторону не будет проблемой.
transaction_cats — это просто словарь тэгов или категорий, кому как больше нравится. Сначала я планировал связть один ко многим, но потом ко мне пришла идея, что мне может захотеться тегоривать записи несколькими категориями. Поэтому появилась еще одна таблица связей.
Допускаем, что это наша финальная структура базы данных. Приступим к переносу этой структуры в реальную СУБД.
Перенос структуры базы данных в MySQL. Миграции Yii2
Я не стал извращаться с выбором СУБД, думаю, что MySQL мне пока хватит за глаза, тем более, что моя цель, это изучение yii, а не PostgreSQL, или SQLight.
Чтобы начать работать с базой данных их Yii, будет очень неплохо настроить подключение. Делается это в файле:
config/db.php
Потеряться в настройках очень не просто, я в тебя верю, ты сможешь, я же смог =) Единственно, что я добавил там, так это параметр префикса к таблицам, считаю использование префикса хорошей практикой. Префикс добавляется параметром tablePrefix
<?php return [ 'class' => 'yii\db\Connection', 'dsn' => 'mysql:host=localhost;dbname=finansomer', 'username' => 'root', 'password' => 'root', 'charset' => 'utf8', 'tablePrefix' => 'yourBestPrefix_' ];
Дальше начинаются ништяки от Yii.
Нужно сгенерировать миграции для наших таблиц. Миграции — это такой крутой механизм для управления базой данных, многие сравнивают его с системой контроля версий, и у меня нет оснований спорить с ними. Чтобы создать миграцию, в терминале нужно перейти в папку проекта и выполнить команду
./yii migrate/create start_tables
Это приведет к автоматическому созданию файла с замысловатым названием m[ГодМесяцДень]_[ЧасыМинутыСекунды]_start_table в папке migrations, которая лежит в корне проекта.
<?php
use yii\db\Migration;
class m170307_040537_start_tables extends Migration
{
public function up()
{
}
public function down()
{
echo "m170307_053734_tst cannot be reverted.\n";
return false;
}
/*
// Use safeUp/safeDown to run migration code within a transaction
public function safeUp()
{
}
public function safeDown()
{
}
*/
}
Сюда нужно писать код, который будет создавать таблицы при выполнении миграции, и убивать таблицы при откате (последнее не обязательно, но тогда метод down обязан вернуть false):
<?php
use yii\db\Migration;
class m170307_040537_start_table extends Migration
{
public function up()
{
// Финансовые доски
$this->createTable('{{%fin_boards}}', [
'id' => $this->bigPrimaryKey(),
'name' => $this->string(255)->notNull(),
'dsc' => $this->text(),
]);
// Лист транзакций
$this->createTable('{{%transaction_list}}', [
'id' => $this->bigPrimaryKey(),
'board_id' => $this->bigInteger()->notNull()->defaultValue(0),
'user_id' => $this->bigInteger()->notNull()->defaultValue(0),
'dsc' => $this->string(255)->notNull(),
'val' => $this->money(11, 2)->defaultValue(0),
'date_add' => $this->datetime()->notNull(),
'date_update' => $this->datetime(),
]);
// Состанвой индекс для листа транзакций
$this->createIndex('transaction_list_key', '{{%transaction_list}}', ['board_id', 'user_id']);
// связь досок и пользователей
$this->createTable('{{%board_rel}}', [
'board_id' => $this->bigInteger()->notNull()->defaultValue(0),
'user_id' => $this->bigInteger()->notNull()->defaultValue(0),
'PRIMARY KEY(board_id, user_id)',
]);
// Индекс для таблицы связей 'board_rel`
$this->createIndex('idx_board_user_rel', '{{%board_rel}}', 'user_id');
// категории транзакций
$this->createTable('{{%transaction_cats}}', [
'id' => $this->bigPrimaryKey(),
'name' => $this->string(255)->notNull(),
]);
// связь транзакций и категорий
$this->createTable('{{%cats_rel}}', [
'transaction_id' => $this->bigInteger()->notNull()->defaultValue(0),
'cat_id' => $this->bigInteger()->notNull()->defaultValue(0),
'PRIMARY KEY(transaction_id, cat_id)',
]);
// Индекс для таблицы связей 'cats_rel`
$this->createIndex('idx_transaction_cat_rel', '{{%cats_rel}}', 'transaction_id');
}
public function down()
{
$this->dropTable('{{%fin_boards}}');
$this->dropTable('{{%transaction_list}}');
$this->dropTable('{{%board_rel}}');
$this->dropTable('{{%transaction_cats}}');
$this->dropTable('{{%cats_rel}}');
}
}
В действительности генератор миграций умеет много больше чем просто генерировать пустой класс, передавая параметры он может автоматически создавать всю структуру и связи между таблицами, например:
# Миграция таблицы [table-name] с полями title и body ./yii migrate/create create_[table-name]_table --fields=title:string,body:text
При этом поле id указывать не нужно, оно будет создано автоматически, это не обязательно, при желании можно первичный ключ переопределить. Для автоматической генерации название миграции должно начинаться с create_, а заканчиваться _table, чтобы yii понимал о чем идет речь, все что будет идти между этими конструкциями, определяется как имя таблицы. Не буду расписывать всё и вся, луче в конце этого поста дам ссылки на документацию, где более подробно расписаны возможности.
Думаю, что совершенно спокойно можно использовать вариант автоматической генерации полей, но лично мне нравится писать миграции ручками, т.к. с первой попытки все равно не угадаю правильную конфигурацию таблиц, к тому же это только изучение, и мне нужны эксперименты =)
Технически создание каждой таблицы можно было бы определить отдельной миграцией, и тогда можно было бы использовать автогенератор, но у меня уже есть структура и не вижу смысла разделять на мелкие операции.
Итог
Эта часть получилась не совсем про yii, а скорее про проектирование. Пока её писал структура БД менялась несколько раз, то одно забыл, то другое, потом не с первого раза определился с именованием таблиц и полей. Но вот по истечении ~месяца я решил, что это будет финальная начальная структура (классная формулировка). Этим хочу сказать, что не нужно вылизывать структуру, надо в определенный момент решиться и реализовать то, что есть, лучше по ходу что-то допилить. К тому же дополнительные сложности и телодвижения помогут лучше изучить framewark.
Полезные ссылки
Про миграции по-русски
Про типы данных
Всем рок!
