Band App Diary #2: Database to Feed List

Since I’ve decided on going with a single feed list that’s going to represent all of the different kinds of band content that might get produced, I’ve been thinking about how to represent that in a database and how I’m going to generate the JSON the iOS app needs. I’m using a database instead of something like MongoDB because I understand databases fairly well, and because I’ve decided to use Azure Mobile Services for the server, and using MSSQL is the most straight forward option for that. I think I could probably get by deploying and hosting the app myself somewhere else, and for a future project I might do that, but the way I’m viewing Mobile Services is as a really nice front end for creating a Node app where I don’t have to think about server stuff at all. At this point it’s better if I can just focus on writing code.

So, getting back to the database, some of the things I need to represent are:

  • Shows
  • Songs
  • Videos
  • Social
    • Instagram
    • Twitter
    • Facebook

There’s probably more I’m forgetting, but that’s a pretty good start. The first way that I thought to represent all of the different feed item types was to have a big items table that would have the information for a feed item with an type ID, but that doesn’t work, because each of these things could actually be significantly different (a show song won’t have a lat/lon or venue name, but a show will).

The shows and songs tables might be have some columns like this:

  • Shows
    • unique_id
    • venue_name
    • lat
    • lon
    • date_time
    • duration
    • city
    • description
  • Songs
    • unique_id
    • media_url
    • title
    • description
    • post_date

I’m leaving some things out, but they aren’t really critical right now for what I’m trying to get at. What I want to do is get all of the different feed item types and serve them up as JSON via one API endpoint to my iOS app. In order to keep things simple, I think the best way to do that is to when the endpoint is called fetch each of my feed item types, loop through each and create an object for each with the information that’s needed to display them in the feed, add them all to an array and return that. The properties could be things like:

  • unique_id
  • title
  • description
  • thumbnail
  • type (integer value to differentiate each item type)

I could then use the type and unique ID to do things like call a “show with ID” endpoint to display a detail item. I’m almost certain that this is not the most efficient way to do this, but also that if I need to optimize it later, I should be able to do so in a way that doesn’t change anything for the client. The reason I’d like to represent each type as being the same in the feed JSON is that it makes it possible to keep the client code a lot simpler. I’m not sure if that’s the right call or not. Maybe someone with more experience can tell me if I’m way out in the weeds here.

Update 7:39 pm: I had more thoughts about this approach being a good idea or not.