How Can I Sync a Calendar to Mobile Devices

Question

User1064676031 posted

Hello everyone!  I'm re-writing this for the second time because I accidently clicked a link and lost all my data!  So without further ado:

A while back, I built a website using the Club Web Site Starter Kit.  I recently went back and re-visited that site, and had some ideas I would like to implement.  I would appreciate links and anything that you think would help me.  I want to learn a lot of stuff through this.  The trouble is, I have tried searching Google, but I don't even know where to start.  Here are some of my ideas:

1) "Private" syncing to mobile devices: My club has private and public calendars, so when syncing to mobile devices it would be important that they type in a username and password, much like Google Mail and Calendar.  Would I need something installed on the server (like Exchange), or can this be done from a "regular" website?  Perhaps I would need to build applications for each mobile device?  My preference would be to enable syncing from a "regular" website, if this is at all possible.  But I am open to suggestions.

2) Modifiability: It would be nice if users could add and modify small "group" events from their mobile devices, but obviously they must not be able to modify the club events.  This wouldn't really be essential, but would be nice if it were possible.  Another thing I'd like, would be to enable them to sync their personal calendars to the site, if they wish.  Again, if the only way to do this would be through apps, just tell me so and I'll be looking into it.

3) Real-time updating, like Twitter, except inside a calendar, would be really awesome!  I suppose this would require Ajax, or something similar, which is not a problem.  I suspecting that I might not be able to use the built-in original Clubsite Calendar without heavy modification though, so suggestions would be more than welcome.  Do I take the time to modify it?  Are there better options out there?

4) I want to make this ultra expandable, so that I can add multiple clubs on, etc.  People will need to see the calendars from the clubs they join (as well as their personal calendar, possibly).  Also, I want them to be able to see a list of "their" events.  What this means is: all their personal events, and all the events that they are in charge of.  It would be their personal schedule, eliminating all the events they are not going to.  This leads to question 5:

5) What is the most efficent way to store data?  I figure I'll use an MSSQL database, of course I am open to suggestions here.  I have 6+ events per day for the club (yea, it's a lot: that's why I have to store the data efficient for retrieving it).  How should I store the data?  Should I have one table per club?  What about the group events (editable by club members), should those be in another table?  And personal events; should every user have their own table on the database?  Or should everything be contained in one great, big table?  What's the most efficient way?

6) Where do I start?  If you can answer even just one of these questions, please do!  Like I said, I have googled, but don't even know really what to search for, because I don't know what I'm looking for.  Hopefully other people will be able to answer the questions you don't know the answers to!  :)

Thank you in advance for the help!  I hope I remembered everything, but if I did, I'll let you know!  :)

~Joe

Answer 1

User-364289655 posted

1. This is doable, but I think it's really going to be device specific. For the IPhone you want to serve up an ICal format/service. The way google does this is you go into your google calendar settings and select iCal, and you will get a private URL. Open the iCal application on your iPhone, and then plugin the url and you will get the data. This URL is of course open to anyone who has your secret url. No special application for the iPhone needed. I assume the other phones work similarly, but I do not know for sure.

2.  I'm not entirely sure how to guide you on this one, if you research more on the iCal format, and how that works with google and the iphone or whatever, you'll get more details. I believe once you integrate using the secret URL it will share data back and forth, so if they add something in their iphone they can chose to have it sync back to that calendar. More googling would be needed :)

3. Again, integration with the iCal would update however it updates - when the calendar is loaded presumably and maybe on some set time schedule. Not sure, but the syncing should happen without the user needed to do anything special

4. Not sure, you'll need to research how the iCal integration works, but I assume your secret URL can serve up whatever data you want. So you could pull in all events where this user says they are going to, or all events this user can see (going to or not) etc.

5. 

Event { EventId(PrimaryKey, Int), ClubId(int), GroupEvent(bool) MoreFields... }

If it's a GroupEvent = True, then you can have your code display edit functionality, if not, then it's private to the club owner or whoever can edit non group events. You might need to add in an event OwnerId, so that if it's not a GroupEvent, then only the Owner can edit it.

ClubId will link it to the particular club, so you can use one table.

 

6. Most of what I've written is what I think is possible based on what I've read, I've never done any calendar integration, but hopefully it gives you some more to look into

Answer 2

User1064676031 posted

Thank you for the replies!  I have been reading what you wrote and finding lots of information!  I really appreciate all the pointers you have given me.  I appologise for for the delay in getting back.  It's not because I'm not interested, but rather because I am very interested and am spending all day Googling for answers based on what you have told me.  Here are a few more questions I have, base off of your replies and what I have learned.

