Какова лучшая стратегия первичного ключа для онлайн-офлайнового многопользовательского мобильного приложения с базами данных SQLite и Azure SQL в качестве центрального магазина?

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

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

Azure SQL будет центральным хранилищем данных, которое будет отображаться через Web API. Клиенты будут включать в себя веб-приложение и ряд собственных приложений, включая iOS, Android, Mac, Windows 8 и т.д. Веб-приложение потребует "всегда включенное" соединение и не будет иметь локального хранилища данных, но вместо этого будет получать и обновлять через api - думаю CRUD через RESTful API.

Все остальные клиенты (телефон, планшет, рабочий стол) будут иметь локальный db (SQLite). При первом использовании этого типа клиента пользователь должен аутентифицироваться и синхронизироваться. После аутентификации и синхронизации эти клиенты могут работать в автономном режиме (создание, удаление и обновление записей в локальном SQLite db). Эти изменения в конечном итоге будут синхронизироваться с бэкэндом Azure.

Распределенный характер баз данных оставляет нам проблему с первичным ключом и причину возникновения этого вопроса.

Вот что мы рассмотрели до сих пор:

GUID

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

Идентичность

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

Composite

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

HiLo (объединенный композитный)

Как и композитный подход, каждому клиенту присваивается идентификатор клиента (int32) при первой синхронизации. Идентификатор клиента объединяется с уникальным локальным идентификатором (int32) в один столбец, чтобы сделать уникальный уникальный идентификатор приложения (int64). Это не должно приводить к конфликтам во время синхронизации. Хотя для этих ключей и GUID больше заказов, поскольку идентификаторы, созданные каждым клиентом, являются последовательными, будут тысячи уникальных идентификаторов клиентов, так что мы все еще подвергаем риску фрагментации нашего кластерного индекса?

Мы что-то пропускаем? Есть ли еще какие-то подходы, которые стоит исследовать? Обсуждение плюсов и минусов каждого подхода было бы весьма полезным.

Ответ 1

Ключевая (предназначенная для каламбура) вещь, которую нужно запомнить, - это просто иметь уникальный ключ для каждого объекта, который вы храните в постоянном хранилище. Как вы справляетесь с хранением этого объекта, полностью зависит от вас и до методологии доступа к этому ключу. Каждая из перечисленных вами стратегий имеет свои причины для того, почему они делают то, что они делают, но в конце они хранят ключ для определенного объекта в db, поэтому все его атрибуты могут быть изменены, сохраняя одну и ту же ссылку на объект в базе данных,

Ответ 2

Я долго рассматривал этот вопрос и решил, что GUID обычно является лучшим решением. Вот небольшая информация о том, почему:

тождественность

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

композитный

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

Идентификаторы GUID

Мои самые большие опасения по поводу использования GUID были

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

Затем я понял, что в автономном веб-приложении вы обычно не переносите большие объемы данных одновременно, поскольку все они хранятся на клиенте.

Вы также не сильно беспокоитесь о производительности базы данных сервера, потому что это происходит за кулисами в синхронизации - вы просто беспокоитесь о производительности данных на стороне клиента.

Наконец, я понял, что урегулирование конфликта действительно тривиальная вещь. Просто проверьте наличие конфликта и, если он у вас есть, создайте новый GUID на сервере и продолжите работу. Затем отправьте сообщение обратно клиенту, в результате чего клиент выдаст небольшое сообщение об ошибке, а затем удалит все данные на стороне клиента и повторно загрузит их заново с сервера. Это действительно быстро и легко реализовать, и вы, вероятно, уже хотите сделать это возможной операцией в автономном веб-приложении. Хотя это может показаться неудобным для пользователя, вероятность того, что пользователь когда-либо увидит эту ошибку, составляет почти 0%.

Заключение

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

Если вашему приложению не нужно работать в автономном режиме, но у вас есть база данных на стороне клиента для повышения производительности или по другим причинам, вы также можете рассмотреть возможность загрузки gif загрузки и приостановки выполнения на стороне клиента, пока идентификатор не будет возвращен через сервер с помощью ajax.