Modelo de Base de Datos de un Sistema de Red Social Básico

Les voy a explicar el modelo de base de datos de un sistema de red social básico, ya que es posible agregar tantos módulos/tablas como necesitemos.

Este modelo de base de datos esta basado en el sistema Smile y Smile Pro.

Tablas

El modelo de base de datos de un sistema red social cuenta con las siguientes tablas:

• User: Sirve para almacenar los datos basicos del usuario
• Recover: Sirve cuando el usuario quiere recuperar su cuenta/contraseña
• Level: Nivel de distribución de las publicaciones
• Country: Países
• Sentimental: Situación sentimental
• Profile: Información de perfil extendida
• Album: Para crear albums de imágenes
• Image: Para guardar las imagenes
• Post: Guardar las publicaciones/statuses
• Post Image: Relacionar las imágenes con un post
• Heart: (Corazon) para agregar “Me gusta” a las publicaciones/imagenes
• Comment: Comentarios de las publicaciones/imagenes
• Conversation: Conversaciones entre usuarios
• Message: Mensajes entre usuarios
• Notification: Notificaciones de interacciones
• Team: Grupos

Modelo entidad-relación

En la siguiente imagen se muestra el diagrama entidad-relación de la base de datos.

Link: Ver el codigo SQL del sistema de red social smile.

Tabla user

La tabla user es la tabla principal del sistema, ya que cada usuario corresponde una cuenta y todos las las acciones se relacionan con el usuario desde publicar, comentar, hasta las notificaciones, etc..

create table user(
	id int not null auto_increment primary key,
	name varchar(50),
	lastname varchar(50),
	username varchar(50),
	email varchar(255),
	password varchar(60),
	code varchar(20),
	is_active boolean default 0,
	is_admin boolean default 0,
	created_at datetime
);

La tabla user cuenta con los siguientes campos:

• id: es la llave primaria auto incremental de la tabla (La mayoría de las tablas incluyen su ID para ser relacionadas con otras tablas)
• name: Nombre real del usuario
• lastname: Apellidos del usuario
• username: Nombre de usuario para inciar sesión
• email: Correo para iniciar sesión y para recibir notificaciones
• password: Contraseña
• code: Código de recuperación
• is_active: Si el usuario esta activo
• is_admin: Si el usuario es administrador
• created_at: Fecha de creación automática por el sistema

Tabla recover

La tabla recover sirve para almacenar los datos cuando el usuario intenta recuperar su contraseña.

Codigo sql

create table recover (
	id int not null auto_increment primary key,
	user_id int,
	code varchar(20),
	is_used boolean default 0,
	created_at datetime,
	foreign key(user_id) references user(id)
);

Explicación de los campos:

• id: Llave primaria
• user_id: El id del usuario que solicita la recuperación
• code: Codigo generado aleatoriamente
• is_used: Si el codigo ya esta usado, si esta usado ya no se puede user de nuevo
• created_at: Fecha de creación o de solicitud

Tabla level

La tabla level sirve para guardar los niveles de distribución de las publicaciones, por ejemplo las publicaciones publicas las pueden ver todos aunque no sean amigos, Amigos: solo mis amigos, Amigos de mis amigos…

create table level(
	id int not null auto_increment primary key,
	name varchar(50)
);

Explicación:

• id: Llave primaria
• name: Nombre o texto

Tabla country

Sirve para almacenas los países que seleccionan los usuarios en su perfil.

create table country(
	id int not null auto_increment primary key,
	name varchar(50),
	preffix varchar(50)
);

Explicación:

• id: Llave primaria
• name: Nombre que se muestra, por ejemplo Mexico
• preffix: Prefijo, por ejemplo MX

Tabla sentimental

Almacena los tipos de relaciones o estado de las relaciones: Soltero, casado, divorciado, es complicado …

create table sentimental(
	id int not null auto_increment primary key,
	name varchar(50)
);

Explicación de los campos:

• id: Llave primaria
• name: Nombre que se muestra

Tabla profile

Almacena información extendida sobre el perfil del usuario.

