Sistema de mensajes privados

[hgp147]

Hola, estaba pensando como hacer la estrucutura de la db para un sistema de mensajes privados que se pueda enviar el mensaje a 3 destinatarios como máximo, se me ocurrio hacer dos tablas:

Tabla 1:
id | mensaje | asunto | fecha

Tabla 2:
id | usuario | tipo (destinatario o remitente) | mensaje_id | estado (si esta leido o no, o si esta borrado. 0->No leido, 1-Leido, 2-Borrado)

En la primer tabla guardo información relacionada con el mensaje y en la segunda con el usuario.

Al mostrar la bandeja de entrada de usuario haría:

SELECT * FROM tabla2 WHERE usuario='hgp147' AND tipo='dest' AND estado<>2 (no borrado)

Luego dentro del while con los resultados que devolvio la consulta hago:
-Consulto a la tabla1 el asunto y la fecha
-Consulto a la tabla2 el remitente que tenga el mismo mensaje_id

Para mostrar los mensajes enviados:
SELECT * FROM tabla2 WHERE usuario='hgp147' AND tipo='rem' AND estado<>2 (no borrado)

Luego dentro del while con los resultados que devolvio la consulta hago:
-Consulto a la tabla1 el asunto y la fecha
-Consulto a la tabla2 los destinatarios que tenga el mismo mensaje_id

Me gustaría que me digan si esta bien así o si se les ocurre algo mejor.

Gracias.

[Triby]

Yo uso solamente 1 tabla para los mensajes:

Tabla: mensajes
id | owner | de | para | carpeta | fecha | asunto | mensaje | estado

Yo uso todos los campos numericos, excepto claro, asunto y mensaje.
id = ID del mensaje (auto increment)
owner = ID de usuario al que pertenece el mensaje
de = ID de usuario que envia el mensaje
para = ID de usuario que recibe
carpeta = tiny 1 (1 = recibidos, 2= enviados)
fecha = siempre la trato como timestamp (int 10)
asunto = varchar 255
mensaje = text
estado = tyny 1 (1 = nuevo, 0 = leido)

No tiene caso crear un estado para mensajes borrados, es mejor eliminarlos directamente.

Ver una carpeta:
SELECT * FROM mensajes WHERE owner = $idusuario AND carpeta = $carpeta ORDER BY fecha DESC

[Ronruby]

carpeta = tiny 1 (1 = recibidos, 2= enviados)

No es necesario crear un campo para saber si el mensaje esta en la bandeja de entrada o salida.
Para los entrantes es solamente verificar que los mensajes cuyo campo 'para' sea igual a el usuario conectado, y para los salientes aquellos cuyo campo 'de' es igual a el usuario.

Ejemplo entrantes:
SELECT * FROM mensajes WHERE para='{$_SESSION['userId']}' ORDER BY fecha DESC

[hgp147]

Gracias Triby, esta bueno tu ejemplo ya que de esta forma ahorro de hacer consultas dentro del while. No sería mejor guardar el mensaje una sola vez, por ejemplo si se envía un mensaje privado a 3 personas, habria 4 veces el mismo mensaje, se podría ahorrar espacio al guardar solo uno. Vale la pena?

Gracias

[Triby]

Ronruby, no lo habia pensado asi, pero... falta especificar el propietario, quedaria:

SELECT * FROM mensajes WHERE owner = '{$_SESSION['userId']}' AND para = '{$_SESSION['userId']}' ORDER BY fecha DESC


hpg147: No pienses tanto en el espacio que va a ocupar un mensaje, sino en que tanto tu como tus usuarios tengan pleno control sobre los mensajes que desean mantener o los que desean eliminar, ademas de optimizacion de tus consultas, aunque con tu diseño se puede hacer un join y se soluciona ese tema.

[Ronruby]

Tampoco es necesario especificar el dueño del mensaje, con los campos 'de' y 'para' es suficiente.

[Triby]

