Results 1 to 8 of 8

Thread: Database Design Question

  1. #1

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

    Database Design Question

    I'm working on my first very large database application; essentially I need to allow businesses add employees to allow them to input customers/prospects and then those entries will be automatically emailed.

    For my business table I'm thinking that this will be the schema:
    Code:
    Name         | Type
    Email        | varchar(50)
    Signature    | varchar(max)
    [Name]       | varchar(25)
    Phone        | varchar(20)
    Fax          | varchar(20)
    Address      | varchar(50)
    City         | varchar(25)
    State        | varchar(2)
    Zip          | Char(5)
    HasReminders | Boolean
    IsActive     | Boolean
    And the entries (such as customer/prospect/business) schema would look like this:
    Code:
    Name         | Type
    [Type]       | Int
    FirstName    | varchar(25)
    LastName     | varchar(25)
    Phone        | varchar(20)
    Email        | varchar(50)
    Address      | varchar(50)
    City         | varchar(25)
    State        | varchar(2)
    Zip          | Char(5)
    I'm not too worried about the business or entries tables because they are fairly straight forward. What I'm having difficulties wrapping my head around is the email table(s).

    Basically I need a way for not only the system admin (not the businesses, but the company I'm selling this to) to be able to create templates that the users can take advantage of for one time emails, but I also need to have some emails campaigns to automatically be sent out at certain triggers (which will be updated via my software and web applications). To make it even a little more confusing, I need to be able to track each one-time email and each auto-email results (like how many were accepted, rejected, unsubscribed, etc. for each campaign).

    Would this be a situation where relational databases would play a role? Would the one-time emails and auto-emails be two separate tables or one single table that the sent date field gets updated? HALP MEH!
    "Code is like humor. When you have to explain it, it is bad." - Cory House
    VbLessons | Code Tags | Sword of Fury - Jameram

  2. #2
    Frenzied Member
    Join Date
    May 2014
    Location
    Central Europe
    Posts
    1,372

    Re: Database Design Question

    from what i understand i see two more tables:
    EmailTemplates which stores the template for an email. The Body will propably contain placeholders for names etc.
    EmailTracking which stores results for each sent email

    when an email is to be sent, the user or automated Job takes a template (you may want to add references which template shall be used/selectable for each recepient - that info would Need to go somewhere) replaces the placeholders, sends the email and adds a record to the EmailTracking table.
    In the EmailTracking you then update if the mail was accepted, rejected etc. i would also add a boolean "active" field (or something like that) to that table to close old mails with no Response, otherwise you might end with many dead mails sent a year ago which you still track.

  3. #3
    PowerPoster ChrisE's Avatar
    Join Date
    Jun 2017
    Location
    Frankfurt
    Posts
    3,046

    Re: Database Design Question

    hello dday,

    like the Shaman said a Template is where I would start to.

    The Template should consist all possibilities that can ocur,
    from that Template(tables) I would create a sort of Wizzard(Select per Checkbox) to create Tables
    for the diffrent situations.

    The Admin could specify the Tablename with the selected Data(per Checkbox) an Create the table. I know this might
    be to much effort, but it sounds like this Project can develop into something that will
    become hard to Track, and keep on top of.

    mabye I'm going overthe top, but with clear Table names it should be easier to Trak the Status.

    regards
    Chris

  4. #4
    Frenzied Member
    Join Date
    Dec 2014
    Location
    VB6 dinosaur land
    Posts
    1,191

    Re: Database Design Question

    Just a suggestion, but why don't you take some time to study how email marketing programs do it? I have only used phplist in the past but I know many others exist. Install and study their features and db structure.

  5. #5
    Smooth Moperator techgnome's Avatar
    Join Date
    May 2002
    Posts
    34,532

    Re: Database Design Question

    tblEmail
    ----------------
    ID
    ParentID <-- points to the parent record business or individual
    [Type] <--- type of email (in our system some people have more than one email, home, work, etc)
    Flag <--- here you can set bit flags to tell you what you can (one time email, template) or cannot (do not email) do with the email or optionally break them out into different fields.


    To make it even a little more confusing, I need to be able to track each one-time email and each auto-email results (like how many were accepted, rejected, unsubscribed, etc. for each campaign).
    This I'd probably store in a new table: EmailResults... store the EmailID, the date and the disposition, plus anything else you might need to know.

    -tg
    * I don't respond to private (PM) requests for help. It's not conducive to the general learning of others.*
    * I also don't respond to friend requests. Save a few bits and don't bother. I'll just end up rejecting anyways.*
    * How to get EFFECTIVE help: The Hitchhiker's Guide to Getting Help at VBF - Removing eels from your hovercraft *
    * How to Use Parameters * Create Disconnected ADO Recordset Clones * Set your VB6 ActiveX Compatibility * Get rid of those pesky VB Line Numbers * I swear I saved my data, where'd it run off to??? *

  6. #6

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

    Re: Database Design Question

    This is ultimately what I think that I'll go with:
    Code:
    Table: Business
    ID           | Auto-generated Number | Not Null
    Email        | varchar(50)           | Not Null
    Signature    | Blob                  | Not Null
    Name         | varchar(50)           | Not Null
    Phone        | char(10)              | Not Null
    Fax          | char(10)
    Address      | varchar(50)           | Not Null
    City         | varchar(25)           | Not Null
    State        | char(2)               | Not Null
    Zip          | char(5)               | Not Null
    HasReminders | boolean               | Not Null
    IsActive     | boolean               | Not Null
    
    Table: Users
    ID         | Auto-generated Number | Not Null
    BusinessID | int                   | Not Null | FK
    RoleTypeID     | int               | Not Null | FK
    FirstName  | varchar(25)           | Not Null
    LastName   | varchar(25)           | Not Null
    Email      | varchar(50)           | Not Null
    Password   | varchar(25)           | Not Null
    
    Table: Email
    ID          | Auto-generated Number | Not Null
    EmailTypeID | int                   | Not Null | FK
    Subject     | varchar(25)           | Not Null
    Body        | blob                  | Not Null
    Description | varchar(50)
    
    Table: Queue
    ID             | Auto-generated Number | Not Null
    BusinessID     | int                   | Not Null | FK
    EmailTypeID    | int                   | Not Null | FK
    CustomerTypeID | int                   | Not Null | FK
    SendDate       | date                  | Not Null
    
    Table: Result
    ID             | Auto-generated Number | Not Null
    BusinessID     | int                   | Not Null | FK
    EmailTypeID    | int                   | Not Null | FK
    CustomerTypeID | int                   | Not Null | FK
    SentDate       | date                  | Not Null
    Accepted       | int
    Rejected       | int
    Delivered      | int
    Failed         | int
    Opened         | int
    Clicked        | int
    Unsubscribed   | int
    Complained     | int
    
    Table: Entry
    ID             | Auto-generated Number | Not Null
    BusinessID     | int                   | Not Null | FK
    UserID         | int                   | Not Null | FK
    CustomerTypeID | int                   | Not Null | FK
    FirstName      | varchar(25)           | Not Null
    LastName       | varchar(25)           | Not Null
    Email          | varchar(50)           | Not Null
    Phone          | char(10)              | Not Null
    Address        | varchar(50)
    City           | varchar(25)
    State          | char(2)
    Zip            | char(5)
    IsWinback      | boolean               | Not Null
    WinbackDate    | date                  | Not Null
    IsUnsubscribed | boolean               | Not Null
    
    Table: fkTypes
    ID          | Auto-generated Number | Not Null
    Description | varchar(25) | Not Null
    Where the fields that have type in their name (RoleTypeID, EmailTypeID, and CustomerTypeID) will be foreign keys linked to the fkTypes table where the Description field will hold their values. My idea is to essentially use them like an Enum in Visual Basic.

    What are y'all's thoughts on this? Something that I'm unsure of is the Entries table where I have both the UserID and BusinessID field, realistically I could get the BusinessID from the UserID using a query like:
    Code:
    Select [BusinessID] From [User] Where [ID] = Entry.UserID
    So it seems like it is probably unnecessary.
    "Code is like humor. When you have to explain it, it is bad." - Cory House
    VbLessons | Code Tags | Sword of Fury - Jameram

  7. #7
    Superbly Moderated NeedSomeAnswers's Avatar
    Join Date
    Jun 2002
    Location
    Manchester uk
    Posts
    2,660

    Re: Database Design Question

    one thing i have moved away from is just calling my ID columns ID. I tend to prefix them with the table name for clarity so - users_id , email_id. It makes it easier to read when your doing lots of joins in your SQL select statements.

    Also i know our DBA here hates uppercase characters in table names so all our table names are lower case, although admittedly i dont know if this is just his personal preference
    Please Mark your Thread "Resolved", if the query is solved & Rate those who have helped you



  8. #8
    Frenzied Member
    Join Date
    May 2014
    Location
    Central Europe
    Posts
    1,372

    Re: Database Design Question

    table "Email": these are your email templates? i would add a boolean field isActive here. do you need to have separate templates for separate Businesses? or separate user roles? then you need a x-ref table idEmailTemplate,idBusiness
    "Entry": these are the receipiets for the emails? why do the need a UserID?
    generally are you mixing up terms? there is a CustomerTypeID but no Customer table. maybe the Entry table should be named "Customers"?

    so assuming you want a "User" of a "Business" to be able to send an email generated from an "Email" template to one of the "Entry" entities, then i'd combine the "Queue" and "Result" table to just one table lets call it "Results" to stay with your terms and design it like that:
    Table: Result
    ID | Auto-generated Number | Not Null
    BusinessID | int | Not Null | FK
    EmailTemplateID | int | Not Null | FK
    CustomerID | int | Not Null | FK
    SentDate | date | Not Null
    Accepted | date
    Rejected | date
    Delivered | date
    Failed | date
    Opened | date
    Clicked | date
    Unsubscribed | date
    Complained | date

    keep track of sent mails and the result for each sent mail in that table.

Posting Permissions

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



Click Here to Expand Forum to Full Width