CODEWITHSUNDEEP
database-designrelational-databasedatabase-schema
SrgHartman
SrgHartman

Reputation: 651

Email list database design

What is an efficient way to store sent emails for a email list database? I'm having some trouble figuring it out.

Right now, to simplify I have something like this:

Lists:
ID,
Name

Subscribers:
ID
Email
Name

ListSubscribers:
ID
SubscriberID
ListID

Messages:
ID
Title
Content
ListID

So far so good... The problem is figuring out what is an efficient way to store sent and to be sent emails as well as email sending status.

For instance, I might have hundreds of lists with each one having tens of thousands of subscribers. To know the status of each mail, I would have to store the details about each message:

MessageStatus:
ID
MessageID
SubscriberID
Status (processing, sent, soft bounce, hard bounce)

With a handful of lists and hundreds of thousands of subscribers this can balloon to millions of messages in just a few days.

Is there a more efficient way to do this?

Upvotes: 1

Views: 1740

Answers (2)

Joel Brown
Joel Brown

Reputation: 14418

Since you are asking the question, I'll assume that the amount of space taken up by the MessageStatus table will be an issue for you. You should challenge this assumption, however.

Given that you need to manage the space for MessageStatus, you could operate from the perspective of a work list rather than an audit log - unless of course you need to have an audit log for some reason.

To do this, use your MessageStatus table as you've defined it, except that when the record gets to "SENT" status, delete it instead of updating it. This way, you only have as many records as there are emails that have yet to be sent.

Upvotes: 1

Scotty Boy
Scotty Boy

Reputation: 336

  • List(table): ListID(PK), ListName

  • Subscriber(Table): SubscriberID(PK), ListID(FK), FirstName, LastName, EmailAddress

  • Email(table): EmailID(PK), ListID(FK), Subject, Content, SendDate

Upvotes: 1

Related Questions