Какая структура БД оптимальна в данном случае?

Question

[salacoste]

Доброго времени суток, друзья.

Наконец добрался до рассылки. На текущий момент у меня есть БД->основная таблица со структурой вида
ID
Имя
Статус
Регион
и другие мелкие поля

Мне нужно добавить рассылку писем по выбранным лицам (выбирает пользователь). Как лучше в таком случае организовать таблицу для хранения записей о выборе рассылки.

Что предлагается пользователю для выбора рассылки:

Край или область (например есть 5 регионов, выбрать может сразу несколько).
В каждом регионе есть порядка 5-6 городов (выбрать так же может несколько).
Таким образом к одной записе в основной таблице может коррелироваться выборка из 2х регионов + 3-4 городов.
К каждому городу или региону привязана своя рассылка и в зависимости от совокупной выборки определяется список рассылки.

Мне не до конца понятна оптимальная структура хранения выборки, которая коррелируется по ID к записи в основной таблице. Посоветуете?

Answer 1

[Александр Кубинцев]

Чисто абстрактно в общем виде у вас иерархическая структура данных по локациям. Если решать в лоб, то можно сделать таблицу locations на все виды локаций с полями parent_id, name и location_type_id. Однако в случае с MySQL это создаст геморрой с рекурсивными запросами.
Поэтому можно немного упростить реализацию благодаря небольшой глубине вложенности, введя отношение 1 ко многим для каждого уровня локаций. Правда в случае нескольких государств иерархическая структура может отличаться и это уже не будет красивым решением.
А для РФ мы имеем:
- города/села/деревни/ПГТ входят в область
- области входят в федеральный округ
- округи входят в состав федерации

Следовательно задача по выборке локаций из родительской сведётся к запросу вида:

SELECT t.* FROM towns AS t
JOIN regions AS r on r.id = t.region_id
JOIN state AS s on s.id = r.state_id
WHERE t.id IN (...) OR r.id IN (...) OR s.id IN (...)