August 11th, 2013, 02:22 PM
Which database is right for this project?
I will be creating a program that will listen to directories and add files that are created in directories into the database. The program will allow a user to search through the database for files by names, dates and other data that will be retrieved from the files themselves and stored in the table. The database will be accessed most of the time only by a single user (however, there could be instances where a few users would need to access at the same time). I will be writing the program in C#.
What would be the right database? I'm eyeballing MySQL and SQLite, but I'm not sure which one would work better for this purpose. I have very basic experience with querying MySQL database from some tutorials (I use a free database hosting site for that purpose). There will be thousands of files and I'm not sure what the best way to address the storage of files. Right now they simply reside in a directory on our server. I'm assuming the easiest way would be to have links in the database to those files, rather than storing the files into the database.
Future ideas for the project is to be put on secured web site so that clients can access the database through it. Would SQLite support that? Or is MySQL a better investment of time? I'm intrigued by the fact that SQLite doesn't need to be installed on client side, unlike MySQL.. so.....
What are your thoughts?
August 11th, 2013, 03:36 PM
where did you get the idea that mysql has to be installed on the client side?
Originally Posted by bkboggy
August 12th, 2013, 03:47 PM
I was under impression that SQLite could be embedded into an application and the only thing that would need to be provided would be a .dll. Unlike MySQL, which would have to be present on the machine to run it. I'm not talking about remote access, I'm talking local databases.
Originally Posted by r937
Ultimately, I want my application to be capable of both, though.
August 12th, 2013, 04:55 PM
ah, okay, i'm sorry, i do not know why i did not understand this scenario the first time
like phil mickelson, i am such an idiot
August 12th, 2013, 10:49 PM
lol, it's not your fault. I didn't explain myself well enough. I'm very new to actual development of applications. Up to this point I've been doing mostly what college courses teach you with some GUI in C++ and Java on my own time. But it has never been something that can actually be used in real-world application (well, mostly... one Java application that I've been working on is pretty neat... but will have to be re-written since I am learning databases now). So, it's a bit hard to explain myself without having the correct knowledge on the subject.
Originally Posted by r937
August 18th, 2013, 04:03 AM
There are quite a few systems that provide this functionality on Linux, and most projects have found it a good idea to either base the system on Postgres or MySQL. The reason for this is the sheer volume of data involved in indexing things that users may want to search/aggregate their data by, and the lack of "interesting index" functionality in SQLite. This lack of interesting indexing becomes a larger handicap the more data is stored, but is particularly problematic when it comes to full text search (especially over multiple or undefined languages) and date/range comparison. Those areas of handicap in SQLite tend to overlap almost exactly with the areas you need your data system to be strong in.
Postgres can do a lot more interesting things than MySQL can (and its license permits completely embedding your own fork in your own product), but is a bit more work to understand. In either case you need more than just a library, you either need an existing install on the client system or your own purpose built system to be running internal to the install of your system.
If you're doing this on Windows, then I suggest doing the "build a private binary" approach using something like Postgres and have it configured to only talk to your application. In other words, treat the source code for Postgres as part of the source code for your project, and compile and bundle it with the final product and project installer.
Depending on what you want the system to be capable of doing (relative suggestion, or just find something that is there or isn't?) performance overhead may be a big deal for you, in which case it is very advantageous to program the database to give complete answers instead of just acting as a "persistence" layer. In particular when you are doing specialty processing for which there are no accepted solutions yet, it is very often a good idea to try your idea out first in application code where development feels easiest for you, then move that inside the DB in a procedural language behind a table-returning interface, and make sure the concept still works, then translate it into a custom db procedure in C to make it go as quickly as possible.
Good luck, whatever method you choose.