create table profile(
	day_of_birth date ,
	gender varchar(1) ,
	country_id int ,
	image varchar(255),
	image_header varchar(255),
	title varchar(255),
	bio varchar(255),
	likes text,
	dislikes text,
	address varchar(255) ,
	phone varchar(255) ,
	public_email varchar(255) ,
	user_id int ,
	level_id int ,
	sentimental_id int ,
	foreign key (sentimental_id) references sentimental(id),
	foreign key (country_id) references country(id),
	foreign key (level_id) references level(id),
	foreign key (user_id) references user(id)
);

Explicación de los campos:

• day_of_birth: Fecha de nacimiento
• gender: Genero, hombre, mujer, otro, etc..
• country_id: Id del pais desde la tabla “country”
• image: Imagen de perfil
• image_header: Imagen de cabecera
• title: Titulo del perfil
• bio: Descripción o biografia del perfil
• likes: Cosas que te gustan
• dislikes: Cosas que no te gustan
• address: Dirección o domicilio
• phone: Numero de telefono
• public_email: Email publico
• user_id: Id del usuario desde la tabla “user”
• level_id: Quien puede ver tu perfil desde la tabla “level”
• sentimental_id: Situación sentimental desde la tabla “sentimental”

Tabla album

Sirve para crear albums de imagenes.

create table album(
	id int not null auto_increment primary key,
	title varchar(200),
	content varchar(500),
	user_id int,
	level_id int,
	created_at datetime,
	foreign key (user_id) references user(id),
	foreign key (level_id) references level(id)
);

Explicación:

• id: Llave primaria
• title: Titulo del album
• content: Descripción del album
• user_id: Id del usuario propietario del album
• level_id: Nivel de permiso o distribución del album
• created_at: Fecha de creación

Tabla image

Sirve para guardar las imágenes  y su información.

create table image(
	id int not null auto_increment primary key,
	src varchar(255),
	title varchar(200),
	content varchar(500),
	user_id int,
	level_id int,
	album_id int,
	created_at datetime,
	foreign key (album_id) references album(id),
	foreign key (user_id) references user(id),
	foreign key (level_id) references level(id)
);

Explicación:

• id: Llave primaria
• src: Nombre del archivo de imagen
• title: Titulo de la imagen
• content: Descripción de la imagen
• user_id: Id del usuario propietario de la imagen
• level_id: quien puede ver la imagen
• album_id: Si la imagen pertenece a un album, se guarda el id del album.
• created_at: Fecha de creación

Tabla post

Sirve para guardar las publicaciones o cambios de estado del usuario, también eventos y publicaciones para amigos.

create table post(
	id int not null auto_increment primary key,
	title varchar(500) ,
	content text,
	lat double ,
	lng double ,
	start_at datetime,
	finish_at datetime,
	receptor_type_id int default 1, /* 1.- user, 2.- group **/
	author_ref_id int,
	receptor_ref_id int,
	level_id int,
	post_type_id int default 1,
	created_at datetime,
	foreign key (level_id) references level(id)
);

Descripción de los campos:

• id: Identificador, llave primaria auto incremental
• title: Titulo de la publicación
• content: Contenido de la publicación
• lat: Coordenada latitud para la ubicación
• lng: Coordenada longitud para la ubicación
• start_at: Fecha de inicio
• finish_at: Fecha de fin
• receptor_type_id: Donde se publica en el usuario, un rupo, una pagina, etc…
• author_ref_id: El id del usuario que publica
• receptor_ref_id: El id del usuario que recibe la publicación
• level_id: Nivel de quien puede ver la publicación
• post_type: Tipo de publicación puede ser estatus, evento, etc.
• created_at: Fecha de creación

Tabla post_image

Sirve para relacionar un post con una imagen o mas.

create table post_image(
	post_id int,
	image_id int,
	foreign key (post_id) references post(id),
	foreign key (image_id) references image(id)
);

Descripción:

• post_id: Id de la publicación
• image_id: Id de la imagen

Tabla heart

Sirve para guardar los “Me gusta” de las publicaciones. No se pude usar la palabra “like” para el nombre de la tabla por que “like” es un comando SQL.

create table heart(
	id int not null auto_increment primary key,
	type_id int default 1,
	ref_id int,
	user_id int,
	created_at datetime,
	foreign key (user_id) references user(id)
);

Descripción de los campos:

