Design of an SMS Alerting System

I would appreciate any thoughts you may have on my planned design of an an application to allows our Clients to send their Users SMS text messages.

Our application allows our Clients to send their Users EMails. Typically this is to inform the Users of forthcoming events.

The Users can also see a calendar of events on the website (after logging in)

I would like to add SMS alerts for the following scenarios:

1. Client wants to alert the user - e.g. an event is cancelled because of bad weather

2. User wants to be notified by SMS - e.g. of the fact that they have been sent an email announcing a new event (I figure the Users should check their emails but believe it or not the users want this facility!!)

As I see it there will therefore be:

a) Emergency SMS messages that the Client will pay for - e.g. notification that an event is cancelled.

b) Informational SMS messages that the User will pay for - e.g. "New event XYZ on 10th December, see email for details"

I also think that the user may want to enter additional mobile phone numbers, and the Client will probably want the user to pay for those additional messages (i.e. Client will only pay for emergency messages to the User's Primary phone number)

So my design thoughts are:

Add an SMS Text field (160 characters max) to our current Bulk Mail form / database table.

Add SELECT list:

• No SMS
• SMS Client pays
• SMS Client pays primary only
• SMS User pays

We currently have a Mobile Phone field in the user's Membership record. I reckon I need a child table so that users can have multiple phone numbers, and nominate which ones they want to pay to have notification messages sent to, and indicate which is the "Primary" phone.

Table: MobilePhone
Columns: MemberID, PhoneNo, DoSMSThis, IsPrimary

Next up I want to store all SMS messages. Two reasons: I guess there will be some disputes over billing and secondly I want to alert the user if they log on to the website (more on that later)

Table: SMSTextSent
Columns: DateTimeSent, SentBy (Client ID), SentTo (MemberID), ChargeTo (Client or User), SentToPhoneNumber, MessageText,
ErrorCode, BulkMailingBatchID, HasBeenDelivered

Then I need a record of who is in credit.

Client Balance Table:
AccountType (Client or User), ID (either Client or User ID), RunningBalance, LastBuyDate, LastBuyUnits

This is just an Open-Balance type record, rather than something more formally "transactional". I haven't yet figured out if Open-Balance style will do ...

Q1: Do I need a full Transaction Table?

Right, now for the processes:

Client sets up a Bulk Mailing, with templates for EMail and SMS (these contain "markers" for data to be substituted, such as the User Name etc.)

Once they've proof read everything they press SEND. Currently, to produce the Emails, I generate a resultset of the appropriate Users with their EMail details, and the application sends the emails one-by-one and stores the email details (Recipient, Subject, Text) in a SentEmails table together with any error code (as reported by SMTP).

I plan to add to this a resultset for processing SMS. This will have Mobile Phone Numbers of any of the members in the target mailing, and a flag for whether an SMS is "paid for" or not. Application will find & replace any "markers" in the template to create the personalised text message, attempt to send the message via the SMS gateway, and store the message and ErrorCode in the SMSTextSent table.

I will create a record even if either the Client or the User is not in credit for the transaction (but if they are not in credit I wont send the SMS itself)

I have no idea if the answer-back I get from the SMS gateway at this point is categorical as to whether the SMS text is deliverable, or not. So there may be another step to reconcile some sort of Daily LOG from the SMS Gateway as to which messages have been delivered. More messing around Either way, my SMSTextSent table needs to have a flag for whether the gateway says the message was delivered, or not.

Q2: What can I expect as a result-code from the gateway? Do I have to subsequently process Gateway Logs to discover what was actually delivered?

Now the user logs on to the website (lets say). At successful login I alert them to the fact that there is a recent SMS Text - assuming they haven't checked their phone!

Q3. Could I use a ReadReceipt to flag the records in SMSTextSent table as "Received"? (and give a slightly different user experience)

Part of my intention here is to encourage users / clients to subscribe to the SMS service. so:

For informational messages when a user (with no SMS credits) logs in I display the alerting message together with a "You can have messages like this sent to your Mobile Phone. Click Here"

or for "emergency messages" (for which the Client had no credit) I could display "XYZ Client is not currently sending these messages by SMS Text, if that service would be useful to you please encourage XYZ Client to use this service"

Q4: I'm hoping that other users who have done this will be able to point out pitfalls and Gotchas based on their experiences, which I would greatly appreciate before I embark on this.

and lastly: I figure there must be other, cheaper, alerting services that I could also accommodate. Id like to add those to the Member Registration record:

Q5: What services would you add? IM, Twitter, others?

EDIT: I'm in the UK, but I believe in the USA mobile phone operators have EMail addresses that route to the phone - e.g. 01234567@MyCarrier.com. This could be an additional service route - although I don't suppose it is high-priority and control over Subject/Body of the EMail message conversion to SMS Message is probably "variable" between carriers

Thanks for you help, and apologies for the length of my diatribe