Newsletter Table(s)

As part of my DevCampus website, I plan on allowing people to get newsletters via email or RSS. For those newsletters, I'm building a database table and trying to think of every kind of possible attribute (column) associated with my newsletters I might want to track. After asking my friend Darren for his input he suggested I blog about it to get feedback from readers and so, here you have this blog post. I'm sure a lot of you guys have built a "newsletter table" or something similar before - if so - what did your table or tables look like?

Here's what I have so far: (column name - data type - key)
Title - varchar - Primary Key (the title of the newsletter will be unique and probably include a date reference e.g. "DevCampus.com Newsletter - 9/30/2005")
CreatedDate - datetime (the date someone first creates a newsletter record)
DistributionDate - datetime (the date the newsletter is actually published/mailed)
HTMLContent - text (for subscribers that want HTML emails)
PlainTextContent - text (for subscribers that only want plain text)
PageViewsGenerated - int (to track how many hits to the website came via links in newsletters)

I'm thinking of adding "CreatedBy" and "DistributedBy" foreign key columns to track which user created and published the newsletters - just in case at some point in the future I have someone else help me administrate the site. So anyway, yeah, tell me what you think. You know you don't have anything better to do, LOL.

No Comments