Flat File Database Example

Question

I would like to see some examples of simple flat file databases and how they are accessed through a data layer. I've written to and read from a flat file before, but I have not ever created a data layer that accessed the data for an application using text files.

If possible, it would be nice to see a tutorial that had a data layer that utilized a simple, custom flat file database. An example that saves custom business objects in XML and then uploads them would be nice because XML is so popular and easy to work with.

I would also be grateful for any links to websites that discuss best practices regarding the design of flat file databases, etc.

My goal is to have a solution for storing simple data on a user's machine and have them not need any special software installed (like SQL Server, etc.) in order to fetch the data from where it is stored.

I know this is a very general question, but any advice that can point me in the right direction is welcome.

Answer 1

Text formats like CSV, INI, XML can be used to store structured data, but IMO aren't flexible or efficient to be used as databases.

I recommend SQLite as an excellent alternative. It's a very powerful, lightweight and standalone database engine.

Answer 2

You can have your cake and eat it too:

SQLite is a SQL database that consists of a single file and requires no installation, it has bindings for many languages and runs in various platforms.

There is no need in the case you mention to write your own data layer on top of flat files. In fact, unless you want a learning excercise, I'd advise against doing it.

Answer 3

There are several embedded databases available that your user won't have to worry about at all.

SQLLite is common, popular, cross platform, etc. Depends on your implementation language. If you use Java, there are several, Derby for example. .NET isn't my ballywick, but I imagine there's something there. At a minimum, MS has a freely usable desktop, embeddable SQL engine you can use.

Writing your own can be an interesting exercise, but that wheel has been done, and it's simply easier and more efficient to use an existing database than start from scratch. Your users won't be impacted at all by this, so if that's a primary driver, no reason to not use an available product/project.

Answer 4

There is the DBD::CSV module in Perl that can load csv files and query them with SQL statements. But for your goal I think you'd be better off investigating SQLite which is a proper relational database that runs without a server.

Answer 5

Rather than reinvent databases, why not bundle your application with a simple database engine? Databases comes in many sizes, not all are huge :-)

If you do want to reinvent the wheel, looking at the source code of simple open source database engines should point you in the right direction.

Answer 6

I agree with the many comments suggesting that it is better to use an existing database engine. However, as an attempt to actually answer the question:

• An extremely common flat file database format is Xbase (files typically with .dbf extensions). You can google for "xbase" and "dbf" and find a lot of information and any number of drivers. You should be able to extract quite a few hints and tips if you are really interested.
• If you want to play with one that is very likely on your system, you can use the ODBC "Microsoft Text Driver". If you have any of the Microsoft data access stuff on your machine, you likely have that driver. I don't know for sure but it is probably installed with MDAC. That driver will read and write comma separated text files (among other formats).

Answer 7

You may have got your definitions a bit mixed up, understandable due to the large number of similar technologies around today.

XML isn't a flat file format (or flat file database), but from reading your goal it sounds like what you really want is a self contained relational database rather than an actual flat file.

Like others, I can strongly recommend SQLite for this purpose. There are bindings for various platforms, .NET has System.Data.SQLite which, in one file, is both the database provider and engine.

The 2 big benefits of using SQLite are that the actual database is completely self contained in a single file controlled by your application, and that it supports standard SQL DDL and DML commands (ie SELECT, INSERT, UPDATE, DELETE, CREATE DATABASE/TABLE etc).

For a single user application SQLite is an excellent (one of the best) ways of storing both application data and settings. Of late there has been discsussion that it can even support smaller scale multi-user applications.

However Oracle, MySQL, SQL Server etc are still definitely preferred for multi-user applications (even small-scale applications) if you have the ability to access/utilise a database server.

Also, don't forget that choice of database is not mutually exclusive.

You may have a multi-user application with a rich client UI installed on many users computers. The central database here should really be a multi-user db such as MySQL. But within the rich client UI, SQLIte is ideal to store each users settings, or perhaps to provide off-line support when the database can't be reached.