orbit070

Ограничение уникальности накладывается на поля в рамках одного отношения, то есть то что вам хочется провернуть, а именно заглянуть как-то в другую табу чтобы проверить уникальность, из коробки такой возможности нет. Два наиболее очевидных способа решения задачи:
1) Прокинуть city_id в нужную таблицу. Да, не по феньшую, но реальность такая что нормализованные базы данных существуют только в книгах по базам данных или во всяких простячковых проектах, добро пожаловать.
2) Оставить все по феньшую и просто повесить триггер, в котором и будет происходить проверка уникальности по городу.

По сути миллион статей и книг по бд где есть примеры и описания, но я сейчас так вспоминаю что с нуля так сходу сложновато понять что к чему будет. Если есть возможность то легче попросить или нанять кого-то кто за пару часов объяснит покажет и расскажет что к чему. Конечно и самому можно разобраться, но займет это не пару часов и даже не пару дней. Но если хотите самостоятельно то любую книгу по базам данных берите там везде очень детально объясняется что к чему, просто это информация из серии "прочитал, все понял, но сам сделать не смогу", поэтому лучше попросить помощи

Таблица Рейтинг постов вида (post_id, user_id).
Таблица Рейтинг комментов вида (comment_id, user_id).
Таблица комментов вида (post_id, comment_id).

Для того чтобы при каждом обращении не считать с нуля рейтинг делается элементарная штука: в таблице Постов заводите дополнительное поле Рейтинг. И каждой раз когда кто-то ставит плюс(ну или минус) помимо добавления(или удаления) записи в таблицу Рейтинг постов вы так же увеличиваете(или уменьшаете) поле Рейтинг в таблице постов. Аналогично заводите такое же поле Рейтинг в таблице комментариев и при каждом плюсе/минусе комментария помимо добавления в таблице Рейтинг комментариев увеличиваете(уменьшаете) счетчик-поле рейтинг в самой таблице Комментарии

Вопрос конечно каша, но давайте попробуем:
1) Никаких двух бд не надо
2) Для пользователей создается таблица например Users, в которой хранятся почта, хеш пароля, имя, и т.д, то есть информация об аккаунте
3) Для персонажей игры создается отдельная таблица например Heroes. В ней храните уже информацию о персонаже игры и поле user_id, чтобы было понятно какому пользователю принадлежит персонаж.
4) Все остальные таблицы вроде Инвентарь и прочее должны ссылаться на таблицу Heroes и хранить hero_id.
5) Профит

На вопрос невозможно дать однозначный ответ. Все перечисленные способы имеют право на жизнь в тех или иных ситуациях, но:
1) Я бы не советовал захламлять излишне таблицу user, получите огромную кашу.
2) Json я бы рекомендовал только в исключительном случае, все-таки это уход от нормальных форм.
3) Наиболее каноничным является вынесение настроек в отдельную таблицу вида user_settings(user_id, setting1, setting2, ...).

Касательно уведомлений - так как уведомления собой представляют чаще набор, то каждое отдельное уведомление вписывать в таблицу с настройками конечно можно и удобно, но не совсем по феньшую. Если пунктов уведомлений не так много, то можно их вписать прям в таблицу настроек, то есть вроде этого user_settings(user_id, setting1, setting2, ..., notification1, notification2, ...). Но этот вариант не является гибким, потому что к примеру для каждого отдельного уведомления могут в дальнейшем потребовать настройки(например приоритет уведомления). Поэтому наиболее правильным будет завести одну таблицу для настроек профиля как указано выше, а вторую таблицу для уведомлений вида user_notifications(user_id, notification_id, notification_name, priority, ...). Так все будет очень гибко и будет соответсвовать нормальным формам.

Если, чисто теоретически представить, что пользователей миллионы, а постов десятки миллионов, является ли такая структура оптимальной?

Почти. Нужно применить дублирование и прокинуть в эту таблицу сразу все те поля, которые вам могут пригодиться для отображения, иначе каждый раз придется делать join, чего бы не хотелось при highload. То есть нужно добавить в таблицу сразу поля вроде user_name, post_title, post_body, и т.д(в общем все то, что вы планировали доставать с помощью join).

На счет "пользователей миллионы, а постов десятки миллионов":
Если у вас будет такое количество данных, то вам в любом случае в какой-то момент придется прибегнуть к горизонтальному шардингу, поэтому если считаете что проект реально может дорасти до такого количества данных лучше сразу учесть это и спроектировать базу данных так, чтобы горизонтальный шардинг не стал проблемой.

нужно ли тут поле id, или PK сделать составной (post_id, user_id) или PK вообще не нужен? Это влияет на селект?

Зависит от сценариев использования(подумайте, в каком случае вам нужно будет поле id), но в большинстве случаев оно не нужно и такие поля вводят для душевного спокойствия и гармонии. На селект это не влияет, ведь все равно вы будете делать выборку либо по user_id либо по post_id(опять же, это в большинстве распространенных сценариев, если у вас есть какая-то логика, где нужно будет выбирать из таблицы likes записи по какому-то намеренно введенному идентификтаору, то вводите).