What Is The Best Software For Small Business Database Creation?
It’s a toss up really, but LibreOffice and OpenOffice immediately come to mind when I’m asked by someone “What’s the best software for small business database creation?” Those two at this point (February 2012) operate in the same way as far as I can tell, and I use them both as front end software for MySQL when I’m creating a new small business database.
I'm writing a php app that allows someone with an Android device and a bluetooth barcode scanner to walk around a do different things with inventory. At the moment, it tells us the retail and on hand quantity of item X are in both store locations, and how much we've sold in the last year. We've currently got two MySQL database I'm contending with. One belongs to our ERP, and the other is one I created for our online estimating tool. I've only got select privileges on the ERP database, which is on purpose; I don't want to be responsible if something goes wrong. The second database though, is mine. I created it as sort of an add-on, and it keeps track of estimates customers do on their own. Lately though, I've decided to use it so that I can keep track of some stuff, and that meant adding tables.
I can add MySQL tables from a command line, but there are some instances where I require visuals, and this is one of them. I like to see the tables and columns as I make them, and OpenOffice/LibreOffice allows me to do this by showing a table as a spreadsheet. Here's how…
I'm assuming you've already got a Java Runtime Environment installed. What you'll need now is a mysql-connector. I must say that MySQL's website is a bit more of a PITA to navigate since Oracle took over, but for now what you need is here: http://www.mysql.com/downloads/connector/j/, at least until someone decides to bury it further…
So grab the file, and then stick it on your computer somewhere. I put in /usr/local/. Now to activate it you get into Tools –> Options in Open or Libre office, and you'll see this:
Your installed version of Java should show up in this window, but there's a bit of a delay sometimes from when you open this to when the list actually populates. Once you're in here, click on Class Path, and you'll be taken to another window where you need to click on the Add Archive button. Another window pops up where you can navigate to the connector you downloaded (and unzipped probably, depending on which file you grabbed) earlier.
After you've done this, clicked all of the Open and OK buttons necessary, you'll have to restart the app (Open or Libre Office) to hook up to a database. You should be good to go on the client end.
There are some adjustments you'll need to make on the server end though. A user must exist that can connect from the client. Meh? It's easy really. Lets say my laptop is a MySQL server. Well, it is, so I guess there's not much to the imagination with this one… Connecting to MySQL through LibreOffice is no different, as far as the server is concerned, than via the command line ( the MySQL prompt, mysql> ). You are on localhost (or 127.0.0.1). It's when the box you're running LibreOffice on is a different computer than the box that MySQL is running on that some changes will be made. If your MySQL username is joebob and the ip address of the computer your on is 192.168.0.2, then someone on the server is going to have to run a command like "grant all on blahdyblah.* to email@example.com identified by 'joebobpassword';"
In this case, the user joebob has the run of the mill on the blahdyblah database. You could limit things to select, update, drop, etc, etc. It's up to you.
Remember that this setup is only going to work if the ip address is going to stay the same from session to session. This probably means on a LAN, or if your client machine is behind a static ip on the internet.
You could technically log in and run that grant statement every time your ISP changes your public ip (if you're set up on a dynamic ip type of account) or go set something up on dyndns.org.
For a no-nonsense list of steps, you can visit http://www.openoffice.org/dba/FAQ/specific.html#connectmysql and follow the steps there. It's the same process, I just used pretty pictures.