JSinga

select d.subject, count( mv.message_id) from users u
inner join dialogs_users du on ( u.id = du.user_id)
inner join dialogs d on ( d.id = du.dialog_id)
inner join dialog_messages dm on ( dm.dialog_id = d.id and dm.user_id = u.id)
inner join message_views mv on ( mv.message_id = dm.id and mv.user_id = u.id)
where
u.id = ?
Having count( mv.message_id) > 0 --can delete in our case

Вместо вопроса подставлять нужный id user - а. Если нужно всех то просто уберите последнюю строку и добавьте вывод юзера в select.
Я склепал ответ из двух полей первое это тема диалога, 2 кол-во непрочтенных в этом диалоге, по сути должно работать хоть и выглядит жутко:)

Agile Web Development with Rails

Даже если не пишите на рельсах советую полистать книгу( можно найти и на русском)
там как раз во 2 части по шагам расписано создание интернет магазина, и структура БД там проектировалась отлично.

Это если надо быстро.
Если будете продолжать создавать БД в будущем то лучше прочесть доп еще литературу.

К ваше структуре слишком много вопросов. Хочется все переделать, и привести к виду как описано в книге выше.