My new project – creating an email subscription web service (Part 1)

This post starts a series of posts on me creating a certain web app. I will create this app for educational purposed as I am currently learning web development. As you may know, my specialty is iOS development, but I feel like I need to get at least some experience in server side development.

The best way to learn how to program is to build something for real, so I have chosen to create an email subscription web service. You know, the kind of service for entrepreneurs that allows them to build lists of their potential customers with emails. The one example of such an app that I know of is MailChimp. So, I’m going to do something similar.

There will be a difference though, that will make things much easier for me. The difference is that my app will be entirely mine, meaning that I will be its only user. This app will help me collect the emails of other people. Who are those other people? Maybe the future readers of my blog. Maybe future customers of my future businesses. Who knows? The thing is that creating such an app is a challenging task and it requires learning a lot of useful stuff, like programming a web app and designing a database.

I will be using Java server with JavaMail framework and MySQL database. Mind you I don’t know much about any of those things. I will be learning them along the way as I will be developing my app.

The first step: database

First of all I guess I’m going to design a database. I have a MySQL server installed on my computer. I have already created a database called email_subscription_db in it. Before creating tables and populating them with data I decided that I should learn how to use some visual tool for dealing with databases. Designing a database through Terminal window requires too much typing. So I downloaded MySQL Workbench. I looked at it and it seems easy to use. I haven’t done much with it yet, though.

Before creating tables I need to figure out exactly which tables I will need. And still before that I need to decide what things my app should be able to do, because that will determine what tables I’ll be using.

Web app features

First of all there should be multiple lists of people. And I should be able to send different emails to different lists of people. Also it should be possible to send series of emails that were prepared in advance. I should be able to see the history of my communication with my subscribers as well as stats. It already sounds too complicated for me. Let’s simplify things a little bit. I don’t need to create the whole app in one iteration. For now I can create just a tiny part of the app with the most necessary functionality.


So, what are the things that I need right now? I’ll keep multiple lists, because they are not so hard to create. But I will forget about preprogrammed series of emails for now. And no stats. Basically what I need to be able to do is choose one list of subscribers and send them one letter.

Now what tables do I need to accomplish just that? Here is a list of tables that I think I will need.

  1. people table which will contain peoples emails, names and other information
  2.  lists table which will contain basically the names of the lists of subscribers. 
  3. Lists and people have a many-to-many relationship, meaning that one person can subscribe to multiple lists, and vice versa: one list can have multiple subscribers. To connect the two tables together I need an intermediate table, which I will call Subscriptions. Each subscription will contain an id of a person and an id of a list to which that person is subscribed.
  4. Letters table. This is a table that will contain the letters that I’ll be sending to my subscribers. Each letter will belong to exactly one list. For now this table won’t contain any information on the time when the letter were sent. Or, you know what? Maybe I don’t even need this last table right now. In the first release of my app there will be no way to save letters, so there really is no need for this table.

So, that’s it. I only need three tables: people, lists and subscriptions.

Now I’m going to start playing around with MySQL Workbench.

Part 2


Leave a Reply
