Web based database on premise

We have a spreadsheet we use to track information that has 15 or so fields.    We keep a separate sheet for each site and managing these has become cumbersome.     I am looking to put this into a database.     I don't like access although it would do the trick I would like something web based for easy access.    I look at online database builders like ZOHO and they are very good and do what I am looking for.    however I don't like the monthly pricing models and the data stored online.   I would prefer storing the data on our SQL server.    I have looked at microsoft lightswitch ... its okay but really confusing to work with compared to the builders i found online.

Suggestions?
LVL 2
Zoldy2000Asked:
Who is Participating?
 
Joe JenkinsConnect With a Mentor Commented:
I think you have found the best tool in Microsoft  Lightswitch that's as close to ZOHO creator as possible.  Microsoft's learning center usually has some pretty good videos on how to use their edge products like this.  The Lightswitch application allows for database creation as well as building screens to interact with that data.  This is very similar to how MS Access was built -- giving you access to data and interface creation in one program.

That's the only real insight I can offer on this.
0
 
Jerry MillerConnect With a Mentor Commented:
If you already have a SQL server, it would be easy enough to create a separate database to house the data. You could also create a separate schema if you don't want (or need) a complete database.

Once you have the database in place, a web based solution can be created. Don't hard code your queries in the web code. Use stored procedures to access your data. They are easily updated and you could use many of the stored procedures across sites as they would only differ by site ID.
0
 
Zoldy2000Author Commented:
Thanks..   I am looking for a tool to build the interface.   Similar to Zoho online database tool.
0
Cloud Class® Course: Microsoft Exchange Server

The MCTS: Microsoft Exchange Server 2010 certification validates your skills in supporting the maintenance and administration of the Exchange servers in an enterprise environment. Learn everything you need to know with this course.

 
Jerry MillerConnect With a Mentor Commented:
If you are only talking about 15 or so fields that is currently on a spreadsheet, you are probably looking at just a few tables. One for the main data and maybe a couple of others for various types that should be broken down for normalization.

For example, you should have a site identifier in the data table so that you will know to which site the data belongs for queries and reports. There would also be a site table with the ID, site name, description, phone, address, and any other pertinent site information that you need.

There are tons of people on EE that would help you design a small structure like it appears that you are needing. There are examples of Create Table and Create Stored Procedure scripts on MS TechNet that could get you started as well.

http://technet.microsoft.com/en-us/library/cc879262.aspx
0
 
Joe JenkinsCommented:
We could probably whip up some scripts and table creations in a matter of minutes but I think he wants some more control over it as well as concurrently develop a solution that users can interact with which is what ZOHO is currently providing him.

Hopefully the Author gets back to us soon with some more information.

Have a great week everyone!
0
 
Zoldy2000Author Commented:
That is correct.   I know how to create the tables and work with them in lightswitch.    however adding search functions and user logon permission etc... are the missing pieces.
0
 
Joe JenkinsConnect With a Mentor Commented:
I don 't know specifics on Lightswitch, but I did find a decent video tutorial on custom filters for Search Screens:
Search Screens

If I come across a video on MSDN about creating and maintaining user logins I will surely point you to it.
0
 
Zoldy2000Author Commented:
Okay this is kind of changing to a lightswitch help case.   but the other problems I have is creating relationships.    My data is stored in SQl ... I have 2 tables.     I just want t a simple link between the 2 so that a field (site name) is link to the other table ...so any records for that site are linked together.

the issue is it wont allow a link on a field that is not a key field.    Each table has its own auto incrementing ID but of course that will not work.    I assume that perhaps I need another separate table with just sites and a unique ID associate with each one and then from there create my linked table?
0
 
Joe JenkinsConnect With a Mentor Commented:
You're on the right track there, yes.  

Create a table with perhaps something like this:

site_id <key>
site_name
site_city
site_state
etc etc

This way you can put ALL information pertaining to sites in this one table and it can be referenced anywhere else in the database simply by joining on the site_id in this table.

In your other tables with data you wan to relate, you can have a column that is that site_id.
0
 
Zoldy2000Author Commented:
okay so let say I have done this.    And now i have another table where I want to reference the site information... this is where I am not understand.    How does the site ID ... which is incremented and automatic key get populated in the table.    users need to be able to create this link by choose a customer and those ID's would be meaningless to them
0
 
Joe JenkinsCommented:
I can help you with the SQL side of things but not specific to lightswitch.   You might need to open a new question on Lightswitch to attract some experts who know that software in depth.

With that said, let me take a crack at at least the logic side of things.  I'm going to make some assumptions about the power of Lightswitch in handling things like this.  

I'm going to use your customer information example.  If you wanted to make a form that created a new customer, you would maybe have a dropdown that includes the names of location codes or whatever from the site list table and have the user select that.  And, based on the selection of the user, your would build the logic in the form that "Company A" is the same as site_id 1.  So, when the form saves the customer record, in the site_id field for that customer it would substitute the user input "Company A" for "1".  

Does that make any sense?  If it doesn't, I apologize.  You sound like you have a good head on your shoulders and with some pointed direction or some videos on the subject to provide some training you could knock this out.
0
 
Zoldy2000Author Commented:
Yes that does make sense ... I just have to figure out how to apply all this in lightswitch... your right a may need to open a separate case.
0
 
Joe JenkinsCommented:
Hopefully this has helped you at least answer your initial question.  I think you have the right tool.  It should be able to do everything you need to do based on my reading on the subject.
0
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

All Courses

From novice to tech pro — start learning today.