-
Jul 5th, 2017, 06:07 PM
#1
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!
-
Jul 6th, 2017, 03:30 AM
#2
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.
-
Jul 6th, 2017, 04:01 AM
#3
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
-
Jul 6th, 2017, 07:16 AM
#4
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.
-
Jul 6th, 2017, 07:19 AM
#5
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
-
Jul 6th, 2017, 05:43 PM
#6
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.
-
Jul 7th, 2017, 02:46 AM
#7
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
-
Jul 7th, 2017, 03:08 PM
#8
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
-
Forum Rules
|
Click Here to Expand Forum to Full Width
|