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?

число записей в таблице A?

9:
1x varchar (23)
3x INT
4x MEDIUMINT
1x TINYINT
ну и с этим будет тогда 5 mediumint

Эмм.. на всякий. На одну А приходится от 1 до 180 (экстремальный вариант) B. В среднем можно считать порядка 6 - 18.

да не колонок, а записей. Рядов. Rows. Размер таблицы, в общем.

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

На счет размера то А планируется несколько 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

так и есть, берёшь первичые ключи из обеих таблиц и делаешь ещё одну таблицу отображения одних на другие - в твоём случае АБ. Ессно, всё должно быть покрыто индексами.
Если бы А была небольшого размера - то можно было с автоинкрементом не заморачиваться, просто создать индекс поверх хеша и его же использоваться как ключ в АБ, но на больших таблицах лучше ввести дополнительную integer колонку для меньшего размера индекса.

PS: А и Б связаны как 1*n, или же m*n? Т.е. что каждой записи из А соответственно >1 записи из Б, я уже понял - а в обратную сторону? Одной записи из Б может соответствовать только одна А или несколько?

В обратную сторону не интересует, таблица Б тут будет конечной, то есть никуда дальше не ведет.
Спасибо за пояснение :)

Еще у меня есть небольшой вопрос немножко по другому поводу.
В таблицу часто разными пользователями пишется часто повторяющаяся информация. По-моему тут лучше будет сначала проверять есть ли уже такая запись и если есть то давать ее id, если нет то записывать ее.
Единственное что смущает это количество запросов, сначала прийдется делать
SELECT.. а потом INSERT если ничего не найдено.
Единственное решение которое я вижу это
INSERT ... ON DUPLICATE KEY UPDATE[FONT=verdana,geneva,lucida,'lucida grande',arial,helvetica,sans-serif]
но ему нужен уникальный индекс, а в моем случае это будет все что пишется. Таким бы мог стать md5 или crc32, но они по длине 1/3 всех данных.[/FONT]
Имеет ли это смысл? Или лучше 2 запроса?

Да я, собственно, не про то, кто куда ведёт - просто если каждому Б соответствует только один А, то в дополнительной таблице нужда вообще отпадает - просто в Б хранишь id из А как внешний ключ. Если же А и Б относятся как m*n - тогда и появляется третья таблица отображений.
Странное построение таблицы. Подробнее о том, что именно за данные и какова структура таблицы - потому что сдаётся мне, что тут что-то не так.
Если же на самом деле это набор небольшого количества мелких данных - то, как вариант, покрыть их UNIQUE индексом и использовать ON DUPLICATE KEY UPDATE.
В общем, подробнее про данные и таблицу - тогда можно будет более предметно обсуждать.

Да нет, тут не получится. Для каждого А есть много Б.
Но при этом много А могут ссылаться на одно Б.
Если брать второй вопрос (хотелось бы это объединить в одну систему) то каждое А проверяет есть ли Б и если есть то берет только его ID, на создавая нового Б.
Б - описание самой информации что закладывается в БД.
В итоге получается что если добавляется уже существующая информация то новое Б не создается, просто берется его id и записывается в А (через АБ конечно).
Если меняется одно Б то таким образом оно меняется везде где упоминается. Ну и заобно облегчается контроль количество каждого Б :), увеличивать просто счетчик на 1 с помощью АПДЕЙТ.

На этом приходим ко второму вопросу, как эту фигню туда записать. Каждое значение в Б может встречаться во всей таблице дофига раз, но комбинация всех значений только 1.
Тут больше похоже на проблему скорости/места на диске. В моем случае скорость не очень важна.

Вот тут пришел на ум пример, записать синусоиду допустим из 50 точек на период.
Можно просто записывать, быстро и сердито. Время - значение.
А можно брать по несколько точек за раз и присвоить им Id. При записи искать встречается ли эта комбинация и если встречается давать ссылку на нее.
Получаем в первом случае 50 записей типа: время - значение
во втором (10 точек за раз) 5 записей типа: время - линк на таблицу значений точек.

Что-то подобное второму мне бы и хотелось.

тогда заголовок темы неверен. Это m*n, а не 1*n.
Два способа:
а) искусственный ключ;
б) UNIQUE-индекс поверх всех колонок из Б.

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

Во втором случае ты просто требуешь уникальности сочетания значений Б - и, соответственно, при поиске просто перечисляешь все эти значения.

