sql - servidor - Diseño de base de datos para sistema de mensajes de correo electrónico

perfil de correo sql server

Question

Quiero hacer que un sistema de mensajes de correo electrónico como gmail tenga. Me gustaría tener la siguiente opción: Starred, Trash, Spam, Draft, Read, Unread. En este momento tengo la siguiente estructura en mi base de datos:

CREATE TABLE [MyInbox](
    [InboxID] [int] IDENTITY(1,1) NOT NULL,
    [FromUserID] [int] NOT NULL,
    [ToUserID] [int] NOT NULL,
    [Created] [datetime] NOT NULL,
    [Subject] [nvarchar](255) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL,
    [Body] [nvarchar](max) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL,
    [IsRead] [bit] NOT NULL,
    [IsReceived] [bit] NOT NULL,
    [IsSent] [bit] NOT NULL,
    [IsStar] [bit] NOT NULL CONSTRAINT [DF_MyInbox_IsStarred]  DEFAULT ((0)),
    [IsTrash] [bit] NOT NULL CONSTRAINT [DF_MyInbox_IsTrashed]  DEFAULT ((0)),
    [IsDraft] [bit] NOT NULL CONSTRAINT [DF_MyInbox_Isdrafted]  DEFAULT ((0))
) ON [PRIMARY]

Pero estoy enfrentando algunos problemas con la estructura anterior. En este momento, si un usuario A envía un mensaje al usuario BI, estoy almacenando una fila en esta tabla. Pero si el usuario B borra ese mensaje, también se borrará del mensaje A enviado por el usuario. Esto es incorrecto, quiero exactamente como lo hace el sistema de correo electrónico normal. Si A borra el mensaje de su artículo enviado, entonces B no debería eliminarse de su bandeja de entrada. Estoy pensando en otro problema que supondrá que un usuario A envíe un correo electrónico a 500 usuarios a la vez, de modo que, según mi diseño, tendré 500 filas con cuerpos duplicados, es decir, sin una memoria eficiente para almacenarlo. ¿Podrían por favor ayudarme a hacer el diseño de un sistema de mensajería?

Answer 1

¿POR QUÉ ELIMINAR? Creo que no hay necesidad de eliminar nada. Solo ocúltalo, de los usuarios cuando se eliminan. Debido a que será un problema comprobar ambas partes, cuando el remitente envíe el mismo mensaje a muchos destinatarios. Luego debe verificar y marcar a todos los destinatarios. Si todo está bien, entonces elimine ... Creo que no hay necesidad de borrar nada.

Answer 2

Creo que necesitas descomponer tu esquema un poco más. Almacene los correos electrónicos por separado y asigne las bandejas de entrada a los mensajes que contienen.

Answer 3

Necesitas dividir tu mesa para eso. Podría tener el siguiente esquema y estructura

CREATE TABLE [Users]
    (
      [UserID] INT ,
      [UserName] NVARCHAR(50) ,
      [FirstName] NVARCHAR(50) ,
      [LastName] NVARCHAR(50)
    )

CREATE TABLE [Messages]
    (
      [MessageID] INT ,
      [Subject] NVARCHAR(MAX) ,
      [Body] NVARCHAR(MAX) ,
      [Date] DATETIME,
      [AuthorID] INT,
    )

CREATE TABLE [MessagePlaceHolders]
    (
      [PlaceHolderID] INT ,
      [PlaceHolder] NVARCHAR(255)--For example: InBox, SentItems, Draft, Trash, Spam 
    )

CREATE TABLE [Users_Messages_Mapped]
    (
      [MessageID] INT ,
      [UserID] INT ,
      [PlaceHolderID] INT,
      [IsRead] BIT ,
      [IsStarred] BIT 

    )

Diagrama de base de datos: texto alternativo http://codeasp.net/Assets/Uploaded-CMS-Files/13f15882-7ed9-4e22-8e2c-20c6527522317-31-2010%2012-51-50%20AM.png

