Alpha Video Training
Results 1 to 17 of 17

Thread: Choice of database for small apps

  1. #1
    Member
    Real Name
    Pua Eng Seng
    Join Date
    Mar 2012
    Posts
    21

    Default Choice of database for small apps

    Hi,

    I am MS Access hobby programmer, has developed few desktop Apps to support business in tracking their records/transactions using VBA. After spending time in evaluating A5 for couple of weeks, I am sort of decided to adapt to A5 for front end for web & perhaps mobile client. Just would like to seek opinion whether I shall upsize my database from Access to mySQL or MS SQL? And I am not that familiar to both of these servers (I only develop desktop last time).

    To lower down the learning curve & apps delivery, I am considering to remain the MS Access database and just hook it up to A5 for front end. However, would such approach cause performance, security & other potential issue I may not seen? Since those Apps I am developing is not a big one, I prefer to have a database in single file to ease the maintenance & backup.

    Which database may be the best option? Your opinion is much appreciated.

    Regards,
    Pua

  2. #2
    Moderator Steve Workings's Avatar
    Real Name
    Steve Workings
    Join Date
    Apr 2000
    Location
    The Dreaded Chair
    Posts
    5,596

    Default Re: Choice of database for small apps

    An Access back end on a web site won't handle much. There are a lot of fans here of both MySQL and MS SQL. I favor MS SQL though. And MS SQL Express is free. Make sure you download a package that includes MS SQL Server Development Studio. You'll spend a few hours learning and figuring things out, but it's really not difficult. Still creating databases, tables with fields, indexes, etc.
    -Steve


  3. #3
    Member
    Real Name
    Pua Eng Seng
    Join Date
    Mar 2012
    Posts
    21

    Default Re: Choice of database for small apps

    Steve. thanks for your feedback, appreciate that

  4. #4
    Member
    Real Name
    Mark Rossi
    Join Date
    Jan 2014
    Location
    Australia
    Posts
    24

    Default Re: Choice of database for small apps

    Hi Pua,

    I would suggest avoiding MS Access it has many issues, which I could spend the rest of the day listing. When it comes to simple SQL, known as CRUD (Create Read Update and Delete), most database engines are very similar the difference comes if you start using functions and stored procedures.

    SQL server is good, but can need a bit more memory then other options from what I can remember of it. they have a free express version that just limits the maximum memory and CPU it can use. MS SQL Server is also the closest to Access as they both use Jet SQL.

    MySQL is popular option, I personal don't think much of it but that may just be because I didn't like my MySQL uni lecturer. Be aware it is only free if you don't charge your users a fee to use your app or your app is open source (anyone can access the source code).

    PostgreSQL is probably the best free option but is very technical and may have a bit too steep learning curve for you.

    As for single file the only two I know of is SQLite and MS server CE (compact edition) both are very good but I don't know how well Alpha will work with them. But most database engines have backup routine that creates a single file that you can backup.

    I would suggest MS SQL Express its good similar to Access and if your application ever grows you can switch to the full version but adding a licence.

    Hope this helps and I didn't babble too much. I’ve used most databases at least once in my life so if you have any questions I can try my memory and see if I can answer them for you.

  5. #5
    Member
    Real Name
    Pua Eng Seng
    Join Date
    Mar 2012
    Posts
    21

    Default Re: Choice of database for small apps

    Mark,

    Thank you very much for your insightful info, it really helps. I share the same though to proceed with MS SQL Express for the time being as well to ease of transition and upgrade in the later stage.

    Really appreciate your info, thanks !!

  6. #6
    Member Bill Broyles's Avatar
    Real Name
    William Broyles
    Join Date
    Jul 2007
    Location
    Clarkston,WA USA
    Posts
    67

    Default Re: Choice of database for small apps

    As an OLD Clipperhead, DBF runs in my blood, but i understand times have changed. I played with ACCESS and found it was not as bad as I was led to believe. Lots of good books, and lots of help. I just downloaded and installed MS SQL Express to use with AA. Gads, how confusing is this beast. Looks like I took a big chunk out of my hard drive with NO real help. For those of you bitching about Alpha Five's documentation and help, Alpha help looks like the library of congress compared to Microsoft!

    Where do i start with MS SQL Express, I mean how do you create a database, tables etc??? DBF's never looked so good!

    Licensed NERD....
    Regards from Washington State,
    Bill
    Licensed NERD

  7. #7
    Member
    Real Name
    Doron
    Join Date
    Dec 2011
    Location
    NJ, USA
    Posts
    173

    Post Alpha Five Developer

    Hello There,

    I will definitely use MS SQL 2012 the express version. For the long run I can find more DBAs working with MS SQL compare to MySQL. I always try to avoid free products if I can. Eventfully free cost me more money at the end of the day. I always look for a product that I can find is a company behind it.

    In addition, since Microsoft sql database has other paid versions with even more functionalists you have a virtually unlimited growth path.

    The Developer edition cost only $50.00 and it is similar to MS SQL the enterprise edition. For Ms sql there are also books on line right here: http://technet.microsoft.com/en-us/l.../ms130214.aspx

    If you need any help for MS SQL let me know.

    Regards,

    Doron
    The Farber Consulting Group, Inc.

    Main Web Site: http://www.dFarber.com
    MS SQL Blog: http://www.dfarber.com/computer-consulting-blog.aspx
    Convert Ms Access to Web
    Custom Software Development
    Alpha Five Development
    No Interest Loans Application
    Last edited by DoronF; 03-31-2014 at 06:29 PM.

  8. #8
    "Certified" Alphaholic DaveM's Avatar
    Real Name
    Dave Mason
    Join Date
    Jul 2000
    Location
    Hudson, FL
    Posts
    5,994

    Default Re: Choice of database for small apps

    They all work very well and are to do with taste. Do not forget maria db that is a drop in for MySQL.

    I would definitely stay away from access databases. There are potential problems that can bite you. We had to move away from access several years ago with a major vb app because our customers were to it's limits. We moved to mssql and went on, but it was not a one day job to switch. You are definitely doing the right thing to ask the questions ahead of time.

    I use smarterasp.net to host some stuff mainly for testing client applications as needed before deployment and the asp/c#.net stuff there does lend to mssql and alpha is based a lot on ms products and has dependencies on ms products. Would think it the best overall match.

    Were we talking desktop, I have a fondness for the dbf since I used it with Clipper87 and later vb as well as tons of alpha stuff.
    Dave Mason
    dave@aldaweb.com

    Skype is dave.mason46

  9. #9
    Moderator Steve Wood's Avatar
    Real Name
    Steve Wood
    Join Date
    Nov 2003
    Location
    Bay Area, California
    Posts
    8,827

    Default Re: Choice of database for small apps

    Small Web app = MySQL or MariaDB as mentioned below. Those who know MS love it, but it is significantly more difficult to comprehend than to MySQL. I use MySQL + Navicat unless MSSQL is required. For our level of use Navicat make managing the database much easier than either of the native tools for MySQL or MSSQL.
    Steve Wood
    Join the ALPHA DEVELOPERS NETWORK
    There is no Cloud. It's just someone else's computer.
    Web - Mobile - Hosting - Products - Frameworks - Developer Resources
    AlphaToGo | IADN (100% Alpha Anywhere Websites)

  10. #10
    Member
    Real Name
    Mark Rossi
    Join Date
    Jan 2014
    Location
    Australia
    Posts
    24

    Default Re: Choice of database for small apps

    Quote Originally Posted by Bill Broyles View Post
    ...Where do i start with MS SQL Express, I mean how do you create a database, tables etc??? DBF's never looked so good!...
    SQL Server Management Studio Express, is a tool you can use to manage your SQL Express Db. Available here http://www.microsoft.com/en-us/downl...s.aspx?id=8961. MSDN is a good source for help. http://msdn.microsoft.com/

  11. #11
    Moderator Peter.Greulich's Avatar
    Real Name
    Peter Greulich
    Join Date
    Apr 2000
    Location
    Boston, MA
    Posts
    11,629

    Default Re: Choice of database for small apps

    Quote Originally Posted by Steve Wood View Post
    Small Web app = MySQL or MariaDB as mentioned below.
    I would only add to what Steve said by reminding everyone that all of Googles db's are based on MySQL, now converted to MariaDB. So it's not just for small apps, but for the giant-ist apps in the world.

    http://www.google.com/url?q=http%3A%...omVrOWFZ6L2Wsw

  12. #12
    Member Bill Broyles's Avatar
    Real Name
    William Broyles
    Join Date
    Jul 2007
    Location
    Clarkston,WA USA
    Posts
    67

    Default Re: Choice of database for small apps

    [QUOTE=DaveM;669410]They all work very well and are to do with taste. Do not forget maria db that is a drop in for MySQL.

    Dave, do you use mariaDB ? Can you use MySQL for the source for a sql connection string?

    TIA
    Bill
    Regards from Washington State,
    Bill
    Licensed NERD

  13. #13
    Member Bill Broyles's Avatar
    Real Name
    William Broyles
    Join Date
    Jul 2007
    Location
    Clarkston,WA USA
    Posts
    67

    Default Re: Choice of database for small apps

    Thanks Steve. i uninstalled MsSQL Express, (I think) and installed mariaDB, created a db, table, added 6 records. Then I downloaded Navicat for mariaDB. Navicat sees the db, table and works as advertised. BUT when i get into AA and try to make a connection string, I pick MySQL, and it doesn't work. Suppose I should have used MySQL as my database instead of mariaDB???

    Kind regards,
    Regards from Washington State,
    Bill
    Licensed NERD

  14. #14
    Member Bill Broyles's Avatar
    Real Name
    William Broyles
    Join Date
    Jul 2007
    Location
    Clarkston,WA USA
    Posts
    67

    Default Re: Choice of database for small apps

    I got it to work with mariaSQL. Just took me a lot of monkeying around until i got the connection string right. YAH!!!!!

    Love AA! mariaDB is going to rock!

    Thanks for all the help...
    Regards from Washington State,
    Bill
    Licensed NERD

  15. #15
    Member
    Real Name
    Michael Cornford
    Join Date
    Oct 2011
    Location
    East Sussex, UK
    Posts
    884

    Default Re: Choice of database for small apps

    You have received so much good advice that I can add little.

    There is a program called Flowheater that moves data from one format to another. https://www.youtube.com/watch?v=Xl7w_w9UVbM
    If you use Access you can export to excel and use Alpha to import to any database you want. This is slower than flowheater but works.

    We have a big system Import 500K records per day using MySQL (Truncate .. delete 500K old records per day) Access hates/falls over this method.
    However we need to use Access elsewhere and it works well with Alpha on smaller record sets.

    If this is really a hobby then try it with Access and move to SQL. The only thing you loose is time.

    Alpha lets you connect to multiple databases https://www.youtube.com/watch?v=KHz1V-w3yrc

    Michael

  16. #16
    Member Bill Broyles's Avatar
    Real Name
    William Broyles
    Join Date
    Jul 2007
    Location
    Clarkston,WA USA
    Posts
    67

    Default Re: Choice of database for small apps

    Thank you Michael, you have been most helpful. I am a hobby programmer, that is I program as a hobby in several different languages. I sold to over 950 doctors and hospitals my Clipper app that was ported to Harbour using the Minigui program. I just took it off the market because sales went flat and Obamacare has forced many of my clients to merge with hospitals etc.

    I had a couple of POS apps that i did in Visual FoxPro and one in Alpha5. I want to expand the POS apps to use mobile models. I liked using AA with the Access db because the Access db is just a file that I can put anywhere. For the heck of it I made the sample mobile app where you took a photo with your mobile device and stored it in an Access db. I had a room full of different Cell phones, a Chrome book, and my Google TV. The iPhones, android phones, and iPads all were taking pictures, and all of the devices were showing all the photos when refreshed. The data was on my laptop. it amazed me to see IOS, Windows, and Linux all working together. Fun stuff.

    I have a used book store customer that inherited an Access POS app when she bought the book store 8 years ago. The Access app has run flawlessly day in and day out. I tried to build an Alpha app to "modernize" her app on new computers, but it was so slow, she continued with the Access app. I want to try AA with mariaDB to see if i can't outdo her Access app. And add iPads into her mix.
    Regards from Washington State,
    Bill
    Licensed NERD

  17. #17
    "Certified" Alphaholic DaveM's Avatar
    Real Name
    Dave Mason
    Join Date
    Jul 2000
    Location
    Hudson, FL
    Posts
    5,994

    Default Re: Choice of database for small apps

    Maria sql has been tested against MySQL and is supposedly a great deal faster in some areas. I have not tested, so cannot corroborate this.
    Maria was built by the people who were with MySQL before the oracle days, so they should know what to do. They tried to keep it almost the same for current usage and will later add and change to become separated from MySQL with more possibilities and speed. That is stuff I read.
    Dave Mason
    dave@aldaweb.com

    Skype is dave.mason46

Similar Threads

  1. Targeting Web Apps on Small Devices
    By FrankR in forum Archived Wishlist
    Replies: 2
    Last Post: 05-22-2010, 12:06 PM
  2. Replies: 0
    Last Post: 09-16-2009, 03:09 AM
  3. Small web apps for sale for learning tool ????
    By crimmelcp in forum Web Application Server v6
    Replies: 7
    Last Post: 11-26-2005, 07:57 PM
  4. Small web apps for sale for learning???
    By crimmelcp in forum Alpha Five Version 6
    Replies: 2
    Last Post: 09-13-2004, 06:42 AM
  5. database for my small estate agency
    By Don Bremner in forum Alpha Five Version 5
    Replies: 2
    Last Post: 07-31-2004, 04:50 AM

Bookmarks

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •