Подсчёт ссылок на строку таблицы в MySQL

← →
ProgRAMmer Dimonych © (2012-10-04 00:21) [0]

Хочу странного.

Есть список компаний, каждая привязана к некоторому городу. Для хранения информации создаются 2 таблицы: Companies и Cities. У записей в Companies соответственно есть FK на записи из Cities. При добавлении компании указывается город (строка), к которому компания относится. Если такого города ещё нет, он добавляется в Cities. При удалении компании связанный с ней город не удаляется, т.к. он может использоваться для других компаний. Обычный "один-ко-многим".

Доп. ограничение
В пользовательском интерфейсе по ТЗ предполагается управление только списком компаний. Т.е. список городов должен поддерживаться автоматически.

-----------------------

Хочу реализовать подсчёт ссылок для каждой из записей в Cities и удалять, когда счётчик ссылок обнуляется.

Как это сделать красивее, стоит ли и какие ещё варианты можно использовать?

← →
Дмитрий С © (2012-10-04 00:25) [1]

индекс по Compaines.CityId
DELETE FROM Cities WHERE Id not in (SELECT DISTINCT CityId FROM Compaines)

Подсчет ссылок просто.
Update Cities set ref_count=ref_count+1 WHERE id=... при добавлении компании

Update Cities set ref_count=ref_count-1 WHERE id=... при удалении.

← →
ProgRAMmer Dimonych © (2012-10-04 00:52) [2]

Упс, дополняю вопрос.

Суть в чём: хранимка добавления компании намечается уж больно навороченной. Сначала INSERT/UPDATE, а потом ещё и SELECT оттуда же, чтобы получить IDшник. К тому же, DELETE с SELECT"ом тоже как-то тяжеловато, кажется, будет для запроса на удаление.

Хотелось бы как-то от этого избавиться. Потому что стандартными способами-то я это сделаю.

← →
Дмитрий С © (2012-10-04 00:55) [3]

> Сначала INSERT/UPDATE, а потом ещё и SELECT оттуда же, чтобы
> получить IDшник

зачем это?

> К тому же, DELETE с SELECT"ом тоже как-то тяжеловато, кажется,
> будет для запроса на удаление.

я думаю сервер сделает все быстро.

← →
sniknik © (2012-10-04 00:55) [4]

не стоит ничего удалять... вообще, ни города ни компании.
перестали с кем нибудь работать сделай его не активным/не свети в выборе, но в базе оставь. для отчетности пригодится...
или у вас бухгалтерия/... задним число ничего не печатает? ну там, сводный отчез за прошлый квартал. например.

← →
знайка (2012-10-04 00:59) [5]

Либо ичего не удалять, либо не удалять города (сегодня не надо, завтра уже надо).
Ну а счетчик ссылок совсем ни кчему. :)

← →
ProgRAMmer Dimonych © (2012-10-04 02:33) [6]

> [3] Дмитрий С © (04.10.12 00:55)
> > Сначала INSERT/UPDATE, а потом ещё и SELECT оттуда же, чтобы
> > получить IDшник
> зачем это?

Чтобы знать, какое значение для FK прописать у новой компании.

----------------------

Ориентируясь на

> [4] sniknik © (04.10.12 00:55)
> [5] знайка (04.10.12 00:59)

решил в первом прототипе оставить без удаления и подумать насчёт какого-нибудь решения покрасивее. Да и города там в отдельной таблице больше для того, чтобы лишнее место не жрать.

Ещё рекомендации приветствуются!

← →
Игорь Шевченко © (2012-10-04 10:00) [7]

> Хочу реализовать подсчёт ссылок для каждой из записей в
> Cities и удалять, когда счётчик ссылок обнуляется.
>
> Как это сделать красивее

не удалять вообще

← →
Дмитрий С © (2012-10-04 11:13) [8]

> Чтобы знать, какое значение для FK прописать у новой компании.

Так есть же специальная функция для этого.

← →
AV © (2012-10-04 12:24) [9]

> Хочу реализовать подсчёт ссылок для каждой из записей в Cities
Делать это следует запросом, по мере надобности.
обычный count - group by быстро скажет эти значения
Не следует писать в процедуре добавления компании в некое поле городов значение-счетчик ссылок. Потому что это избыточная ин-фа и есть риск потери актуальности, при добавлении компании не через процедуру

> и удалять, когда счётчик ссылок обнуляется.
собственно, сказали уже
+ имхо, следует вообще - не удалять никогда ничего

> Доп. ограничение
> В пользовательском интерфейсе по ТЗ предполагается управление
> только списком компаний. Т.е. список городов должен поддерживаться
> автоматически.

Плохо.
Представим, что

Набивает юзер в форму ввода
ООО"Рога" бла-бла г.С-Петербург
а в базе есть только г.Санкт-Петербург.
Программка решает, что появился новый город, добавляет его в БД.

Потом манагер строит отчет по Санкт-Петербургу и идет к начальнику, ругаться на программера, что отчет у него кривой и по Питеру ничего не сходится.

