database - tabla - relacion muchos a muchos postgresql

¿Una o dos claves principales en la tabla de muchos a muchos? (9)

¿Cuál es el beneficio de una clave principal en este escenario? Considere la posibilidad de no tener una clave principal: UserWidgets3: WidgetID (FK), UserID (FK)

Si desea exclusividad, utilice la clave compuesta (UserWidgets2) o una restricción de exclusividad.

La ventaja de rendimiento habitual de tener una clave principal es que a menudo consulta la tabla con la clave principal, que es rápida. En el caso de tablas de muchos a muchos, generalmente no consulta por la clave principal, por lo que no hay ningún beneficio de rendimiento. Muchas de las tablas se consultan por sus claves externas, por lo que debería considerar agregar índices en WidgetID y UserID.

Tengo las siguientes tablas en mi base de datos que tienen una relación de muchos a muchos, que se expresa mediante una tabla de conexión que tiene claves externas a las claves principales de cada una de las tablas principales:

Widget: WidgetID (PK), Título, Precio
Usuario: UserID (PK), FirstName, LastName

Supongamos que cada combinación de User-Widget es única. Puedo ver dos opciones sobre cómo estructurar la tabla de conexión que define la relación de datos:

UserWidgets1: UserWidgetID (PK), WidgetID (FK), UserID (FK)
UserWidgets2: WidgetID (PK, FK), ID de usuario (PK, FK)

La opción 1 tiene una sola columna para la clave principal. Sin embargo, esto parece innecesario ya que la única información que se almacena en la tabla es la relación entre las dos tablas principales, y esta relación en sí misma puede formar una clave única. Por lo tanto, conduce a la opción 2, que tiene una clave principal de dos columnas, pero pierde el identificador único de una columna que tiene la opción 1. También podría agregar opcionalmente un índice único de dos columnas (WidgetID, UserID) a la primera tabla.

¿Hay alguna diferencia real entre los dos en cuanto a rendimiento o alguna razón para preferir un enfoque sobre el otro para estructurar la tabla de muchos a muchos de UserWidgets?

Como cada combinación de User-Widget es única, debe representarla en su tabla haciendo que la combinación sea única. En otras palabras, vaya con la opción 2. De lo contrario, puede tener dos entradas con el mismo widget y las mismas ID de usuario pero con diferentes ID de widget de usuario.

El userwidgetid en la primera tabla no es necesario, como dijiste, la singularidad proviene de la combinación del widgetid y el ID de usuario.

Usaría la segunda tabla, mantendría las claves antiguas y agregaría un índice único en widgetid y userid.

Asi que:

userwidgets( widgetid(fk), userid(fk), unique_index(widgetid, userid) )

Hay una ganancia de rendimiento en no tener la clave primaria adicional, ya que la base de datos no necesitaría calcular el índice para la clave. En el modelo anterior, aunque este índice (a través del unique_index) todavía se calcula, pero creo que es más fácil de entender.

Estoy de acuerdo con las respuestas anteriores, pero tengo una observación para agregar. Si desea agregar más información a la relación y permitir más relaciones entre las mismas dos entidades, necesita la opción uno.

Por ejemplo, si desea realizar un seguimiento de todas las veces que el usuario 1 ha utilizado el widget 664 en la tabla de userwidget, el ID de usuario y el widgetid ya no son únicos.

La opción 2 es la respuesta correcta, a menos que tenga una buena razón para agregar una clave numérica sustituta (que ha hecho en la opción 1).

Las columnas de clave numéricas sustitutas no son ''claves primarias''. Las claves primarias son técnicamente una de las combinaciones de columnas que identifican de manera única un registro dentro de una tabla.

Cualquiera que construya una base de datos debe leer este artículo http://it.toolbox.com/blogs/database-soup/primary-keyvil-part-i-7327 por Josh Berkus para comprender la diferencia entre las columnas de clave numéricas sustitutas y las claves principales.

En mi experiencia, la única razón real para agregar una clave numérica sustituta a su tabla es si su clave principal es una clave compuesta y necesita ser utilizada como referencia de clave externa en otra tabla. Solo entonces deberías incluso pensar en agregar una columna extra a la mesa.

Cada vez que veo una estructura de base de datos donde cada tabla tiene una columna ''id'', es probable que haya sido diseñada por alguien que no aprecia el modelo relacional e invariablemente mostrará uno o más de los problemas identificados en el artículo de Josh.

La opción 2 usa una clave de compunción simple, la opción 1 usa una clave sustituta . La opción 2 es preferible en la mayoría de los escenarios y está cerca del modelo de investigación ya que es una buena clave candidata.

Hay situaciones en las que es posible que desee utilizar una clave sustituta (Opción 1)

No es que la clave compuesta sea una buena clave candidata con el tiempo. Particularmente con datos temporales (datos que cambian con el tiempo). ¿Qué sucede si quiere agregar otra fila a la tabla UserWidget con el mismo UserId y WidgetId? Piense en el empleo (EmployeeId, EmployeeId): funcionaría en la mayoría de los casos, excepto si alguien volviera a trabajar para el mismo empleador en una fecha posterior.
Si está creando mensajes / transacciones comerciales o algo similar que requieren una clave más fácil de usar para la integración. Replicación tal vez?
Si desea crear sus propios mecanismos de auditoría (o similares) y no desea que las claves sean demasiado largas.

Como regla general, al modelar datos, encontrará que la mayoría de las entidades asociativas (de muchas a muchas) son el resultado de un evento. La persona toma un empleo, el artículo se agrega a la canasta, etc. La mayoría de los eventos tienen una dependencia temporal del evento, donde la fecha o la hora son relevantes, en cuyo caso una clave sustituta puede ser la mejor alternativa.

Por lo tanto, tome la opción 2, pero asegúrese de tener el modelo completo.

Personalmente, tendría la columna de clave sintética / sustituta en tablas de muchos a muchos por las siguientes razones:

Si usó claves sintéticas numéricas en sus tablas de entidades, tener las mismas en las tablas de relaciones mantiene la coherencia en el diseño y la convención de nomenclatura.
Puede ser el caso en el futuro que la tabla de muchos a muchos se convierta en una entidad principal para una entidad subordinada que necesita una referencia única a una fila individual.
Realmente no va a usar tanto espacio en el disco adicional.

La clave sintética no reemplaza a la clave natural / compuesta ni se convierte en la PRIMARY KEY de esa tabla simplemente porque es la primera columna de la tabla, por lo que estoy parcialmente de acuerdo con el artículo de Josh Berkus. Sin embargo, no estoy de acuerdo con que las claves naturales sean siempre buenas candidatas para PRIMARY KEY''s y, desde luego, no deberían usarse si se van a utilizar como claves foráneas en otras tablas.

Solo tiene una clave principal en cualquier caso. El segundo es lo que se llama una clave compuesta. No hay una buena razón para presentar una nueva columna. En la práctica, deberá mantener un índice único en todas las claves candidatas. Agregar una nueva columna no le compra más que gastos de mantenimiento.

Ve con la opción 2.

Yo iría con ambos.

Escúchame:

La clave compuesta es obviamente la forma correcta y correcta de ir en la medida en que va reflejando el significado de sus datos. No hay duda.

Sin embargo: he tenido todo tipo de problemas para hacer que hibernación funcione correctamente a menos que use una única clave primaria generada, una clave sustituta.

Entonces usaría un modelo de datos lógico y físico. El uno lógico tiene la clave compuesta. El modelo físico, que implementa el modelo lógico, tiene la clave sustituta y las claves externas.