En la tabla de usuarios, puede tener usuarios. "Mensajes" denota la tabla de mensajes. "MessagePlaceHolders" denota la tabla para marcadores de posición para mensajes. Los marcadores de posición pueden ser bandeja de entrada, artículo enviado, borrador, correo no deseado o basura. "Users_Messages_Mapped" denota la tabla de asignación para usuarios y mensajes. El "UserID" y "PlaceHolderID" son las claves foráneas. "IsRead" e "IsStarred" significan su nombre. Si no se encuentra ningún registro para un messageid en particular en la tabla "Users_Messages_Mapped", ese registro se eliminará de la tabla Messages ya que ya no lo necesitamos.

Answer 4

Puede crear una tabla para MessageContacts que une cada mensaje a las personas que lo tienen en sus buzones de correo. Cuando un usuario elimina un mensaje, se borra una fila de MessageContacts pero se conserva el mensaje original.

Podrías hacer eso ... pero te sugiero que no lo hagas. A menos que sea un ejercicio académico establecido por su tutor, seguramente es una completa pérdida de tiempo desarrollar su propio sistema de mensajería. Si es tarea, entonces deberías decirlo. Si no, entonces ve a hacer algo más útil en su lugar.

Answer 5

Si estás haciendo un trabajo orientado a documentos, te sugiero echar un vistazo a CouchDB . Es sin esquema, lo que significa que problemas como este desaparecen.

Echemos un vistazo al ejemplo: A envía un mensaje a B y B lo borra.

Tendría una única instancia del documento, con los recipients enumerados como un atributo del correo electrónico. A medida que los usuarios eliminen los mensajes, elimínelos de la lista de destinatarios o agréguelos a una lista de deleted_by o lo que elija.

Es un enfoque de los datos mucho más diferente al que está acostumbrado, pero puede ser muy beneficioso tomarse un tiempo para considerarlo.

Answer 6

Si yo fuera usted, establecería dos indicadores uno para el remitente y otro para el receptor si ambos indicadores son verdaderos, entonces el mensaje debería eliminarse de la base de datos; de lo contrario, consérvelo en la base de datos pero ocúltelo de quién lo eliminó.

Haz lo mismo con la basura. Es posible que desee ejecutar cron o verificar manualmente si tanto el emisor como el receptor eliminan el mensaje y luego lo eliminan de la base de datos.

Answer 7

Un mensaje solo puede estar en una carpeta a la vez, por lo que desea una tabla de carpetas (que contenga las carpetas ''Papelera'', ''Bandeja de entrada'', ''Archivar'', etc.) y una clave externa de mensajes a carpetas. Para las etiquetas, tiene una relación muchos a muchos, por lo que necesita una tabla de etiquetas y también una tabla de enlaces (labels_ messages). Para protagonizar, una columna de bit simple debería hacer, lo mismo para ''no leído''.

Answer 8

CREATE TABLE `mails` (  
  `message_id` bigint(20) unsigned NOT NULL AUTO_INCREMENT,  
  `message` varchar(10000) NOT NULL DEFAULT '''',  
  `file` longblob,  
  `mailingdate` varchar(40) DEFAULT NULL,  
  `starred_status` int(10) unsigned NOT NULL DEFAULT ''0'',  
  `sender_email` varchar(200) NOT NULL DEFAULT '''',  
  `reciever_email` varchar(200) NOT NULL DEFAULT '''',  
  `inbox_status` int(10) unsigned NOT NULL DEFAULT ''0'',   
  `sent_status` int(10) unsigned NOT NULL DEFAULT ''0'',  
  `draft_status` int(10) unsigned NOT NULL DEFAULT ''0'',  
  `trash_status` int(10) unsigned NOT NULL DEFAULT ''0'',  
  `subject` varchar(200) DEFAULT NULL,  
  `read_status` int(10) unsigned NOT NULL DEFAULT ''0'',  
  `delete_status` int(10) unsigned NOT NULL DEFAULT ''0'',  
  PRIMARY KEY (`message_id`)  
)

Puede usar esta tabla para almacenar los correos y manipular las consultas según los buzones de correo. Estoy evitando el resto de las tablas como los detalles del usuario y la tabla de detalles de inicio de sesión. Puede hacerlos de acuerdo a su necesidad.