sql - Conceptos derivados: consideraciones de diseño de la base de datos

performance database-design

Question

Tengo un concepto principal: mensaje de correo y dos conceptos derivados:

• correos generados por plantilla
• correos electrónicos de forma libre

Estoy pensando en crear tres tablas para guardar los datos y necesito sus opiniones sobre este asunto (si es malo, si es bueno, cómo se puede mejorar, etc.):

MailMessages

• Id [int] identidad (autoincrement) PK
• ToAddress [varchar (300)]
• IsTemplateBased [bit]
• TemplateId [int] nulo FK
• MailBodyId [int] NULL FK (TemplateId debe ser nulo y MailBodyId debe ser no nulo o viceversa)
• StatusId [tinyint] FK

MailParams

• MailMessageId [int] PFK
• ParamNumber [tinyint]
• Valor [nvarchar (4000)]

MailBody

• MailMessageId [int] PFK
• FromUsername [varchar (30)]
• FromDomainId [tinyint] FK
• PlainTextContent [nvarchar (max)]
• HtmlContent [nvarchar (max)] nullable

Algunas explicaciones están en orden aquí:

• Me gustaría un diseño lo más normalizado posible
• La columna IstemplateBased bit (boolean) se puede omitir y la naturaleza del mensaje de correo se puede deducir de TemplateId y / o MailBodyId (es decir: si TemplateId no es nulo, entonces está basado en plantillas) pero creo que esta des-normalización (?) podría ser útil para el rendimiento
• La razón detrás de tener dos columnas (FromUsername y FromDomainId) es hacer cumplir las reglas de cordura del correo electrónico - No considero que tener una sola columna (FromAddress) sea apropiada

Habiendo dicho todo esto, ¿cuáles son sus opiniones, queridos lectores?

Answer 1

Aquí hay un ejemplo del modelo "tipo / subtipo estándar" anotado por @Philip Kelley arriba:

Tienes

• SupertypeType. La tabla de dominio que restringe el dominio del identificador de tipo.

• Supertipo. El supertipo común. Existe una fila en esta tabla para cada instancia de una de las instancias de subtipo. Contiene el identificador de objeto (SupertypeID), el identificador de tipo de objeto (TypeID) y los atributos comunes a todos los subtipos.

• Subtipo. Existe una tabla para cada subtipo. Su clave principal es el id del objeto de la tabla de supertipo. Para cada instancia del supertipo, no existe más de una fila en todas las tablas de subtipos (puede que no haya filas, por supuesto, si la instancia en cuestión es del tipo base (super). Cada tabla de subtipos varía, y contiene los atributos único para ese subtipo en particular.

Para enumerar todos los elementos, consulta solo la tabla de supertipo.

Si sabe que solo es interesante en un subtipo particular, puede simplemente seleccionar de la tabla de subtipo apropiada, uniéndose a la tabla de supertipo según sea necesario para obtener los atributos comunes que necesita.

Apéndice. Si necesita una vista plana y desnormalizada de todo el conjunto, simplemente deje unir los subtipos:

select *
from Supertype t
left join Subtype1 t1 on t1.SupertypeID = t.SupertypeID
left join Subtype2 t2 on t2.SupertypeID = t.SupertypeID
left join Subtype3 t3 on t3.SupertypeID = t.SupertypeID

Ahora no necesitas múltiples consultas. Lo has cambiado por tener que lidiar con la nulidad.

Answer 2

Necesita el modelo de subtipo y también debe considerar cualquier descomposición adicional que necesite para eliminar esos valores nulos. Una tabla que permite nulos no está en forma normal.

Answer 3

Puede simplificar y hacer que se parezca un poco más al modelo tipo / subtipo estándar, así:

• Comience con MailMessags.Id como la clave principal de la tabla "tipo"
• Tener IsTemplateBased como indicador de tipo, para decir qué tipo de correo electrónico es. (Lo convertiría en una minúscula, para permitir su posible uso en índices y para más tipos en el futuro).
• Drop TemplateId y MailBodyId, no los necesitarás.
• En la tabla "subtipo" MailParams, configure MailMessageId como el PK y como un FK que hace referencia a MailMessages.Id
• Haga lo mismo con MailBody.MailMessageId

En lugar de tres claves sustitutas diferentes (y, en consecuencia, mayores posibilidades de error y confusión), ahora solo tiene una para administrar.