Pros/Cons of using RoR for Databases, over MS Access

rubyonrails-talk
1

I am new to RoR and had a question that I did not see specifically address here or anywhere else…

My current job uses many databases that someone created years ago from MS Access. Some of these databases simply store/track data, some contain templates to fill in info on a form that prints off a PDF, but the one we use most often is to send requests to support staff in other offices or working remotely (fill out a form, often sent with very large PDFs attached). These MS Access databases are very slow and often get corrupted out of nowhere and/or require maintenance. Sometimes there are issues that pop up that require everyone to get out of the database before they can be fixed, but inevitably someone leaves their computer with the database open, which causes more delays.

I know that I can create an app w/RoR that store/tracks data and that has forms/templates that a user can fill out. I believe that RoR could fix these other issues (speed/maintenance while others continue to use the app/etc.) but I am not 100% sure about the following:

  • Can RoR apps be used privately on a server? Not exactly sure how our current databases work, but you can only access them when on company network/server. Are RoR apps only for webpages accessed through internet?
  • Can you send large documents interoffice quickly/efficiently w/an RoR app?
  • Do RoR apps ever get corrupted like MS Access databases?
  • Do updates/fixing bugs/maintenance/etc. require everyone to exit the app like on Access?

I just want to know that RoR can be as big of an upgrade that I think it can be, before I continue down this road of attempting to recreate these databases. I’d like to also talk to my boss about it before I put in the time to make sure they are open to switching, should I be able to create something better. I’ve been doing pragmatic studios courses, which have been awesome but I think I need to actually do something like this in order to continue learning. Thanks in advance for your help, and if anyone has any additional tips/etc. I would greatly appreciate it.

2

Wow. Access. That takes me back to the late 90s, when I was building my first interactive sites using IIS, in collaboration with “the IT Guy”, who had his MCSE and only spoke Windows. Back then, the advice was to migrate the Access tables (which are basically similar under the hood to SQLite) to MS SQL Server whenever the going got rough. There was even a tool to do that, IIRC.

To answer another of your questions, Rails can be configured to work with a wide range of databases, and it can be networked and secured in any way that any other Web application can. I have seen a lot of “intranets” and “extranets” built with it, and your IS team should be able to integrate it within your network.

Rails apps don’t get corrupted, per se. Their databases can, certainly, but that’s really rare if you’re using a modern database engine and are following good development practices in your application code.

The problem with Access, which it shares with SQLite, is that it really can’t deal with contention or multiple requests at a time. It’s an excellent mediocre choice for a single-user application, but any Web application is by nature multi-user and possibly multi-threaded. It’s a complete mis-match for any serious work, even internal work.

It is certainly possible to build a Web form that will upload even gigabytes of data. Modern “chunked upload” JavaScript widgets can even make that recoverable in the unavoidable case of the desktop browser crashing or the network stalling. You would not want to do that if you are hosting on IIS, but if you used NGINX or Apache as your server, and a decent Ruby application server behind that, you could certainly tune things such that really large uploads succeeded on a regular basis.

Sending them (up to or down from) the server would not be the ultimate gating factor. Your network (both logical and physical layers) would be.

I built a workflow system for Comedy Central a number of years ago, where their editors were uploading full-broadcast-resolution program promotion videos and their art directors and lawyers were downloading them to review and approve. The server ran ffmpeg in a background job, which decoded the full-res version and transcoded it into a Web-deliverable format, so only the upload was painful. But this had to run on several large AWS EC2 virtual servers (behind a load balancer), and all the files went to S3, in a protected bucket. That way the downloads didn’t actually occupy the Rails server at all, and there was usually a free instance of the application ready to receive new uploads.

As far as updates go, there are all sorts of mechanisms you can engineer, some of which would allow a completely seamless cutover between new and old versions of the application. Nothing would really preserve a connection between an uploading browser and a receiving server if the latter was forcefully restarted, but if you had built a recoverable upload system as noted above, the user might not notice it. I have no idea what you mean about everyone “exiting the app”, that’s not a thing in Rails land.

Finally, it sounds to me like your basic problem is using Access instead of a database. Just migrating to MS SQL would probably improve the existing setup immeasurably.

Good luck with your exploration, and I hope you enjoy learning Ruby and Rails!

Walter

3

Hey, thanks this was very helpful!