← →
картман © (2012-10-04 14:11) [10]

> + имхо, следует вообще - не удалять никогда ничего

это еще Гоголь в Коробочке вывел

← →
Лакки (2012-10-04 14:39) [11]

+1 Не удалять ни компании, ни города

← →
Palladin © (2012-10-04 15:13) [12]

хозяйкам на заметку, есть такая штука как ОКАТО

← →
Inovet © (2012-10-04 15:20) [13]

> [12] Palladin © (04.10.12 15:13)
> ОКАТО

В данном случае КЛАДР.
Ещё есть ОГРН

Только это всё внутри России.

← →
MsGuns © (2012-10-04 15:48) [14]

Сам сабж от темноты. Для того, чтобы не считать "сылки", в БД придуман Foreign Key (FK). Это во-первых.
Во-вторых, зачем нужен именно счетчик ? Вот есть две записи, в некоей таблице A, на одну есть одна ссылка из таблицы B, а на вторую - 10000 из таблицы C. А теперь вопрос - чем вторая запись "ценнее" первой в смысле того, стОит ли ее сберечь, а вторую "вычистить" ? А если нет разницы, то в чем сакральный смысл этих чисел - 1 и 10000 ?

← →
MsGuns © (2012-10-04 15:54) [15]

Вдогону.
Если автора волнует проблема "удаления лишних записей из базы" (именно такое или почти такое звучание имеет тема у апологетов АФА ибо так их учили), то пусть не волнует - проблемы с базами начинаются совсем в другой области.
Опыт 100% утверждает, что когда база "дорастет" до состояния "срочно чистить" ибо не влазит, то пректировщик будет уже настолько могуч и у него будет такой скил, что он шутя решит эту проблему :)
99,9999... % "самочистящихся" баз, разработанных новичками, так и исчезают в вакууме, не будучи востребованы никем, кроме самого "изобретателя" :)

← →
MsGuns © (2012-10-04 15:56) [16]

Не АФА, а ФАФ :)

← →
Игорь Шевченко © (2012-10-04 15:58) [17]

никто не мешает раз в необходимый интервал времени выполнять запрос

DELETE FROM cities c WHERE NOT EXIST (SELECT NULL FROM companies cmp WHERE cmp.city = c.city)

или аналогичный

Но все равно это лишняя работа

← →
Ega23 © (2012-10-04 16:04) [18]

Не удалять вообше.
Но если так сильно хочется, то повесить на таблицу Companies триггер на удаление. И там уже проверять, сколько в Companies записей с таким cityid. Если 1 (как раз удаляемая), то грохать и запись и город. Если больше 1, то грохать только запись.

← →
Inovet © (2012-10-04 16:11) [19]

А через час вводим новую компанию из удалённого города и новый город, причём ошибёмся в одной букве.

← →
Jeer © (2012-10-04 16:14) [20]

Справочник городов должен содержать все, вплоть до "муравейника" :)

← →
Inovet © (2012-10-04 16:19) [21]

> [20] Jeer © (04.10.12 16:14)
> Справочник городов должен содержать все, вплоть до "муравейника":)

Собственно городов не так уж и много - несколько тысяч, а вот населённых пунктов райнного и городского подчинения уже раз в 100 больше.

← →
Ega23 © (2012-10-04 16:33) [22]

> А через час вводим новую компанию из удалённого города и
> новый город, причём ошибёмся в одной букве.

Можно эвристику прикрутить, то же "расстояние Левенштейна".
Можно выпадающий список подсказок давать при вводе каждой буквы (ну, типа как у гугла).
Можно совместить первое и второе.
Можно придумать ещё что-нибудь.

← →
AV © (2012-10-04 16:36) [23]

мы КЛАДР вводили - не прижилось. Слишком много там всего.
Сделали проще
Если вводят то, чего нет, то ищется ближайшее по написанию (через Сфинкс)
Потом уточнение, не это ли хотели. Если нет - добавляется.

и у всех есть поле IsDeleted datetime
т.е. where A.IsDelete <, =, > [ЧасЧ]
и все, телемаркет :)
будут данные до удаления | после удаления
с учетом удаления

А потом, если компания придет, ее заново заводить?
Нет, занулить IsDeleted и всех дел

← →
AV © (2012-10-04 16:40) [24]

> Можно эвристику прикрутить, то же "расстояние Левенштейна".
>

Делал, но прикинь, пишут
"Нкатиринбург"
т.е. промахнулись в первой букве
А ты уже ушел в БД на букву Н искать/перебирать..
Это если по мере ввода делать, конечно.

Сфинкс под Oracle неплохо работает.
Хотя, в принципе, там ничего нового, думаю, не открыто.
Он просто переидексирует вдоль и поперек все. Такое мнение сложилось, во всяком

← →
Ega23 © (2012-10-04 16:43) [25]

> Делал, но прикинь, пишут
> "Нкатиринбург"

Левенштейн как раз тут поможет

← →
Ega23 © (2012-10-04 16:44) [26]

