example - MySQL: ¿una relación uno a uno?

jsf jpa login example

Question

Ahora sé que hay algunas respuestas sobre One to One Relation, pero ninguno de ellos respondió mi pregunta, así que lea esto antes de votar :)

Estoy tratando de lograr la relación uno a uno en la base de datos MySQL. Por ejemplo, digamos que tengo la tabla de Usuarios y la Tabla de Cuentas. Y quiero asegurarme de que el Usuario puede tener solo una cuenta. Y que solo puede haber una Cuenta por usuario.

Encontré dos soluciones para esto, pero no sé qué usar, y ¿hay otras opciones?

Primera solución:

DROP DATABASE IF EXISTS test;
CREATE DATABASE test CHARSET = utf8 COLLATE = utf8_general_ci;
USE test;
CREATE TABLE users(
id INT NOT NULL AUTO_INCREMENT,
user_name VARCHAR(45) NOT NULL,
PRIMARY KEY(id)
) ENGINE = InnoDB DEFAULT CHARSET = utf8;
CREATE TABLE accounts(
id INT NOT NULL AUTO_INCREMENT,
account_name VARCHAR(45) NOT NULL,
user_id INT UNIQUE,
PRIMARY KEY(id),
FOREIGN KEY(user_id) REFERENCES users(id)
) ENGINE = InnoDB DEFAULT CHARSET = utf8;

En este ejemplo, defino la clave externa en las cuentas que apuntan a la clave principal en los usuarios. Y luego hago que la clave foránea sea ÚNICA, por lo que no puede haber dos usuarios idénticos en las cuentas. Para unir mesas, usaría esta consulta:

SELECT * FROM users JOIN accounts ON users.id = accounts.user_id;

Segunda solución:

DROP DATABASE IF EXISTS test;
CREATE DATABASE test CHARSET = utf8 COLLATE = utf8_general_ci;
USE test;
CREATE TABLE users(
id INT NOT NULL AUTO_INCREMENT,
user_name VARCHAR(45) NOT NULL,
PRIMARY KEY(id)
) ENGINE = InnoDB DEFAULT CHARSET = utf8;
CREATE TABLE accounts(
id INT NOT NULL AUTO_INCREMENT,
account_name VARCHAR(45) NOT NULL,
PRIMARY KEY(id),
FOREIGN KEY(id) REFERENCES users(id)
) ENGINE = InnoDB DEFAULT CHARSET = utf8;

En este ejemplo, creo una clave externa que apunta desde la clave principal a una clave primaria en otra tabla. Como las claves primarias son ÚNICAS por defecto, esto hace que esta relación sea uno a uno. Para unir mesas, puedo usar esto:

SELECT * FROM users JOIN accounts ON users.id = accounts.id;

Ahora las preguntas:

• ¿Cuál es la mejor manera de crear una relación One to One en MySQL?
• ¿Hay otras soluciones además de estas dos?

Estoy usando MySQL Workbench, y cuando diseño la relación Uno a Uno en el diagrama EER, y dejo que MySQL Workbench produzca código SQL, obtengo la relación One to Many: S Eso es lo que me confunde: S

Y si importo cualquiera de estas soluciones en el diagrama EER de MySQL Workbench, reconoce las relaciones como Uno para muchos: S Eso también es confuso.

Entonces, ¿cuál sería la mejor manera de definir la relación One to One en MySQL DDL? ¿Y qué opciones hay para lograr esto?

¡¡Gracias!!

Answer 1

Como las claves primarias son ÚNICAS por defecto, esto hace que esta relación sea uno a uno.

No, eso hace que la relación "uno a cero o uno". ¿Es eso lo que realmente necesitas?

Si es así, entonces su "segunda solución" es mejor:

• es más simple,
• toma menos almacenamiento ¹ (y por lo tanto hace que el caché sea "más grande")
• es menos índices para mantener ² , lo que beneficia la manipulación de datos,
• y (dado que está usando InnoDB) clusters los datos de forma natural, por lo que los usuarios que están muy cerca también tendrán sus cuentas almacenadas, lo que puede beneficiar a la ubicación del caché y ciertos tipos de escaneos.

Por cierto, tendrá que hacer accounts.id un entero común (no autoincremento) para que esto funcione.

Si no , mira abajo ...