No, Ronruby, si es necesario especificarlo, porque en 1 mensaje enviado, donde se almacena una copia:

Enviado: propietario = para = quien recibe y de = quien envia
Copia: propietario = de = quien envia y para = quien recibe

Tanto el que envia, como el que recibe mantienen el mensaje como leido o nuevo y pueden decidir si conservar o eliminar el mensaje, sin afectar al otro.

[Ronruby]

No, Ronruby, si es necesario especificarlo, porque en 1 mensaje enviado, donde se almacena una copia:

Ah, si guardas una copia entonces si es necesario. Disculpa.

Pero si guardas una copia directamente, eso quiere decir que cada mensaje esta 2 veces.
Y al cabo de un tiempo si tienes muchos mensajes la base de datos puede ser pesada. Para ahorrarte la mitad de espacio, yo lo que haria es crear una base de datos llamada mensajes.
Esta contendra un id y el campo message tipo text (y si lo deseas el asunto tambien), luego en tu tabla de mensajes solo haces una relacion al id del mensaje. Asi te ahorras tener el mensaje escrito 2 veces en la base de datos.

[hgp147]

Estube probando lo de join, con estas dos tablas:

mp
id | usuario | tipo | mensaje_id | estado

mp2
id | mensaje | asunto | fecha | de | para

Esta sería la consulta para ver los mp enviados:
SELECT *FROM mp INNER JOIN mp2 ON mp.usuario = 'hgp147' AND mp.tipo='rem'
AND mp.mensaje_id = mp2.id ORDER BY mp2.fecha DESC LIMIT 20

Y está para los recibidos:
SELECT *FROM mp INNER JOIN mp2 ON mp.usuario = 'hgp147' AND mp.tipo='dest'
AND mp.mensaje_id = mp2.id ORDER BY mp2.fecha DESC LIMIT 20



Ejemplos de registros:
mp
id | usuario | tipo | mensaje_id | estado

1 | hgp147 | rem | 1 | 0
2 | Triby | dest | 1 | 1
3 | Ronruby | dest | 1 | 1


mp2
id | mensaje | asunto | fecha | de | para
1 | texto | asunto | 2009-01-03 18:56:10 | hgp147 | Triby;Ronruby

Es eficiente?

Si no lo es, usare lo que tu dices Triby.

Gracias Triby y Ronruby

[Triby]

hpg147, yo leeria con "alias" asi:

SELECT u.*, m.* FROM mp u, mp2 m WHERE u.usuario = 'usuario' AND u.tipo = 'carpeta' AND m.id = u.mensaje_id ORDER BY u.mensaje ID DESC LIMIT 20

si el ID de mensaje es con incremento automatico se supone que el orden tambien seria cronologico y me fio mas de solicitar el orden por la tabla primaria que por la unida, cabe decir que no soy muy ducho con esto de los JOIN.

Ahora, como alcaracion: las dos opciones (1 y 2 tablas) pueden representar problemas de espacio.

Usando 1 tabla, no todos los usuarios almacenan copia de sus mensajes enviados, pero esto puede "solucionarse" al poner un limite de mensajes para almacenar, digamos 50 en ambas carpetas.

Usando 2 tablas puedes enviar copias a muchos usuarios, pero debes tener en cuenta que cada que alguno lo borre de sus carpetas, verificar si el mensaje ya fue eliminado por todos los usuarios, para eliminarlo tambien en mp2

Por cierto, el DE y PARA yo los pondria en la tabla MP, dejando solo el autor en MP2, ya que si envias el mismo mensaje a varios usuarios, en MP2 solo creas 1 registro y en MP creas uno para cada destinatario.

Y no le veo mucho sentido a tener ID en MP, si es necesario crear un indice seria mejor buscar otra alternativa.

Edito: ignora la ultima linea, es mas facil marcar como leido, modificar, eliminar, etc. usando ID, sorry.

[hgp147]

Muchas gracias Triby, probare como dices.