Assistance in Design

[dday9]

I'm creating an auto email system for a client and I need some help with designing my database following database normalization. Essentially there will be an option that the user can select and it will contain a title giving a brief overview of which marketing sequence it represents as well as a description giving the full details of the sequence. Marketing sequences hold the order that the emails will be sent along with the respective number of days the email will be sent after a customer is inserted.

I already have my email and email_queue tables. They look like this:

email:
  email_id - autoincremented primary key
  title - allows nulls, represents brief description of the email
  description - allows nulls, represents a more detailed description of the email
  subject - represents the subject line
  body - represents the HTML of the body
  customer_type_id - foreign key linking to the customer type (customer, former customer, etc.)
  template - boolean used to indicate if this email is a pre-approved template

email_queue:
  queue_id - autoincremented primary key
  email_id - foreign key linking to the email
  user_id - foreign key linking to the user that the email will be sent from
  send_date - the date that the email will be sent
  campaign - boolean used to indicate if this email is apart of a specialized campaign

The idea is that once a customer is inserted and the marketing sequence is selected, it would place the respective email(s) in the sequence into the queue where the send_date is NOW() plus however many days out the email is to be sent.

What I'm having difficulty wrapping my head around (probably due to lack of sleep) is how to structure the table(s) representing the marketing sequences.

[dday9]

After thinking on this, I think that I am going to structure my tables like this:

sequence_id - autoincremented primary key
customer_type_id - foreign key linking to the customer type
title - represents brief description of the marketing sequence
description - represents a detailed rundown on what emails will be sent out and when
required - Boolean, since the admin can select which marketing sequences he/she wants to implement, this field indicates if this marketing sequence that is required to be displayed

And then amend my email to add the following to (nullable) fields:

sequence_id - foreign key linking the marketing sequence
days_after - (if sequence_id is not null) indicates how many days after the customer is inserted it will set the send_date field to from NOW()

However, if y'all have any other suggestions, I would love to hear them out.

[jdc2000]

I am not sure I understand exactly how you are planning on having this work. It would seem that you really need more tables, depending on exactly what information you want to track:

Customer table
Contains the customer data: e-mail address, name, etc.

E-Mail table
Contains the data for each marketing e-mail to be sent out.

Marketing sequence table
Contains the list of e-mails, in order, to be sent out for a sequence.
Example:
1, email001, etc.
2, email135, etc.
2, email243, etc.

E-Mail queue table
Contains data on e-mails to send.
Example:
Date to send, Time to send, E-Mail address to send to (and other customer data), Pending/Sent indicator, etc.

[dday9]

I had the customer table, email table, and email queue table already setup from prior requirements. I really just needing help on the marketing_sequence portion.