Преимущества и недостатки, полагаю, очевидны. Первый способ даёт избыточные вычисления, но уменьшает число колонок в индексе до одной с ограниченной длиной. Если у тебя в таблице Б есть колонки типа BLOB или TEXT - то следует заюзать хеш.
Второй же способ более пригоден в случае, если у тебя в Б набор небольших данных (к примеру, несколько INTEGERов, и т.п.) - тогда расходоваться ещё и 32-байтовый хеш смысла нет, просто покрывай их все UNIQUE-индексом и ищи по сочетанию значений.


Плохой пример. В данном случае лучше получить 50 записей, т.к. далее ты можешь быстрее их доставать и с ними работать. Если чтение происходит намного чаще, чем запись/обновление - то лучше пожертвовать местом в таблице и временем записи (обновления индексов) для достижения большей скорости чтения.

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

Извините что пропал, но это я ковыряю в свободное от остального время :)
Сделал Б с уникальным индексом по всему Б и через 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му примеру похожему на пример с манов мускула в сети. А больше спрашивать негде :(.

лол, да я как бы не предлагал, а так понял структуру твоей БД на основе твоих же описаний.
а откуда он должен взяться?
Давай ты немного упростишь для примеру задачу и по шагам распишешь, что у тебя есть на входе, и что нужно найти/получить - потому что не знаю, как коллега Aeon, а я лично из последнего поста практически ничего не понял - откуда берётся, что берётся, почему А нельзя найти, по каким признакам он должен быть найден, и т.п.

Да, мне тоже немного трудно разобраться что с какого края идёт. Кроме того, обозначать их А и Б особо пониманию не помогает... Скажи уж что в них живёт, в таблицах этих твоих. Мне это напоминает таблицу продуктов и заказов, скажем – 

продукты:
ид, название, цена
1, фигня, 100
2, фиговина, 200

заказы:
ид, клиент, адрес
1, "Ваня Буркалов", "дом 200 деревня Буркалы"
2, "Вася Пупкин", "дом 1, деревня Пупково"

продукты–в–заказах:
заказ, продукт, количество
1, 1, 3
1, 2, 1
2, 1, 1

(Вообще–то клиенты бы жили в отдельной таблице, но бог с ним, пример ясен).

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

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

Да, сорри. Вот как это выглядит (аттач).
Папка с голубыми значками - уникальные ключи (что под папкой)
Все что с '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 (таблица, да и то не всегда).

Будет такое работать? ))

"будет, но только под себя" ;)
А если серьёзно - то твоя проблема в том, что ты для ряда из таблицы 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.

Мдя.. что-то я вчера недодумал про чтение. По-моему я вообще это прочитать не смогу так как декодировать хэш будет сложновато.:biggrin:. Но вопрос сформулирован точно.

sample_hash_2 присутствует по непонятной мне причине :) Програма сама тянет все индексы из пред. таблицы. и фиг ей объяснишь что они там не нужны :)

sample_id полностью заменяет basic2_b1_id, basic2_b2_id, это поле увеличивается по мере (auto incr.), уникально для всей таблицы sample.

Если делать так то получится настоящий 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. Очень похоже не то что ты предложил.

что значит "на одно значение hash много sample"? Не может такого быть. А если есть - то неправильно. Каждой записи в hash соответствует ОДНА запись из navtable (на которую hash ссылается внешним ключом hash.hash_id), и ОДНА запись из sample (определяемая через hash.sample_id).

Соответственно ключ поверх этой таблицы простой - 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.

Одной записи в hash соотв. одна запись в navtable, это так, но много записей sample.
Пример постом выше. Просто когда я пишу в hash у меня еще нет navtable чтобы искать
SELECT hash_id FROM hash WHERE sample_id=XXX
поэтому мне надо какую-нибудь уникальную запись из всех sample, почему такой не может быть хэш всех sample_id?
Берем пустую строку и добавляем в конец ее sample_id, потом в конец этого добавляем следующий sample_id. В конце получится длинноечисло которое можно запихать в md5(). Оно же будет уникальным.

Второе утверждение, имхо, бессмысленно и неправильно. Создавай сначала записи в sample, затем - запись в navtable, затем уже линкуй их друг с другом посредством hash. Если нет соответствующей записи в navtable/sample - то запись в hash является бессмысленной и некорректной, т.к. отсутствует ссылочная целостность.
Ещё раз говорю - я НЕ ВИЖУ СМЫСЛА в твоей методике, когда "одной записи из hash соответствует много записей sample". Разбей эту запись в hash на несколько, каждой из которых будет соответствовать ОДНА запись в sample, посредством внешнего ключа sample_id.

Все, сделал вроде как надо :)
Потом опробую как хотел сделать.
Всем спасибо за внимание.