• id: Llave primaria
• type_id: Tipo, si es para posts, imágenes, albums etc.
• ref_id: El id del del post, imagen o album segun el caso.
• user_id: El id del usuario que crea el “me gusta”
• created_at: Fecha de creación de el “me gusta”

Tabla comment

Sirve para guardar los comentarios de los posts, imágenes, etc.

create table comment(
	id int not null auto_increment primary key,
	type_id int,
	ref_id int,
	user_id int,
	content text,
	comment_id int,
	created_at datetime,
	foreign key (user_id) references user(id),
	foreign key (comment_id) references comment(id)
);

Explicación:

• id: Llave primaria
• type_id: Tipo, si es para posts, imágenes, albums etc.
• ref_id: El id del del post, imagen o album según el caso.
• user_id: El id del usuario que crea el comentario
• content: Contenido del comentario
• comment_id: Si es un comentario de otro comentario, se guarda el id del comentario superior
• created_at: Fecha de creación

Tabla friend

Sirve para guardar las solicitudes de amistad y amigos.

create table friend(
	id int not null auto_increment primary key,
	sender_id int,
	receptor_id int,
	is_accepted boolean default 0,
	is_readed boolean default 0,
	created_at datetime,
	foreign key (sender_id) references user(id),
	foreign key (receptor_id) references user(id)
);

Descripcion de los campos:

• id: Llave primaria
• sender_id: Usuario que envía la solicitud de amistad
• receptor_id: Usuario que recibe la solicitud
• is_accepted: Una vez que el usuario acepta entonces ya son amigos
• is_readed: Si el usuario que recibe lee la solicitud
• created_at: Fecha de creacion

Tabla conversation

Sirve para crear las conversaciones entre usuarios.

create table conversation(
	id int not null auto_increment primary key,
	sender_id int,
	receptor_id int,
	created_at datetime,
	foreign key (sender_id) references user(id),
	foreign key (receptor_id) references user(id)
);

Explicación:

• id: Llave primaria
• sender_id: Usuario que envía la solicitud de conversación
• receptor_id: Usuario que recibe la solicitud
• created_at: Fecha de creación

Tabla message

Sirve para guardar los mensajes de las conversaciones entre usuarios.

create table message(
	id int not null auto_increment primary key,
	content text,
	user_id int,
	conversation_id int,
	created_at datetime,
	is_readed boolean default 0,
	foreign key (user_id) references user(id),
	foreign key (conversation_id) references conversation(id)
);

Descripción de los campos:

• id: Lllave primaria de la tabla
• content: contenido del mensaje
• user_id: Usuario que envía el mensaje
• conversation_id: Id de la conversación
• created_at: Fecha de creación
• is_readed: Si el mensaje ya fue leído por el otro usuario

Tabla notification

Sirve para las notificaciones.

create table notification(
	id int not null auto_increment primary key,
	not_type_id int,
	type_id int,
	ref_id int,
	receptor_id int,
	sender_id int,
	is_readed boolean default 0,
	created_at datetime,
	foreign key (sender_id) references user(id),
	foreign key (receptor_id) references user(id)
);

Descripción de los campos:

• id: Llave primaria de la tabla
• not_type_id: Tipo de la notificación: me gusta, comentario…
• type_id: Tipo de contenido que activa la notificación: publicación, imagen, album, etc.
• ref_id: Id del contenido que activa la notificación
• receptor_id: Usuario que va a recibir la notificación
• sender_id: Usuario que activa la notificación
• is_readed: Si ya fue leída la notificación
• created_at: Fecha de creación de la notificación

Tabla team

Sirve para crear grupos y compartir información. No se puede usar la palabra “group” por que esta reservada por MySQL.

create table team (
	id int not null auto_increment primary key,
	image varchar(200),
	title varchar(200),
	description varchar(500) ,
	user_id int,
	status int default 1 /* 1.- open, 2.- closed */,
	created_at datetime,
	foreign key (user_id) references user(id)
);

Explicacion de los campos:

• id: Llave primaria
• image: Imagen del grupo
• title: Titulo del grupo
• description: Descripcion del grupo
• user_id: Propietario del grupo
• status: Estado del grupo
• created_at: fecha de creación

Les invito a dejar sus dudas, comentarios y/o sugerencias.

Saludos