Нужно сделать универсального робота, который будет обрабатывать SMS-команды.
Некоторые команды не подразумевают ответа, на большинство команд в случае их успешной обработки приходит ответное SMS.
Для некоторых команд нужно организовать двухшаговую процедуру — при получении команды отправителю по другому каналу направляется код авторизации с определенным сроком действия, при получении этого кода в течении указанного срока первоначальная команда выполняется.
Команды должны допускать вариабельность в написании: команда может быть написана латинскими или русскими буквами (ЗАПРОС или ZAPROS), возможно смешанное использование букв, начертание которых выглядит одинаковым (например в команде ЗАПРОС буквы A и C могут быть латинскими), возможны разные варианты транслитерации (например PLATEZH или PLATEJ, YABLOKO или JABLOKO). У некоторых команд могут быть параметры, указываемые после пробела.
Отправитель сообщения может быть сопоставлен с пользователем моей информационной системы (например это клиент, номер телефона которого указан в качестве контактного).
У некоторых команд могут быть ограничения — например их можно использовать не чаще одного раза в месяц или они могут быть доступны только клиентам, входящим в определенную группу.
Робот в основном будет работать с SMS, но некоторые команды могут приходить по EMail.

Я на бумажке прикинул примерно такую структуру базы данных:
ПолеТипОписаниеidчислоСчетчик, идентификатор строкиtransportТранспорт сообщения (SMS, email)senderтекстОтправитель сообщения (номер телефона, электронный адрес)targetтекстПолучатель сообщения (номер телефона, электронный адрес)clockдатаДата и время сообщения (используются данные из сообщения)messageтекстТекст сообщения без обработкиreq_clockдатаДата и время получения сообщенияreq_userПользователь информационной системы, сопоставленный с senderreq_codeтекстПодтверждающий код авторизации для одной из предыдущих командreq_commandтекстРаспознанный запрос (нормализованный текст команды), null если команда не распознанаreq_paramsтекстДополнительные параметры запросаreq_repeat_countчислоЕсли за короткий период времени от отправителя пришло несколько одинаковых команд, в БД будет сохранена только первая команда и количество повторений, повторные команды игнорируютсяreq_repeat_lastдатаДата и время последней повторенной командыret_clockдатаДата и время обработки командыret_statusчислоРезультат обработки запроса (запрос игнорируется, запрос выполнен, запрос отклонен, отправлен код подтверждения)ret_codeчислоЧисловой код результата обработки и выполнения запросаret_messageтекстТекст сообщения, отправленного в ответ на запрос (null, если ответ не отправлялся)ret_auth_codeтекстКод авторизации, отправленный для подтверждения выполнения командыret_auth_expireдатаСрок действия кода авторизацииret_auth_linkчислоid строки, в которой пришел код авторизации (циклическая ссылка на себя, не потомок->родитель, а родитель->потомок)dlv_statusОтчет о доставке (ответ доставлен / не доставлен)dlv_clockДата доставки ответа
База данных не принципиальна, пока что будет использоваться MySQL.
Пока что все в одной таблице, но в реальной БД какие-то поля будут внешними ключами к отдельным таблицам.

Может посоветуете, что еще добавить?
К данной БД довольно часто будут запросы на выборку с фильтром по sender, req_clock, req_command, ret_status, не посоветуете, как лучше организовать индексы?

________________________
Мы смотрим с оптимизмом...
...в оптический прицел.

Справочники команд, пользователей, списков доступа (ограничений) само собой будут.

SERG1257Журнал шагов
Можно на примере, как это должно выглядеть?
Разве сгенерированного кода подтверждения со сроком его действия недостаточно?

SERG1257Таблица алиасов
Как-то я себе эту таблицу слабо представляю.
Что в ней должно быть, список всех мыслимых сочетаний?
Вообще-то я планировал, что нормализацию у меня будет делать скрипт робота.
Или имеется ввиду что-то другое?

SERG1257Проиндексировать поля
Это понятно.
Я хотел узнать, может быть есть какие-то универсальные рекомендации?
Или все зависит исключительно от выбранной СУБД?
Есть в SQL-запросе часто ожидается отбор по sender=<sender> and ret_status=<status> and req_clock>=<clock>, то есть ли смысл в составном индексе по этим полям?