¿Cuál es el mejor diseño para una tabla de base de datos que puede ser propiedad de dos recursos diferentes y, por lo tanto, necesita dos claves externas diferentes?

Mi aplicación tiene configuraciones de notificación para usuarios que pueden pertenecer a grupos. Un administrador de grupo puede definir configuraciones para todo el grupo, de modo que cuando cualquier usuario realice una acción, se notifique al administrador. El administrador también puede definir configuraciones para un usuario individual, que anulará la configuración del grupo.

Ahora mismo tengo una base de datos con columnas: group_id, action1, action2, action3, ... Las acciones son booleanas que determinan si se notifica al administrador cuando esa acción la realiza un usuario de su grupo.

Podría hacer una tabla separada propiedad del modelo de Usuario en lugar del modelo de Grupo, pero se siente ineficaz para almacenar exactamente los mismos datos en una tabla completamente separada, excepto al cambiar group_id por user_id .

Otra opción es agregar user_id a la tabla que ya tengo, y permitir valores nulos para group_id . Al determinar las configuraciones de notificación para un Usuario, la aplicación primero elegiría la configuración en función del Usuario y la alternativa a la configuración donde group_id no es nulo. Esto se siente ineficiente porque habrá muchos valores nulos en la base de datos, pero definitivamente requiere menos trabajo de mi parte.

¿Hay un diseño para esta situación que sea más eficiente que los dos que he descrito?

Generalmente, hay dos estrategias para manejar una situación como esta:

1. Utilice FK exclusivos

Básicamente, cada una de las tablas principales posibles tendrá su propia clave externa separada en la tabla secundaria, y existe un CHECK que impone la obligación de que una de ellas sea no NULL. Como los FK solo se aplican en campos que no son NULL, solo se aplicará uno de los FK.

Por ejemplo:

enter image description here

(se omite la relación entre el usuario y el grupo)

 CHECK ( (group_id IS NOT NULL AND user_id IS NULL) OR (group_id IS NULL AND user_id IS NOT NULL) ) 

2. Usar herencia

Heredar usuario y grupo de un supertipo común y luego conectar la configuración al supertipo:

enter image description here

Para obtener más información sobre la herencia (también conocida como categoría, subclases, subtipo, jerarquía de generalización, etc.), consulte el capítulo “Relaciones de subtipo” de ERwin Methods Guide . Desafortunadamente, los DBMS modernos no son compatibles de forma nativa con la herencia. Para obtener algunas ideas sobre cómo implementarlo físicamente, eche un vistazo a esta publicación .

Esta es una solución para trabajo pesado probablemente no justificada solo para dos tablas (grupos y usuarios), pero puede ser bastante “escalable” para muchas tablas.

¿Qué tal una tabla de acciones en su lugar?

Podría tener las columnas:

 Table Actions: ActionId - Identity columns Action - Store your action here; type would depend on your system RefId - The Id for either the user or the group RefTable - either User or Group 

luego al acceder a la mesa, ya conoce su ID, y usted sabe si es un grupo o usuario y luego puede obtener la acción adecuada.

Esto tiene sentido?

Actualizar:

Si es posible que pueda tener la misma acción para el usuario / grupo y desee que una tenga prioridad (como mencionó en su Q) también podría agregar una columna de priority y configurarla como un número tinyInt : menor número = mayor prioridad. Luego, cuando seleccione las acciones, puede ordenarlas según esta prioridad. Luego realice la primera acción, o cada acción en orden.