> Сфинкс под Oracle неплохо работает.
> Хотя, в принципе, там ничего нового, думаю, не открыто.
> Он просто переидексирует вдоль и поперек все.

Ну да, именно так.

← →
Inovet © (2012-10-04 16:46) [27]

> [22] Ega23 © (04.10.12 16:33)

Город уже удалён, так что вводим его заново и с ошибкой. А без удаления был бы более-менее безошибочный справочник, поскольку уже несколько раз город встречался и ошибку при первом вводе потом заметили и исправили, но зачем-то исправленный удалили.

← →
Ega23 © (2012-10-04 16:47) [28]

> Город уже удалён, так что вводим его заново и с ошибкой.

Не надо удалять, я же писал. Тем более, что работа со справочником автоматически ведётся.

> [24] AV © (04.10.12 16:40)
> "Нкатиринбург"
> т.е. промахнулись в первой букве

в первой промахнулись в пятой лажанулись.

> в пятой лажанулись.

:(
да...

> Левенштейн как раз тут поможет

не совсем
для него нужен образец для сравнения. Или хотя бы что бы образец попал в порцию, которую ты взял к анализу
А тут ты уже сместился далеко от правильных образцов.

Абакан
Алма-Ата
... еще 5000 городов
Киров
...еще 5000 городов

Хотят ввести Анапа, промахиваются, "Кнапа"

а ты уже забрал в выборку все города на К и гоняешь по Левенштейну их с введенным. Нет результата. Вернее, дистанция больше нормы с любым.

Ты же не будешь брать весь справочник, и тупо по списку, к каждой строке применять функцию.

Можно попробовать брать union по первым 3м буквам, но это много будет.
т.е. where [первая буква] = "К" union where [2ая буква] = "п" и т.п.
-----------
зы
Я так думаю, что Сфинкс настроит хешей по-разному, в разных вариантах
и потом введенное накладывает
кто точнее наложился на все варианты - тому и релевантность больше присваивает

> Набивает юзер в форму ввода
> ООО"Рога" бла-бла г.С-Петербург
> а в базе есть только г.Санкт-Петербург.
> Программка решает, что появился новый город, добавляет его
> в БД.

Это лечится использованием географических справочников и запретом ввода населенных пунктов пользователем
Ну или курить soundex и иже с ним (выше примеры есть) - алгоритмы сравнения.

> Павел Калугин © (04.10.12 17:08) [32]

soundex для русского языка? А где это сделано?
Сейчас мне разрабы Сфинкса уже должны премию дать :), но вот в нем что-то подобное используется. Но опять же, имхо, не совсем sound :)
Дурят нашего брата(такой он у меня, брат то :)).
имхо, все равно, написание (хоть и с ошибками ) подгоняется под правильный вариант

А теперь реклама.
Лилипутия и Блефуску! Каждый день на дельфимастер!

Тут еще надо разруливать конкурирующие транзакции от разных пользователей...
Есть компания K1 привязанная к городу Г.
Пользователь П1 пытается добавить К2 с тем же городом Г.
Проверяет, что Г есть, соответственно можно добавить только К2 без изменения таблицы городов.
В этот момент П2 удаляет К1 и Г (так как пока К2 еще не добавлен).
Теперь вставляем К2 (город Г не добавляем, т.к. помним, что он уже есть).
В результате имеем К2 привязанную к несуществующему Г.

Можно это, конечно, разрулить блокировками например (с MySQL дел не имел, поэтому не знай как у них там). Только нафига блокировки и их разруливание вешать на операции вставки?

Имхо мое тут такое (уже озвучивалось) - не удалять города.

> soundex для русского языка? А где это сделано?

Не встречал. :) Но описание алгоритма попадалось, если память не изменяет.

> Тут еще надо разруливать конкурирующие транзакции от разных
> пользователей...

Удаление компании и города, естественно, должно идти в одной транзакции.

> Ega23 © (04.10.12 19:11) [37]

> Удаление компании и города, естественно, должно идти в одной
> транзакции

Естественно.
Но, в том-то и дело, что на момент проверки второй транзакцией, а есть ли город, результат будет положительный. Хотя первая транзакция, его удаляющая, уже стартовала, но не закоммитилась...

> Но, в том-то и дело, что на момент проверки второй транзакцией, а есть ли город, результат будет положительный.
> Хотя первая транзакция, его удаляющая, уже стартовала, но не закоммитилась...
какие то у тебя странные представления о транзакциях...

> sniknik © (04.10.12 20:00) [39]

> какие то у тебя странные представления о транзакциях...

С чего бы? Ну, вот пример (изначально "My Gorod" существует):

T1: START TRANSACTION
T2: START TRANSACTION
T1: DELETE FROM GORODA WHERE NAME="My Gorod"
T2: SELECT COUNT(*) FROM GORODA WHERE NAME="My Gorod"
T1: COMMIT
T2: COMMIT

Че вернет SELECT? За исключением DurtyRead типа транзакций (этот тип, кстати, не всеми серверами поддерживается)?

Подсчёт ссылок на строку таблицы в MySQL Найти похожие ветки