Solved

Web based database on premise

Posted on 2014-03-11
13
418 Views
Last Modified: 2014-03-12
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?
0
Comment
Question by:Zoldy2000
  • 6
  • 5
  • 2
13 Comments
 
LVL 18

Assisted Solution

by:Jerry Miller
Jerry Miller earned 150 total points
ID: 39920431
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
 
LVL 2

Author Comment

by:Zoldy2000
ID: 39920439
Thanks..   I am looking for a tool to build the interface.   Similar to Zoho online database tool.
0
 
LVL 5

Accepted Solution

by:
Joe Jenkins earned 350 total points
ID: 39920498
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
 
LVL 18

Assisted Solution

by:Jerry Miller
Jerry Miller earned 150 total points
ID: 39920985
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
 
LVL 5

Expert Comment

by:Joe Jenkins
ID: 39921051
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
 
LVL 2

Author Comment

by:Zoldy2000
ID: 39921072
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
Free Trending Threat Insights Every Day

Enhance your security with threat intelligence from the web. Get trending threat insights on hackers, exploits, and suspicious IP addresses delivered to your inbox with our free Cyber Daily.

 
LVL 5

Assisted Solution

by:Joe Jenkins
Joe Jenkins earned 350 total points
ID: 39921180
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
 
LVL 2

Author Comment

by:Zoldy2000
ID: 39921684
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
 
LVL 5

Assisted Solution

by:Joe Jenkins
Joe Jenkins earned 350 total points
ID: 39921693
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
 
LVL 2

Author Comment

by:Zoldy2000
ID: 39921733
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
 
LVL 5

Expert Comment

by:Joe Jenkins
ID: 39921759
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
 
LVL 2

Author Comment

by:Zoldy2000
ID: 39921768
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
 
LVL 5

Expert Comment

by:Joe Jenkins
ID: 39921770
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

Featured Post

What Is Threat Intelligence?

Threat intelligence is often discussed, but rarely understood. Starting with a precise definition, along with clear business goals, is essential.

Join & Write a Comment

Thoughout my experience working on eCommerce web applications I have seen applications succumbing to increased user demand and throughput. With increased loads the response times started to spike, which leads to user frustration and lost sales. I ha…
Slowly Changing Dimension Transformation component in data task flow is very useful for us to manage and control how data changes in SSIS.
Wufoo.com provides powerful tools for surveying targeted groups, and utilizing data from completed surveys to find trends, discover areas of demand or customer expectation, and make business decisions on products or services.
Learn how to set-up PayPal payment integration in your Wufoo form. Allow your users to remit payment through PayPal upon completion of your online form. This is helpful for collecting membership payments, customer payments, donations, and more.

747 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question

Need Help in Real-Time?

Connect with top rated Experts

17 Experts available now in Live!

Get 1:1 Help Now