¿Cuál es la mejor manera de crear una relación One to One en MySQL?

Bueno, "lo mejor" es una palabra sobrecargada, pero la solución "estándar" sería la misma que en cualquier otra base de datos: ponga ambas entidades (usuario y cuenta en su caso) en la misma tabla física.

¿Hay otras soluciones además de estas dos?

Teóricamente, podría crear FK circulares entre las dos PK, pero eso requeriría restricciones diferidas para resolver el problema de la gallina y el huevo, que desafortunadamente no son compatibles con MySQL.

Y si importo cualquiera de estas soluciones en el diagrama EER de MySQL Workbench, reconoce las relaciones como Uno para muchos: S Eso también es confuso.

No tengo mucha experiencia práctica con esa herramienta de modelado en particular, pero supongo que es porque es "uno para muchos" donde el lado "muchos" fue limitado a 1 por lo que es único. Recuerde que "muchos" no significa "1 o muchos", significa "0 o muchos", por lo que la versión "limitada" realmente significa "0 o 1".

¹ No solo en el gasto de almacenamiento para el campo adicional, sino también para el índice secundario. Y dado que está utilizando InnoDB, que siempre agrupa las tablas , tenga en cuenta que los índices secundarios son incluso más caros en las tablas agrupadas que en las tablas basadas en el montón.

² InnoDB requiere índices en claves externas.

Answer 2

¿Qué pasa con el siguiente enfoque?

1. Crear usuario de tabla

   CREATE TABLE `user` (
   `id` int(11) NOT NULL AUTO_INCREMENT,
   `name` varchar(45) DEFAULT NULL,
   PRIMARY KEY (`id`)
   ) ENGINE=InnoDB DEFAULT CHARSET=utf8;

2. Crear una cuenta de tabla con un índice único en user_id y account_id con una relación de clave externa para usuario / cuenta y una clave principal en user_id and account_id

   CREATE TABLE `account` (
   `id` int(11) NOT NULL AUTO_INCREMENT,
   `name` varchar(45) DEFAULT NULL,
   PRIMARY KEY (`id`)
   ) ENGINE=InnoDB DEFAULT CHARSET=utf8;

3. Crear tabla user2account

   CREATE TABLE `user2account` (
   `user_id` int(11) NOT NULL,
   `account_id` int(11) NOT NULL,
   PRIMARY KEY (`user_id`,`account_id`),
   UNIQUE KEY `FK_account_idx` (`account_id`),
   UNIQUE KEY `FK_user_idx` (`user_id`),
   CONSTRAINT `FK_account` FOREIGN KEY (`account_id`) REFERENCES `account` (`id`),
   CONSTRAINT `FK_user` FOREIGN KEY (`user_id`) REFERENCES `user` (`id`)
   ) ENGINE=InnoDB DEFAULT CHARSET=utf8;

Si bien esta solución tiene la mayor huella en la base de datos, existen algunas ventajas.

• Poner la FK_Key en la tabla del usuario o en la tabla de la cuenta es algo que espero que sea una repetición de uno a muchos (el usuario tiene muchas cuentas ...)
• Si bien este enfoque de user2account se usa principalmente para definir una relación de muchos a muchos, agregar una restricción UNIQUE en user_id y en account_id evitará crear algo más que una relación de uno a uno.

La principal ventaja que veo en esta solución es que puede dividir el trabajo en diferentes capas de código o departamentos en una empresa

• El departamento A es responsable de crear usuarios, esto es posible incluso sin permiso de escritura para la tabla de cuentas
• El departamento B es responsable de crear cuentas, esto es posible incluso sin permiso de escritura para la tabla de usuarios
• El departamento C es responsable de crear la asignación, esto es posible incluso sin permiso de escritura para el usuario o la tabla de cuentas
• Una vez que el Departamento C ha creado un mapeo, ni el departamento A ni el B pueden borrar ni el usuario ni la cuenta sin pedir al departamento C que elimine primero el mapeo.

Answer 3

Su primer enfoque crea dos claves candidatas en la tabla de cuentas: id y user_id .

Por lo tanto, sugiero el segundo enfoque, es decir, el uso de la clave externa como clave principal. Esta:

• usa una columna menos
• le permite identificar de manera única cada fila
• le permite hacer coincidir la cuenta con el usuario