imho.ws |
|
29.09.2007, 18:59 | # 1 |
Full Member
Регистрация: 25.11.2001
Адрес: Imho.ws
Сообщения: 941
|
MySQL: уникальный индекс в 1:n
Есть 2 таблицы, которые надо связать. В А должна быть ссылка (ид) от Б чтобы можно было перейти. А - одна на много Б.
Связываю через 3ю таблицу АБ, куда идут уникальные id из Б (B_id) и где есть индекс который записывается в А (AB_id). AB_пример: AB_id: B_id 1 2 1 3 1 2 2 5 2 2 2 3 Таким образом идет много значений B_id на одно значение AB_id, где последний повторяется много раз. Ни один из них не являетс уникальным, AB_id так как повторяется много раз, B_id так как их может быть много одинаковых. Выборка будет идти тогда так: Прочитал А, взял AB_id. По AB_id нашел в таблице AB все ключи для B. Прочитал все B. TABLE A A_id AB_id ... TABLE AB AB_id B_id TABLE B B_id ... ... Вопрос: как сгенерировать ключ AB_id. Автогенерация не поможет, так как будет его увеличивать. Было бы логично A_id, но ключ A_id является ключем к "внешнему" миру, это 13-23 значное число из uniqid (php). Получается многова-то по длине. Значит остается только создать для этой цели отдельный элемент в А и автоматичеси увеличивать его специально чтобы искать AB?
__________________
Когда умираешь, да еще так долго и трудно, очень хочется хоть немного насолить живым, просто невозможно удержаться от искушения! М.Фрай |
29.09.2007, 19:58 | # 3 |
Full Member
Регистрация: 25.11.2001
Адрес: Imho.ws
Сообщения: 941
|
9:
1x varchar (23) 3x INT 4x MEDIUMINT 1x TINYINT ну и с этим будет тогда 5 mediumint Эмм.. на всякий. На одну А приходится от 1 до 180 (экстремальный вариант) B. В среднем можно считать порядка 6 - 18.
__________________
Когда умираешь, да еще так долго и трудно, очень хочется хоть немного насолить живым, просто невозможно удержаться от искушения! М.Фрай Последний раз редактировалось Melkor; 29.09.2007 в 20:03. |
29.09.2007, 21:08 | # 4 |
::VIP::
Регистрация: 12.11.2002
Адрес: Nicosia, Cyprus
Сообщения: 1 285
|
да не колонок, а записей. Рядов. Rows. Размер таблицы, в общем.
Да, и второй вопрос - в чём проблема с упомянутым выше "автоматическим увеличением AB_id"? Тебе нужен уникальный ключ меньшего размера, нежели символьный хеш - автоинкрементная колонка тебе его и даст.
__________________
"If people only knew how hard I work to gain my mastery, it wouldn't seem so wonderful at all." Michelangelo Buonarroti Последний раз редактировалось Saruman; 29.09.2007 в 21:18. |
29.09.2007, 22:09 | # 5 |
Full Member
Регистрация: 25.11.2001
Адрес: Imho.ws
Сообщения: 941
|
На счет размера то А планируется несколько 100к.
Мне нужен способ адресовать из таблицы А все Б. Причем Б на А несколько (от 1 до 180). Поэтому я создаю АБ (спец для этого), где куча Б ассоциируется с одним АБ. Если я буду использовать автоиндекс в АБ то получится каждому Б свой АБ. Смысла нет. С другой стороны если я делаю один индекс для АБ в А, то я могу использовать автоматическое увеличение и потом записывать значение в АБ. Мне просто интересно, есть ли другой способ. Хотя наверное пойду этим путем.. Вот пример как это выглядит: tabl A: A_id: 1 AB_id: 11 <- пишем отсюда в AB.AB_id, auto_increasement ... tabl AB: AB_id: 11 -> AB_id из А.id B_id: 1 -> Из B.id 11:2 -> AB_id:B_id 11:3 11:4 ... куча разных значений из Б, индексируются по B_id auto_incr tabl B B_id
__________________
Когда умираешь, да еще так долго и трудно, очень хочется хоть немного насолить живым, просто невозможно удержаться от искушения! М.Фрай Последний раз редактировалось Melkor; 29.09.2007 в 22:15. |
29.09.2007, 23:08 | # 6 |
::VIP::
Регистрация: 12.11.2002
Адрес: Nicosia, Cyprus
Сообщения: 1 285
|
так и есть, берёшь первичые ключи из обеих таблиц и делаешь ещё одну таблицу отображения одних на другие - в твоём случае АБ. Ессно, всё должно быть покрыто индексами.
Если бы А была небольшого размера - то можно было с автоинкрементом не заморачиваться, просто создать индекс поверх хеша и его же использоваться как ключ в АБ, но на больших таблицах лучше ввести дополнительную integer колонку для меньшего размера индекса. PS: А и Б связаны как 1*n, или же m*n? Т.е. что каждой записи из А соответственно >1 записи из Б, я уже понял - а в обратную сторону? Одной записи из Б может соответствовать только одна А или несколько?
__________________
"If people only knew how hard I work to gain my mastery, it wouldn't seem so wonderful at all." Michelangelo Buonarroti |
29.09.2007, 23:50 | # 7 | |
Full Member
Регистрация: 25.11.2001
Адрес: Imho.ws
Сообщения: 941
|
Цитата:
Спасибо за пояснение Еще у меня есть небольшой вопрос немножко по другому поводу. В таблицу часто разными пользователями пишется часто повторяющаяся информация. По-моему тут лучше будет сначала проверять есть ли уже такая запись и если есть то давать ее id, если нет то записывать ее. Единственное что смущает это количество запросов, сначала прийдется делать SELECT.. а потом INSERT если ничего не найдено. Единственное решение которое я вижу это INSERT ... ON DUPLICATE KEY UPDATE[FONT=verdana,geneva,lucida,'lucida grande',arial,helvetica,sans-serif] но ему нужен уникальный индекс, а в моем случае это будет все что пишется. Таким бы мог стать md5 или crc32, но они по длине 1/3 всех данных.[/FONT] Имеет ли это смысл? Или лучше 2 запроса?
__________________
Когда умираешь, да еще так долго и трудно, очень хочется хоть немного насолить живым, просто невозможно удержаться от искушения! М.Фрай |
|
30.09.2007, 00:45 | # 8 | ||
::VIP::
Регистрация: 12.11.2002
Адрес: Nicosia, Cyprus
Сообщения: 1 285
|
Цитата:
Цитата:
Если же на самом деле это набор небольшого количества мелких данных - то, как вариант, покрыть их UNIQUE индексом и использовать ON DUPLICATE KEY UPDATE. В общем, подробнее про данные и таблицу - тогда можно будет более предметно обсуждать.
__________________
"If people only knew how hard I work to gain my mastery, it wouldn't seem so wonderful at all." Michelangelo Buonarroti Последний раз редактировалось Saruman; 30.09.2007 в 00:48. |
||
30.09.2007, 01:40 | # 9 | |
Full Member
Регистрация: 25.11.2001
Адрес: Imho.ws
Сообщения: 941
|
Цитата:
Но при этом много А могут ссылаться на одно Б. Если брать второй вопрос (хотелось бы это объединить в одну систему) то каждое А проверяет есть ли Б и если есть то берет только его ID, на создавая нового Б. Б - описание самой информации что закладывается в БД. В итоге получается что если добавляется уже существующая информация то новое Б не создается, просто берется его id и записывается в А (через АБ конечно). Если меняется одно Б то таким образом оно меняется везде где упоминается. Ну и заобно облегчается контроль количество каждого Б , увеличивать просто счетчик на 1 с помощью АПДЕЙТ. На этом приходим ко второму вопросу, как эту фигню туда записать. Каждое значение в Б может встречаться во всей таблице дофига раз, но комбинация всех значений только 1. Тут больше похоже на проблему скорости/места на диске. В моем случае скорость не очень важна. Вот тут пришел на ум пример, записать синусоиду допустим из 50 точек на период. Можно просто записывать, быстро и сердито. Время - значение. А можно брать по несколько точек за раз и присвоить им Id. При записи искать встречается ли эта комбинация и если встречается давать ссылку на нее. Получаем в первом случае 50 записей типа: время - значение во втором (10 точек за раз) 5 записей типа: время - линк на таблицу значений точек. Что-то подобное второму мне бы и хотелось.
__________________
Когда умираешь, да еще так долго и трудно, очень хочется хоть немного насолить живым, просто невозможно удержаться от искушения! М.Фрай Последний раз редактировалось Melkor; 30.09.2007 в 01:46. |
|
30.09.2007, 12:12 | # 10 | |||
::VIP::
Регистрация: 12.11.2002
Адрес: Nicosia, Cyprus
Сообщения: 1 285
|
Цитата:
Цитата:
а) искусственный ключ; б) UNIQUE-индекс поверх всех колонок из Б. В первом случае ты считаешь хеш от конкатенации всех полей Б и хранишь его отдельной колонкой. Делаешь индекс поверх этой колонки и при необходимости проверить, если ли определённая запись уже в это таблице, считаешь опять же хеш и ищешь хеш. Во втором случае ты просто требуешь уникальности сочетания значений Б - и, соответственно, при поиске просто перечисляешь все эти значения. Преимущества и недостатки, полагаю, очевидны. Первый способ даёт избыточные вычисления, но уменьшает число колонок в индексе до одной с ограниченной длиной. Если у тебя в таблице Б есть колонки типа BLOB или TEXT - то следует заюзать хеш. Второй же способ более пригоден в случае, если у тебя в Б набор небольших данных (к примеру, несколько INTEGERов, и т.п.) - тогда расходоваться ещё и 32-байтовый хеш смысла нет, просто покрывай их все UNIQUE-индексом и ищи по сочетанию значений. Цитата:
__________________
"If people only knew how hard I work to gain my mastery, it wouldn't seem so wonderful at all." Michelangelo Buonarroti |
|||
03.10.2007, 00:21 | # 11 |
::VIP::
Регистрация: 28.06.2002
Адрес: neverwhere
Сообщения: 1 166
|
Я бы делал уникальный индекс на все колонки определяющие Б. Он для этого и существует. Если же вопрос в скорости, я бы исследовал возможность дальнейшей нормализации Б - когда "значения могут повторяться дофига раз", это знак что их пора вытаскивать в отдельную таблицу. Побольше бы информации о данных в таблице Б (а ещё лучше структура таблицы и пример ряда).
Последний раз редактировалось Aeon; 03.10.2007 в 00:30. Причина: долбаный транслит |
05.10.2007, 00:54 | # 12 |
Full Member
Регистрация: 25.11.2001
Адрес: Imho.ws
Сообщения: 941
|
Извините что пропал, но это я ковыряю в свободное от остального время
Сделал Б с уникальным индексом по всему Б и через ON DUPLICATE KEY UPDATE. Протестил, вроде работает как надо. 2 Aeon Б - самый нижний слой базы. Там только информация (числа) и индекс который увеличивается. Каждое значение в Б уникальное (комбинация). Вся остальная база обращается только к индексу. Сейчас пытаюсь нарисовать структуру в этом. Общая цель чтобы если дается та инфа что уже в таблице есть то вытащить пользователю просто линк на нее из А. Насколько я понял это норма 1. В этом и заключается значение А. Там только индексы на Б и подобные, время (уникальный ключ по всем). Кроме того есть обычный индекс с увеличением и линк (23х значное число, для линка пользователю). Индекс чтобы не таскать это здоровое (медиуминт). А и Б скорее всего как и предложил Saruman m:n. Но у меня не получается вытаскивать уже существующий линк тогда. Если я иду с низу (Б), то когда я подхожу к свойной таблице АБ у меня есть все что нужно чтобы писать в нее от Б. Но у меня нет индекса из А. Писать в А я не могу так как у меня нет значения для Б. В 'А' я должен записать уникальное число из АБ которое будет вести на много уникальных значений Б. Таблица АБ выглядит как в посте от 29.09.2007, 20:09. При этом композитный ключ будет уникальным (AB_id, B_id). В общем проблема найти (если существует то не создавать новый, а дать id) или создать в АБ ключ для А (а не наоборот ) Найти А сложно, он не уникальный в АБ, и их непостоянное количество. Тогда можно попробовать сделать через хэш. Вариант Б от Saruman Можно это сделать через таблицу куда писать хэш значений Б. Если хэш даст коллизию то будет плохо. Не знаю насколько быстро работает md5, но в худшем случае туда пойдет 180 интов. Если писать новый А каждый раз то все работает хорошо, но будет создаваться новый линк. Off: Я понимаю что вопросов много , но я нашел 2 книги и в них почти ничего нет именно про дизайн дб. По 1му примеру похожему на пример с манов мускула в сети. А больше спрашивать негде .
__________________
Когда умираешь, да еще так долго и трудно, очень хочется хоть немного насолить живым, просто невозможно удержаться от искушения! М.Фрай |
05.10.2007, 01:59 | # 13 | |
::VIP::
Регистрация: 12.11.2002
Адрес: Nicosia, Cyprus
Сообщения: 1 285
|
лол, да я как бы не предлагал, а так понял структуру твоей БД на основе твоих же описаний.
Цитата:
Давай ты немного упростишь для примеру задачу и по шагам распишешь, что у тебя есть на входе, и что нужно найти/получить - потому что не знаю, как коллега Aeon, а я лично из последнего поста практически ничего не понял - откуда берётся, что берётся, почему А нельзя найти, по каким признакам он должен быть найден, и т.п.
__________________
"If people only knew how hard I work to gain my mastery, it wouldn't seem so wonderful at all." Michelangelo Buonarroti |
|
05.10.2007, 02:23 | # 14 |
::VIP::
Регистрация: 28.06.2002
Адрес: neverwhere
Сообщения: 1 166
|
Да, мне тоже немного трудно разобраться что с какого края идёт. Кроме того, обозначать их А и Б особо пониманию не помогает... Скажи уж что в них живёт, в таблицах этих твоих. Мне это напоминает таблицу продуктов и заказов, скажем –
продукты: ид, название, цена 1, фигня, 100 2, фиговина, 200 заказы: ид, клиент, адрес 1, "Ваня Буркалов", "дом 200 деревня Буркалы" 2, "Вася Пупкин", "дом 1, деревня Пупково" продукты–в–заказах: заказ, продукт, количество 1, 1, 3 1, 2, 1 2, 1, 1 (Вообще–то клиенты бы жили в отдельной таблице, но бог с ним, пример ясен). Ну и вот... делаешь уникальный индекс на поля (заказ,продукт) в таблице продукты–в–заказах (бо один продукт может быть не более одного раза в заказе), и всё. Ну а когда добавляешь продукт в заказ, проверяешь есть ли он в таблице продукты–в–заказах, и если есть, увеличиваешь количество вместо того чтобы добавлять новый ряд. Давай больше инфы, а то я больше отсебятину гнать не могу не зная вообще в те ли дебри лезу с которыми у тебя проблемы. |
05.10.2007, 02:39 | # 15 |
Full Member
Регистрация: 25.11.2001
Адрес: Imho.ws
Сообщения: 941
|
Да, сорри. Вот как это выглядит (аттач).
Папка с голубыми значками - уникальные ключи (что под папкой) Все что с 'FK' создано програмой, не правильно. Все что с _id это уникальные ключи которые передаются. В таблицах basic находится сама инфа. Каждой комбинации присваивается свой ID, комбинация повторяется в таблице только 1 раз (уникальная). ID из basic идет в sample. Там они пишется порядок в котором они идут относительно другим в этот раз. Сюда идет произвольное количесов basic. Комбиниция всех значений тоже уникальная. В hash идет хэш (мд5) всех значений из sample. md5(sample_1.sample_2... sample_n). Им присваивается индекс hash_id. Сюда прога добавила кучу других индексов, но тут должно их быть только 2 (hash_id и хэш всех значений sample). Комбинация этих 2х уникальная. В navtable идет hash_id и подобные. Комбинация как вы уже догадались должна получиться уникальная Как происходит запись: записать в basic (да и везде) INSERT... ON DUPLICATE KEY UPDATE. Получить последний вставленный id. При этом используется id уже существующейкомбинации если она существует. каждое значение basic_1, basic_2 получают свой порядковый номер в sample. Опять же если такое уже есть то берется только id. Так как комбинаций несколько то берется их хэш. Этот хэш является sample_hash в таблице hash. Тут он получает hash_id и еще несколько вещей (не рисовал). Используется существующий ид если есть. Можно так же писать сам хэш сразу в navtable. Hash_id идет в navtable, где получает дату, линк наружу и еще парочку подобных ключей. Все что с _id = primary key not null auto_increasement. Таким образом если внутрь идет уже существующая комбинация несколько раз то наружу для всех них будет только один линк. Если внутрь идет та же что-то из уже существующего (допустим просто в другом порядке) то создается только новая navtable и hash (таблица, да и то не всегда). Будет такое работать? ))
__________________
Когда умираешь, да еще так долго и трудно, очень хочется хоть немного насолить живым, просто невозможно удержаться от искушения! М.Фрай Последний раз редактировалось Melkor; 05.10.2007 в 04:17. Причина: дописал. |
05.10.2007, 10:50 | # 16 |
::VIP::
Регистрация: 12.11.2002
Адрес: Nicosia, Cyprus
Сообщения: 1 285
|
"будет, но только под себя"
А если серьёзно - то твоя проблема в том, что ты для ряда из таблицы hash не сможешь быстро получить соответствующий ему ряд из таблицы sample. Т.к. в sample у тебя всего две колонки mediumint, то я предлагаю не париться с хешами, сделать UNIQUE(basic2_b1_id, basic2_b2_id) и в таблице hash заменить искусственный и, на мой взгляд, совершенно ненужный sample_hash_2 на sample_id. Тогда, делая выборку по hash_id из таблицы hash, ты сразу получаешь sample_id. И, наоборот, имея sample_id - делаешь выборку из hash и получаешь нужные hash_id. Также непонятно, зачем sample_hash_2 присутствует в таблице navtable, если таблица hash осуществляет отображение many-to-many.
__________________
"If people only knew how hard I work to gain my mastery, it wouldn't seem so wonderful at all." Michelangelo Buonarroti |
05.10.2007, 17:06 | # 17 | ||
Full Member
Регистрация: 25.11.2001
Адрес: Imho.ws
Сообщения: 941
|
Цитата:
sample_hash_2 присутствует по непонятной мне причине Програма сама тянет все индексы из пред. таблицы. и фиг ей объяснишь что они там не нужны Цитата:
Если делать так то получится настоящий 1:n. На одно значение hash много sample, но тогда очень сложно писать, так как я хочу проверять есть ли уже такое в базе. То есть имея 15шт. sample_id мне нужно найти им hash_id если такой уже есть. Причем этот же hash_id не должен соответствовать допустим 20ти sample_id. В случае с хэшем всем sample соответствует только 1 hash. Но тогда не прочитать . Из плюсов так же что сколько бы sample у меня не было значений в sample им соответствует на 32 числа. То есть получается ситуация когда я могу сделать так, чтобы было удобно писать, было удобно читать, но не то и другое. В идеале тогда получается что таблица hsh должна выглядеть так для одного hash_id: table hash hash_id : sample_id: sample_hash (md5(sample_1.sample_2... sample_n).) 3 : 01 : 677878345767 3 : 06 : 677878345767 3 : 92 : 677878345767 3 : 01 : 677878345767 3 : 01 : 677878345767 тогда при чтении я ищу по hash_id(или по sample_hash), а когда читаю ищу hash_id по sample_hash. Очень похоже не то что ты предложил.
__________________
Когда умираешь, да еще так долго и трудно, очень хочется хоть немного насолить живым, просто невозможно удержаться от искушения! М.Фрай Последний раз редактировалось Melkor; 05.10.2007 в 17:18. |
||
05.10.2007, 19:34 | # 18 | |
::VIP::
Регистрация: 12.11.2002
Адрес: Nicosia, Cyprus
Сообщения: 1 285
|
Цитата:
Соответственно ключ поверх этой таблицы простой - PRIMARY KEY (hash_id, sample_id). Нужно получить все id записей из sample, соответствующие одной из записей в navtable - SELECT sample_id FROM hash WHERE hash_id=XXX. Нужно, наоборот, получить все записи из navtable для определённого sample_id - SELECT hash_id FROM hash WHERE sample_id=XXX. И ВСЁ - к чему ещё какие-то сложности? имея sample_id - выдёргиваешь уже конечные значения из basicX.
__________________
"If people only knew how hard I work to gain my mastery, it wouldn't seem so wonderful at all." Michelangelo Buonarroti |
|
06.10.2007, 14:11 | # 19 | |
Full Member
Регистрация: 25.11.2001
Адрес: Imho.ws
Сообщения: 941
|
Цитата:
Пример постом выше. Просто когда я пишу в hash у меня еще нет navtable чтобы искать SELECT hash_id FROM hash WHERE sample_id=XXX поэтому мне надо какую-нибудь уникальную запись из всех sample, почему такой не может быть хэш всех sample_id? Берем пустую строку и добавляем в конец ее sample_id, потом в конец этого добавляем следующий sample_id. В конце получится длинноечисло которое можно запихать в md5(). Оно же будет уникальным.
__________________
Когда умираешь, да еще так долго и трудно, очень хочется хоть немного насолить живым, просто невозможно удержаться от искушения! М.Фрай |
|
06.10.2007, 14:44 | # 20 | |
::VIP::
Регистрация: 12.11.2002
Адрес: Nicosia, Cyprus
Сообщения: 1 285
|
Цитата:
Ещё раз говорю - я НЕ ВИЖУ СМЫСЛА в твоей методике, когда "одной записи из hash соответствует много записей sample". Разбей эту запись в hash на несколько, каждой из которых будет соответствовать ОДНА запись в sample, посредством внешнего ключа sample_id.
__________________
"If people only knew how hard I work to gain my mastery, it wouldn't seem so wonderful at all." Michelangelo Buonarroti |
|