Представление порядка в реляционной базе данных

У меня есть коллекция объектов в базе данных. Изображения в фотогалерее, продукты в каталоге, главы в книге и т.д. Каждый объект представлен в виде строки. Я хочу иметь возможность произвольно упорядочивать эти изображения, сохраняя это упорядочение в базе данных, поэтому, когда я показываю объекты, они будут в правильном порядке.

Например, скажем, я пишу книгу, и каждая глава является объектом. Я пишу свою книгу и ставил главы в следующем порядке:

Введение, доступность, форма против функции, ошибки, согласованность, вывод, индекс

Он переходит к редактору и возвращается со следующим предложением:

Введение, форма, функция, доступность, последовательность, ошибки, вывод, индекс

Как я могу сохранить этот порядок в базе данных надежным, эффективным способом?

У меня были следующие идеи, но я не в восторге от любого из них:

Массив
. Каждая строка имеет идентификатор заказа, когда заказ изменен (через удаление, за которым следует вставка), идентификаторы заказов обновляются. Это упрощает поиск, так как это просто ORDER BY, но, похоже, легко сломаться.

// REMOVAL
UPDATE ... SET orderingID=NULL WHERE orderingID=removedID
UPDATE ... SET orderingID=orderingID-1 WHERE orderingID > removedID
// INSERTION
UPDATE ... SET orderingID=orderingID+1 WHERE orderingID > insertionID
UPDATE ... SET orderID=insertionID WHERE ID=addedID
Связанный список. Каждая строка имеет столбец для идентификатора следующей строки в упорядочении. Обход здесь кажется дорогостоящим, хотя можно каким-то образом использовать ORDER BY, о котором я не думаю.
Разнесенный массив. Установите идентификатор orderingID (как используется в # 1) большим, поэтому первый объект равен 100, второй - 200 и т.д. Затем, когда происходит вставка, вы просто поместите его в (objectBefore + objectAfter)/2. Разумеется, иногда это нужно перебалансировать, поэтому у вас нет слишком близко друг к другу (даже с плавающей точкой, вы в конечном итоге столкнетесь с ошибками округления).

Ни один из них не кажется мне особенно изящным. У кого-нибудь есть лучший способ сделать это?

Ответ 1

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

Ответ 2

Другой альтернативой будет (если ваша RDBMS поддерживает его) использовать столбцы массива типов. Хотя это нарушает правила нормализации, это может быть полезно в таких ситуациях. Одна база данных, о которой я знаю, имеет массивы PostgreSQL.

Ответ 3

Mix_act_as_list в Rails обрабатывает это в основном так, как вы указали в # 1. Он ищет столбец INTEGER с названием position (из которого вы можете переопределить имя), и используя это для выполнения ORDER BY. Когда вы хотите переупорядочить вещи, вы обновляете позиции. Это помогло мне просто отлично каждый раз, когда я использовал его.

В качестве побочного примечания вы можете удалить необходимость всегда делать повторное позиционирование на INSERTS/DELETES, используя разреженную нумерацию - вроде как базовую заднюю часть дня... вы можете указать свои позиции 10, 20, 30 и т.д., и если вам нужно вставить что-то между 10 и 20, вы просто вставляете его с позицией 15. Аналогичным образом при удалении вы можете просто удалить строку и оставить пробел. Вам только нужно сделать повторную нумерацию, когда вы действительно измените заказ, или если вы попытаетесь сделать вставку, и нет подходящего промежутка для вставки.

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

Ответ 4

Просто мысль, рассматривающая опцию # 1 vs # 3: не отделяет ли параметр отстоящего массива (# 3) проблему от нормального массива (# 1)? Какой бы алгоритм вы ни выбрали, либо он сломан, и вы столкнетесь с проблемами С# 3 позже, или он работает, а затем # 1 должен работать так же хорошо.

Ответ 5

Я бы сделал последовательный номер с триггером в таблице, который "делает комнату" для приоритета, если он уже существует.

Ответ 6

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

Ответ 7

Используйте число с плавающей запятой для представления позиции каждого элемента:

Пункт 1 → 0.0

Пункт 2 → 1.0

Пункт 3 → 2.0

Пункт 4 → 3.0

Вы можете поместить любой предмет между любыми другими двумя предметами простым делением пополам:

Пункт 1 → 0.0

Пункт 4 → 0,5

Пункт 2 → 1.0

Пункт 3 → 2.0

(Перемещенный элемент 4 между пунктами 1 и 2).

Процесс деления пополам может продолжаться почти бесконечно из-за кодирования чисел с плавающей запятой в компьютерной системе.

Пункт 4 → 0,5

Пункт 1 → 0,75

Пункт 2 → 1.0

Пункт 3 → 2.0

(Переместите элемент 1 в позицию сразу после пункта 4)

Ответ 8

Я сделал это в своем последнем проекте, но это было для таблицы, которая только изредка нуждалась в том, чтобы быть специально упорядоченной, и к ней не обращалось слишком часто. Я думаю, что разнесенный массив был бы лучшим вариантом, потому что его переупорядочение было бы самым дешевым в среднем случае, просто с изменением одного значения и запроса на два).

Кроме того, я бы предположил, что ORDER BY будет сильно оптимизирован производителями баз данных, поэтому использование этой функции будет выгодно для производительности, а не для реализации связанного списка.

Ответ 9

У меня тоже была эта проблема. Я был под сильным давлением (не все мы), и я пошел с вариантом №1, и менялись только обновленные строки.

Если вы поменяете пункт 1 на пункт 10, просто выполните два обновления, чтобы обновить номера заказов в пункте 1 и пункте 10. Я знаю, что это алгоритмически просто, и это O (n) наихудший случай, но в худшем случае когда у вас есть полная перестановка списка. Как часто это произойдет? Это для вас, чтобы вы ответили.

Ответ 10

У меня была такая же проблема, и я, вероятно, потратил, по крайней мере, неделю на вопрос о правильном моделировании данных, но, думаю, я наконец получил ее. Используя тип данных массива в PostgreSQL, вы можете сохранить первичный ключ каждого упорядоченного элемента и соответствующим образом обновить этот массив, используя вставки или удаления при изменении вашего заказа. Ссылка на одну строку позволит вам отобразить все ваши объекты на основе упорядочения в столбце массива.

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