Hi guys,
Thank you Emil, Rick and Colin.
Emil:
I didn’t exactly get your question. Are you looking for a way to search for the records ? If the number of rows is huge and you need full text search, you may need to look at Solr or Sphinx or AWS CloudSearch for fast queries. If time is not a big concern, you may even be able to do away with simple ActiveRecord queries (do look at squeel gem too : https://github.com/ernie/squeel ).
Yes, part of it is looking for a way to search for the records. And yes, number of rows is huge. There are around 80,000 rows split in multiple Excel sheets.
I am thinking I may have to split this one database table into more tables that can help with the search.
Rick:
Assuming you have little Ruby experience and no Rails experience I think your best plan would be to familiarize yourself with RubyOnRails tutorials at: http://guides.rubyonrails.org/getting_started.html (a full tree provided by the RoR development group covering major Rails components), and: http://ruby.railstutorial.org/ruby-on-rails-tutorial-book (a very good book by Michael Hartl with the apt sub-title “Learn Web Development with Rails”). Both are free online.
I finished the first chapter of Rails Tutorial, and I have this https://github.com/arslanfarooq/first_app and http://fast-beyond-5656.herokuapp.com/
This was really fun and I believe when I finish this whole tutorial I’ll learn a lot.
But I have a 2 week deadline (actually I set the duration myself when they asked me how long will it take). So today I decided to first make this in PHP. Once this app is finished and tested in PHP, then I’ll come back to making it in Rails.
Your application looks like a good one for a first effort with two models: User and Item. Members of the User class can have roles associated with them, i.e.: Manager, Employee, and Administrator. The Item class will be a direct map of the spreadsheet, members will have fields that mirror the spreadsheet columns. The Item search can be nicely built into the item controller’s index method and the item edit restriction can be enforced by keying off the user’s role in the controller methods and views.
What you are saying is kind of making sense, and I feel this may serve like a rough plan for what I want to achieve. Thank you!
I would suggest that, at least initially, you use a snapshot of the company’s spreadsheet to populate a database that you will use for all development, test, and functional acceptance.
I actually did this today in PHP, I imported the Excel data in the database. And the search form is working… kind of. It is pulling the results, but I need to grab a relational database book and look for ways to optimize my search query.
I have decided to make two search forms. One search form to search using details of a product, and another form to search using details of a person.
My SQL knowledge is very basic, and to give a clear idea of my skill level, I am a WordPress developer. So when I said I know how to make this in PHP and MySQL; I should have said I have an idea how to make this in PHP and MySQL.
Right now I need to formulate a smart query when search form is submitted. My current query is too simple and does not give the desired results in many cases. Gives desired results in one particular way 
Which sucks.
You can then segregate out a separate task to either provide functions to import and export the excel based data. I would suggest that the final design might want to have the data held in a database with a publish capability with an excel format (and/or pdf, and/or …) that has the published date/time as part of the page header.
Client did say to implement the print search results feature. So I’ll use the pdf option.
Be sure to learn how to test your app, validate your data, and have fun.
Thank you Rick!
Colin:
+1 to those suggestions. To start developing an app before working
right through a good rails tutorial will just result in wasting a lot
of time.
Yes I completely agree.
The railstutorial.org example will give you some useful
stuff such as user authentication that you can use in your app
I will definitely finish that tutorial once this app is done in PHP.
And for that, I just need to get the search to work nicely. I already implemented Twitter Bootstrap for PHP version so interface is already good and responsive.
Thank you guys for your help and input. God bless you all.
I don’t want to ask for the SQL here. I’ll use appropriate forums etc.
– arslan