Hoy tuvimos la oportunidad de mejorar un poco el tema de diseño de tablas SQL, gracias a Isaac, que tiene a cargo de hacer unas pruebas de implementación de una funcionalidad de red social interna a Dokeos, para una de las próximas versiones de Dokeos.
La primera propuesta de Isaac fue esta:
CREATE TABLE user_friend(
id int not null auto_increment,
friend_id int not null,
user_id int not null,
activate int,
PRIMARY KEY(friend_id)
);
Sin embargo, hay unas cosas que se pueden perfeccionar ahí, lo que hice a través de los siguientes comentarios…
Para las nuevas tablas, y sus IDs, vamos a mejorar un poco la forma de hacerlo y usar el modificador “UNSIGNED” para campos que no van bajo 0.
CREATE TABLE user_friend(
id int unsigned not null auto_increment,
friend_id int unsigned not null,
user_id int unsigned not null,
activate int not null default 0,
PRIMARY KEY(friend_id)
);
Como una red social se hace con varios niveles de conocimiento de personas, creo que sería bueno de tener un estado que no solo es 0 o 1, sino que puede ser mucho más cosas (enemigo (-1), pedido pendiente (0), conocido (1), amigo (2), muy buen amigo(3), familia(4), pareja(5) etc).
Como no sabemos cuantos tipos de relaciones vamos a tener ahí, vamos a considerar que estos son tipos básicos, y que si agregamos subtipos, vamos a usar “11” por ejemplo para un subtipo de “conocidos”.
Para quedarnos con una tabla super-rápida, vamos a llamar a este campo a “relation_type”, definirlo como unsigned también, y definir una segunda tabla que provee la traduction (una tabla diccionario como explicaba el otro día en el curso PHP). Habría puesto “type” sin problema si no era una palabra muy peligrosa que usar en un query SQL (porque podría ser una palabra reservada).
La llave primaria es “id”, no es “friend_id”, porque un usuario puede aparecer más de una vez.
Como esto va a ser una relación entre un usuario y su amigo, vamos a ordenar los campos de una manera diferente y renombrar friend_id a friend_user_id:
CREATE TABLE user_friend(
id int unsigned not null auto_increment,
user_id int unsigned not null,
friend_user_id int unsigned not null,
relation_type int not null default 0,
PRIMARY KEY(id)
);
La tabla adicional será:
CREATE TABLE user_friend_relation_type(
id int unsigned not null auto_increment,
title char(20),
PRIMARY KEY(id)
);
El enlace entre las dos tablas no debe existir en si, pero podríamos considerar que el “title” de esta tabla es el nombre de una variable de idiomas (para poder traducir el tipo de relación).
Finalmente, y como la tabla user_friend siendo potencialmente una tabla de relación entre dos veces la tabla de usuarios (potencialmente 200000 usuarios x 200000 usuarios = 40 000 000 000 de líneas), es necesario usar algo más grande que “int” para el ID, por eso le damos un BIGINT, y es necesario poner un index sobre los campos user_id y friend_id, pero también sobre la relación entre los dos.
CREATE TABLE user_friend(
id bigint unsigned not null auto_increment,
user_id int unsigned not null,
friend_user_id int unsigned not null,
relation_type int not null default 0,
PRIMARY KEY(id)
);
ALTER TABLE user_friend ADD INDEX idx_user_friend_user (user_id);
ALTER TABLE user_friend ADD INDEX idx_user_friend_friend_user
(friend_user_id);
ALTER TABLE user_friend ADD INDEX idx_user_friend_user_friend_user
(user_id,friend_user_id);
CREATE TABLE user_friend_relation_type(
id int unsigned not null auto_increment,
title char(20),
PRIMARY KEY(id)
);
Ahora estas tablas se tienen que añadir a main/install/dokeos_main.sql y main/install/migrate-db-1.8.5-1.8.6 y implementa estas funcionalidades extra. Estas tablas, por ser en relación super cercana con la tabla de usuarios, debería encontrarse en la base main.