0

I am tasked with creating an Email DL where managers can subscribe and unsubscribe at there convenience. In this type of a set up I am thinking that I will need a few tables.

Manager Table (first name, last name, email address) dl1 (email address) dl2 (email address) dl3 (email address)

what I will like created is the manager will subscribe and the main manager table will house all the email addresses. During the subscription process the managers will then have the opportunity to decide on which DLs there will like to receive communications from (dl1, dl2, dl3, etc).

I have access to SQLServer 2014 and will be using it for this project. Any kind of help will be appreciated. I am hoping that one of you kind folks can start me going and I can add / modify the DLs as needed.

Thanks in advance for any help / assistance you can provide.

Improve this question
12
  • A database holds data but does not offer any kind of user interface. From your description it seems you possibly need a master manager table (consider tying this to Active Directory rather than going off and building another "data silo" with a list of people in it). You also need a master "DL" (what is a DL please explain?) and another table that holds the managers that subscribe to that DL. Nowadays these things aren't built from scratch, you just find a cloud solution or do something like RSS feeds. Commented Feb 15, 2016 at 0:38
  • thanks for the tip. In this case is DL is just an email distribution list. We send out many different types of reports to many different distribution lists (DL) and maintaining these the current way it's being done is a nightmare. What we are trying to do as mentioned is to somewhat simplify the process by having the managers the ability to subscribe to what they need and unsubscribe to what they don't need. This was our email distribution lists are cleaner and only the managers who are subscribed to each list will receive the communication they subscribed to. Commented Feb 15, 2016 at 0:50
  • So you're sending reports to people. All enterprise reporting systems (IBM Cognos, SSRS etc.) have built in email list capability. How are generating the reports? If you were to build a custom email list program, what would be your preferred front end for maintenance? Web? Thick client? SQL Statements? Commented Feb 15, 2016 at 0:52
  • yes we custom build all of of reports from various systems using excel. We currently use excel to house the various email distribution lists. When we generate the report when also generate the email as well from Excel which will call the email lists. This does work for us the issue really is manually updating these email lists and keeping them up to date. I was thinking if it was possible to have these lists maintain in SQL then link to them from Excel. In terms of a front end having the Managers be able to use a web browser to subscribe or unsubscribe with SQL being the back end. Commented Feb 15, 2016 at 1:03
  • Have you considered using some kind of email contact group for this? You create a group email address (in outlook this would be a contact group). You email the report to the contact group. Managers are added or removed from these groups. The only downside would be it's not straightforward for managers to add and remove themselves. Is that the only reason you could think of that you wouldn't use a contact group or are there other reasons? Commented Feb 15, 2016 at 1:55

1 Answer 1

Reset to default
1

From your description I suggest the following tables:

This table holds your distribution lists, and information about them like the owner of the list, a description and a report name

  • DistributionList
  • Recipient
  • DistributionListMember

Here are those tables in more detail:

DistributionList

DistributionList_ID INT IDENTITY(1,1) Name VARCHAR(100) Owner VARCHAR(100) Description VARCHAR(100) ReportName VARCHAR(100)

Additional useful fields in this table would be

  • Last time the record was updated
  • Last time a report was sent out against this list

Also this assumes one report per Distribution List. If you have many reports in a list or reports that are in many lists, you need to make this more complicated (for starters you need a master report list)

This table holds the list of users and their emails

Recipient

Recipient_ID INT IDENTITY(1,1) WindowsLogon VARCHAR(100) EmailAddress VARCHAR(100) SubscriberName VARCHAR(100)

I assume this information is already in Active Directory. It's always a bad idea creating duplicated information silos that reflect data that already exists but I guess you're already doing that in Excel

This table holds the list of people subscribing to a list:

DistributionListMember

DistributionListRecipient_ID INT IDENTITY(1,1) DistributionList_ID INT (Foreign key to DistributionList table) Recipient_ID INT (Foreign key to Recipient table)

So there you go - a sample schema that holds data that you're after.

Creating a web application that references this is another story.

Improve this answer
Sign up to request clarification or add additional context in comments.

1 Comment

thanks for all your help and explanations. I will build the tables and go from there. Once I get a good foundation going then I think I'll be able to get the needed help to get the front end going. Again much appreciated your efforts and advice.

Start asking to get answers

Find the answer to your question by asking.

Ask question

Explore related questions

See similar questions with these tags.