dcsimg
Results 1 to 4 of 4

Thread: Assistance in Design

  1. #1

    Thread Starter
    Super Moderator dday9's Avatar
    Join Date
    Mar 2011
    Location
    South Louisiana
    Posts
    9,495

    Assistance in Design

    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:
    Code:
    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.

  2. #2

    Thread Starter
    Super Moderator dday9's Avatar
    Join Date
    Mar 2011
    Location
    South Louisiana
    Posts
    9,495

    Re: Assistance in Design

    After thinking on this, I think that I am going to structure my tables like this:
    Code:
    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:
    Code:
    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.

  3. #3
    Frenzied Member
    Join Date
    Oct 2001
    Location
    Idaho Falls, Idaho USA
    Posts
    1,208

    Re: Assistance in Design

    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.

  4. #4

    Thread Starter
    Super Moderator dday9's Avatar
    Join Date
    Mar 2011
    Location
    South Louisiana
    Posts
    9,495

    Re: Assistance in Design

    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.

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •  



Featured


Click Here to Expand Forum to Full Width