¿Cuál es la mejor estrategia de key primaria para una aplicación mobile multi-cliente en línea / sin connection con SQLite y la database Azure SQL como almacén central?

¿Qué estrategia de key primaria sería mejor usar para un model de database relacional dado lo siguiente?

  • decenas de miles de usuarios
  • múltiples clientes por usuario (teléfono, tableta, escritorio)
  • millones de filas por tabla (en continuo crecimiento)

Azure SQL será el almacén de datos central que se expondrá a través de Web API. Los clientes includeán una aplicación web y una serie de aplicaciones nativas que incluyen iOS, Android, Mac, Windows 8, etc. La aplicación web requerirá una connection "siempre encendida" y no tendrá un almacén de datos local sino que recuperará y actualizará a través de la api – piense en CRUD a través de RESTful API.

Todos los demás clientes (teléfono, tableta, escritorio) tendrán un db local (SQLite). En el primer uso de este tipo de cliente, el usuario debe autenticar y sincronizar. Una vez autenticados y sincronizados, estos clientes pueden operar en modo fuera de línea (creando, eliminando y actualizando loggings en el db SQLite local). Estos cambios eventualmente se sincronizarán con el backend de Azure.

La naturaleza distribuida de las bases de datos nos deja con un problema key principal y la razón para hacer esta pregunta.

Esto es lo que hemos considerado hasta ahora:

GUID

Cada cliente crea sus propias keys. En synchronization, existe una posibilidad muy pequeña de una key duplicada, pero tendríamos que tener en count al escribir funcionalidad en cada cliente para actualizar todas las relaciones con una nueva key. Los GUID son grandes y cuando se consideran múltiples keys externas por tabla, el almacenamiento puede convertirse en un problema a lo largo del time. Probablemente el mayor problema sea la naturaleza aleatoria de los GUID, lo que significa que no pueden (o no deberían) utilizarse como índice agrupado debido a la fragmentación. Esto significa que tendríamos que crear un índice agrupado (quizás arbitrario) para cada tabla.

Identidad

Cada cliente crea sus propias keys principales. En synchronization, estas keys se reemplazan con keys generadas por el server. Esto agrega complejidad adicional al process de synchronization y obliga a cada cliente a "arreglar" sus keys, incluidas todas las keys externas en tablas relacionadas.

Compuesto

A cada cliente se le asigna una ID de cliente en la primera synchronization. Esta ID de cliente se usa junto con una ID de incremento automático local como key primaria compuesta para cada tabla. Esta key compuesta será única, por lo que no debe haber conflictos en la synchronization, pero significa que la mayoría de las tablas requerirán una key primaria compuesta. El performance y la complejidad de las consultas son la preocupación aquí.

HiLo (compuesto combinado)

Al igual que el enfoque compuesto, a cada cliente se le asigna una ID de cliente (int32) en la primera synchronization. La ID de cliente se fusiona con una id local única (int32) en una sola columna para hacer una ID exclusiva de la aplicación (int64). Esto no debería generar conflictos durante la synchronization. Si bien hay más order para estas keys frente a los GUID ya que los ids generados por cada cliente son secuenciales, habrá miles de id. De cliente únicos, ¿aún corremos el riesgo de fragmentación en nuestro índice agrupado?

¿Estamos pasando por alto algo? ¿Hay otros enfoques que valga la pena investigar? Una discusión de los pros y los contras de cada enfoque sería muy útil.

La key (juego de palabras) que se debe recordar es simplemente tener una key única para cada object que está almacenando en la tienda persistente. La forma en que manejas el almacenamiento de ese object depende completamente de ti y de la metodología de cómo accedes a esa key. Cada una de las estrategias que enumeró tiene sus propias razones de por qué hacen lo que hacen, pero al final están almacenando una key para un determinado object en la database, por lo que todos sus attributes pueden modificarse manteniendo la misma reference de object en la database .