1) From what I can figure out, I need a server that supports CalDAV to "serve up an iCal format/service".  You are correct about the Google's "private" URL.  They have that option, and I am able to reproduce that (or something similar).  All that's necessary is to put an *.ics file on your host, and the link to it like webcal://example.com/hidden/calendar.ics  Then, just click on the link on iPhone and you'll be subscribed.  However, Google calendar can also be synced to iPhone through a password (I think it uses Microsoft exchange), so I think that I would have to set up a CalDAV server to do this, from what I have learned.  Do you know of any hosting servers that offer this functionality?

2) So I did more Googling.  I have come to the conclusion that a CalDAV server is necessary for sharing data back and forth.  The private URL is insufficient, probably because someone could start editing your calendar if they do get hold of your private URL.

3) Sorry I wasn't too clear on this point.  I switched from mobile to web-based.  I would like a website calendar, like the clubsite eventscalendar, that would update using AJAX, live (real-time) from the server.  It's not necessary, but it would be cool.  I find that the Clubsite integrated calendar is a little slow with 6 events per day, after a couple years' worth of data.  This could be due to my server's speed, of course.  Basically my question was, is there a better calendar out there than the default Clubsite one?

4) So yes, I think you're right.  I can make it serve the data I want.  I'm going to figure out how, but I have the information I need to learn more.  So for now, this one is solved.  :)

5) Basically then, you would recommend that I use one database table to store all my calendars' event data?  I figure that eventually I could end up with 100,000+ events per year.  (Yea, I'm dreaming big!)  I think it makes sense, but I'm wondering if it would be slow to query the database eventually.  I'm going to have two basic views on the website: 1) The individual clubs' view.  This means that club members can log on and see all the events for that one particular club.  2) The individual users' view.  This means that users will see only "their" events, but from all the clubs (and probably personal events, as well).  So I'm wondering: is it more efficent to create 1 table for each club, and just "join" them when I wish to display individuals' calendars, or is it better to have one huge table, and never have to join tables?  I believe one table would make simpler queries, but would it be faster for the server, and especially for the end-user?

6) Thank you for all your help!  I really appreciate it very much!  You have given me pointers, which is realy what I was looking for, and I'm already just a little bit more intelligent from your help and then Google's.  :)

Thank you, and if you have more suggestions/answers, please let me know.  What I may end up doing to sync privately is to force users to sign in with a Google account on my site, then my site will sync to the Google Calendar, and then the Google Calendar can sync with whatever they want.  Complicated, I guess, but it would give them the option.  Otherwise, their calendar is (theoretically) discoverable.  I may leave both options available to them, and let them choose.  The ideal would be a CalDAV server, I believe, but I can't find one anywhere.

~Joe

Answer 3

User-364289655 posted

Just shifting through your responses and figuring out how to respond to it all - but really quickly for #5

One table for each club is definitely not the way to approach this. What if you get one million clubs all with one event? It's the same data, it's way easier to keep it in the same table and just at a club key. I'm no SQL dba, but if you put the right indexes on your table, it should stay extremely fast for millions of rows and keep your queries and code (and your database) simple. It might be slightly faster if each club had a table and you only wanted one club. But if you are doing a bunch of joins, my intuition says that that will eat up more resources than one table with millions of records. Not to mention the hassle and complexity needed to be added to your code base and queries.

Answer 4

User1064676031 posted

Thank you for the reply for #5!  That was exactly what I was looking for!  There isn't much chance at all that I would get one million clubs with one event, but I understand what you're saying.  And I have to admit that coding will be way easier that way.  I just wasn't too sure how fast querying millions of rows would be, but I think you've answered this really well.  In fact, I think I'm going to go to the drawing board for my database and tables right away!

I've done even more research on calDAV servers, and even contacted a couple hosts.  It's all rather confusing, and I'm under the impression that many people don't host calendars that way.  They just offer a download or "private" URL.  So I think I may just go for my idea to enable them to connect with their Google account, and it can sync to that, which can sync to their mobile device.  That'll take some learning, but I think I know where to find the information.  Of course, I'll give them the option of a private URL, too, with warnings.

I am still interested in #3 about other calendar options.  I have found a few; this one is deffinately the best I've found so far: http://arshaw.com/fullcalendar/ It doesn't have built-in MSSQL and ASP.net integration like the ClubSite one, but I don't expect to find one that does.  I should be able to build that in without too much difficulty.  It'll be neat if I can learn how to live-update the calendar so that if PersonA changes the calendar while PersonB is on the calendar page, the calendar will update without PersonB having to refresh the calendar.  I have not done any Googling yet, but if you have suggestions or know of something to help me learn, please let me know!  Thanks!

Again, a million thanks for your help!  I can't tell you how much I appreciate it!