Diseño de base de datos relacional múltiples tipos de usuario

Tengo 4 tipos de usuarios y cada uno tiene datos específicos, pero también comparten datos comunes, como username , password .

Mi primer pensamiento es crear una tabla de users principales con la columna user_type . Luego, al consultar los datos del usuario, primero puedo seleccionar su user_type y luego, dependiendo de la output ejecutar una consulta diferente para obtener los datos específicos del “tipo de usuario”. No me gusta esto porque me gustaría poder tomar todos los datos relacionados con el usuario con una sola consulta e idealmente usar Foreign Keys.

La segunda idea es no tener una columna user_type en la tabla de users y en su lugar usar una clave externa que desde una tabla de tipo de usuario específico apuntará a una fila la tabla de users principales. Me gusta un poco mejor, aunque supongo que tendré que ejecutar N consultas, donde N es el número de usuario cada vez que necesito obtener datos de usuario.

Hay más opciones ? ¿Cuál sería la buena práctica en tal caso?

Muchas gracias

Su caso parece una instancia de clase / subclase.

Hay dos maneras clásicas de diseñar tablas SQL para tratar con subclases. Cada uno tiene ventajas y desventajas.

Una forma se llama “herencia de tabla única”. En este diseño, solo hay una tabla para todos los tipos de usuarios. Si una columna dada no pertenece a una fila dada, la intersección se deja NULO. Se puede agregar una columna para indicar el tipo de usuario.

Otra forma se llama “herencia de tabla de clase”. Esto es muy parecido a la respuesta que dio Nanego, con algunos cambios menores. Hay una tabla para usuarios, con todos los datos comunes, y un campo de id. Hay una tabla para cada subclase, con datos que pertenecen a esa subclase. El campo id a menudo se configura como una copia del campo id en la fila correspondiente en la tabla de usuarios. De esta forma, la clave de la subclase puede hacer doble función, actuando como una clave principal y como una clave externa que hace referencia a la tabla de usuario. Esta última técnica se llama “clave primaria compartida”. Requiere un poco de progtwigción en el momento de la inserción, pero vale la pena. Aplica la naturaleza uno a uno de la relación, y acelera las uniones necesarias.

Puede buscar estos tres diseños como tags en SO o como artículos en la web.

herencia de tabla única herencia de tabla- clase clave primaria compartida

Aunque es más eficiente el uso de espacio en disco, el problema con la división en tablas separadas es que se requieren efectivamente combinaciones condicionales, uniéndose a la tabla específica de tipo de usuario basada en el tipo de usuario. Es un dolor codificar como SQL, ¿y en estos días a quién le importa el espacio en disco?

La mejor opción es tener una tabla de usuario con suficientes columnas para almacenar información sobre cualquier tipo de usuario, sabiendo que algunas columnas no se usarán para algunos tipos de usuarios. La “ineficiencia” de tener columnas no utilizadas será más que compensada en velocidad de ejecución y simplicidad de consulta.

También es fácilmente extensible, en caso de que obtenga otro tipo de usuario, es mucho más fácil agregar columnas que agregar tablas y, a medida que agregue más tipos de usuarios, el requisito de nuevas columnas disminuiría (simplemente no hay muchas diferencias entre ellas). cosas sobre un usuario que necesita almacenar)

Mi manera de hacerlo habría sido crear una tabla “Persona” con los campos comunes, y una tabla para cada tipo de usuario con una clave externa “person_id”.

En su solicitud, solo tiene que unir dos tablas con foreign_key para obtener todos los datos para un tipo de usuario.

¿Cuántos tipos de usuarios tienes?