Какой из вариантов выбрать для организации хранения данных по нескольким организациям в MySQL?
Уважаемые знатоки, помогите решить задачку.
Дано: 500 организаций, каждая имеет клиентскую базу до 300 человек, клиенты организаций производят записи на прием в организации, сразу оплачивая услугу, берем по максимуму, пускай 100 записей о приеме в год и 100 оплат в год, еще организации имеют маленькие, незначительные таблицы данных о товарах на своих складах, кадровые, бухгалтерские данные и т.п.
Получается, что на одну организацию приходится строк в базе (приблизительно):
• 300 записей (максимум) по клиентам (ID, ФИО, тлф, адрес, дата рождения, пол, поле-1, поле-2, поле-3, поле-4, поле-5, комментарии);
• 300*100 = 30000 (максимум) записей по таблице приемов (ID клиента, дата записи, поле-1, поле-2, поле-3, поле-4, поле-5, комментарии);
• 300*100 = 30000 (максимум) записей по таблице оплат (ID клиента, дата оплаты, сумму оплаты, поле-1, поле-2, поле-3, поле-4, поле-5, комментарии);
• 100 записей (в год) по таблице товаров на складе (ID товара, количество на складе, описание, поле-1, поле-2, поле-3, комментарии);
• 100 записей (в год) по таблице кадров (ID сотрудника, ФИО, тлф, адрес, квалификация, специальность, должность, описание, уволен, дата приема на работу, оклад, поле-1, поле-2, поле-3, поле-4, поле-5, комментарии);
• 300 записей (в год) по таблице выплаченных зарплат;
• Возможны другие мелкие таблицы посвящённые конкретной организации.
Итого получается примерно 601000 строк только по одной организации, без учета количества столбцов в таблицах (я примерно указал поля в таблицах).
На 500 организаций получится 300 500 000 записей в таблице (опять же, без учета количества столбцов).
Задача: организовать хранение и оперативный доступ к данным, а также изолированность организаций. Работа будет происходить в приложении Windows, которое будет установлено в каждой организации отдельно, приложение будет подключаться к СУБД расположенном на удаленном сервере.
Планирую использовать MySQL на облачном расширяемом сервере. В тех поддержке хостинга сказали, что по количеству баз данных меня не ограничивают и нужно учитывать возможности самого MySQL и дисковое пространство сервера. При необходимости можно задействовать дополнительные аналогичные серверы.
Я вижу 3 решения данной задачи:
1. Использовать для каждой организации отдельную БД. Будет мастер БД, в которой будет храниться минимальная информация об организациях и их ID базы данных, а также логин и пароль для нужной базы. Боюсь насчет ресурсов. Не имею представления, как скажется на работе сервера такое количество БД, если учитывать, что 80% организаций могут работать в своей БД в то же время, что и остальные. Но этот метод мне безумно нравится, из-за возможности делать бэкап для каждой организации отдельно, не мешая другим. Восстановление бэкапа не откатит данные других организаций. Простота доступа к данным. Я вижу одни плюсы, вот только не знаю потянет ли сервер и сама MySQL.
2. Использовать одну базу данных в которой будет 500 комплектов таблиц (по количеству организаций), описанных выше. Т.е. будет Мастер таблица, в которой будут данное об организации и указан префикс в именах баз данных, например префикс «Орг1_» для первой организации, тогда для этой организации будет комплект таблиц с именами Орг1_Кленты, Орг1_Приемы, Орг1_Оплаты, Орг1_Склад и .т.д., все таблицы для организации Орг1. И так 500 комплектов по каждой организации. Итого получится 500*10(примерное количество таблиц на организацию) = 5000 таблиц в одной бд. Не много ли? Потянет? Тоже довольно неплохой метод, но из-за недостатка опыта не могу оценить нагрузку и достаточность ресурсов для такого варианта.
3. Использовать 10 (примерное количество таблиц на организацию) таблиц, в которой будет храниться вся информация по всем организациям. Мастер таблица будет хранить данные по Организациям (Id организации, всякая другая информация, типа тлф, адрес и т.д.) и 10 таблиц те, которые я описал в самом начале, только в каждой из них добавляется поле ID организации. Т.е., например, таблица записей по клиентам выглядела бы так (ID, ID_Организации, ФИО, тлф, адрес, дата рождения, пол, поле-1, поле-2, поле-3, поле-4, поле-5, комментарии). И тогда каждая таблица будет хранить данные сразу обо всех организациях. Но мне кажется, что некоторые таблицы будут слишком перегружены, например таблицы записей по приемам и оплатам, в каждой из них будет до 300000*500=150000000 строк, много, не будет тупить? Мне этот метод не нравится, мне кажется, что таблицы получатся слишком перегружены, сложно делать бэкапы и восстановления. Но у этого метода есть возможность использовать ключи с указанием двух полей (ID и ID_Организации) и тогда в одной таблице ID клиента для всех организаций будет начинаться с 1, не знаю, бонус ли это. Еще в этом варианте можно использовать схемы, но не уверен, даст ли это достаточную изоляцию данных между организациями.
Я как раз перед тобой задал этот вопрос, только не так развернуто.
Мне нравится вариант 3 тем что здесь единая база. Ну и если она в облаке например AWS Aurora то ты следишь за бесперебойной работой только но AWS все делает за тебя. Она делает копии, при обнаружении пролем сам их решает причем моментально, распределяет нагрузку по разным зонам а так же дублирует по зонам, создает репилики и т.д. Все что нужно тебе вовремя добавить дополнительных ресурсов как память, процесорная сила и т.д. Так же нужно следить за запросами, вовремя выявлять проблемыне и оптимизировать или код или базу.
Но вот что меня беспокоит. Гипотетически, если ты потеряешь базу то ты птеряешь данные всех клиентов сразу. Тоесть бизнесу хана. Если база встанет, то все клиенты встанут вместе сней а в бизнесе продаж, даже 5 минут могут быть критичными. Еще одной проблемой может быть будующие. Предположим вы хотите что бы другие могли создовать расширения и эти расширения устанавливать свои табилцы. Одна база может вырости очень сильно. Короче сторонние разработчики это проблема. Они могут сделать код без учета идентификатора клиента и получить напимер все записи из таблици. Уезвимости в коде который писали не вы.
Поэтому я склоняюсь к методу 1. Но тут свои недостатки.
А какие недостатки в 1 методе?
Насчет хана базе в 3м варианте, можно сделать зеркалирование на пару серверов и в случае чего, чтобы клиент подключался к другим серверам.
В перовм методе адмиристрирование. Если вы делаете SaaS вы берете на себя ответственность за работоспособность базы. То-есть вм нужно будет мониторить 300 баз. В добавок если вы хотите что бы пользователи могли создать новую компанию вам придется далеть механизм создания новой базы данных. В зависимосит от технологий которые вы будете использовать, это может быть разным уровенм сложности. Если у вас Инстансе в облаке например и вы сами поставили MySQL то нужно много менеджмента что бы все работало исправно. Нужно несколько инстансов в разных зонах, настрока мастре\слэйв, реплики и т.д. А использовать что то типа AWS RDS где это все уже решено вы не сможете так как это будет очень дорого создовать по новай службе на каждого клиента.
Я не могу вам точно сказать так как уже упомянул что я сам задал такой вопрос и не могу точно определится.
Вы уже знаете как технически вы будете ршать задачу? Это будет облачная служба или все будете сами настраивать?
Это будет облачный сервер с предустановленным MySQL. Создание новой БД, запись ее в Мастер БД, создание пользователя для новой БД будет происходить с сайта на php во время регистрации новой организации. Мониторить пока не думал как, можно сделать в мастер БД таблицу для ошибок входа или что-то такое, сервер по расписанию будет запускать скрипт на php сайе, тот будет проверять либо сами базы, либо таблицу ошибок и сигнализировать администратору либо посредством смс, либо email.
я в одной организации уже так сделал, но в масштабах одной организации, а тут совсем другие масштабы планируются, вот и не уверен, что все будет работать как надо.
Если использовать что то типа AWS там можно все поднять на службах, и потом мониторить чере специальную службу Cloud Watch. Ну и в добавок половину работы по решению проблем AWS возмет на себя. А в другом случаее много нужно писать самому и не факт что это будет качественный код и что не будет сбоев всеравно.
Разницы между занимаемыми ресурсами быть не должно. Количество записей все равно будет одинаковое. Что насчет производительности, лучше первый вариант. Легче имкать по базам данных, чем в одной огромной таблице
