Is this Stupid? What do you think?

[EganSolo]

I've got a project keeping its data in 24 CSV files. These tables each have a primary key, an integer, and a name, which is required and must be unique within each table (but could be duplicated across tables).

 I created a SQLite database that amounted to 2.2M, but then I questioned why I needed a database. Keeping in mind that:

• I've never been fond of the database-aware components since I don't like hard-wiring the UI to the DB.
• It's a single-user project.
• It'll see slow growth. I would be surprised if any of the tables reach two thousand rows.

Given all that, I'm considering the following approach:

Create a Tcsv_database class that

• Keeps the data in 24 tables consisting of a TStringGrid + some additional metadata
• Keeps an index for all the names based on TStringList. Each entry would have an associated object that would hold
  The value and a secondary list that tracks the spot of that value and its ID in every table it appears in.

Since the tables are normalized, I'd have to handle the views in code, but it's not as onerous as it may seem since the views are relatively straightforward to build.

I like the simplicity of this setup. It's easy to implement and easy to read.

Are there glaring gotchas I need to think of? 

[Thaddy]

I don't understand 1.

[cdbc]

Hi
Hmmm, fragile setup?!?
Regards Benny

[d4eva]

• I've never been fond of the database-aware components since I don't like hard-wiring the UI to the DB.
• It's a single-user project.
• It'll see slow growth. I would be surprised if any of the tables reach two thousand rows.

Famous last words Don't try to re-invent the SQLite.
And you don't have to "hard-wire the UI to the DB". Have the database and UI separated.

[MarkMLl]

I've never been fond of the database-aware components since I don't like hard-wiring the UI to the DB.

That's rubbish. Even if you put the DB-aware controls on the form at design time you can set up all the credentials etc. at runtime.

And you can set it up for multiple database types if you want, subject to SQL etc. compatibility: I've done this for PostgreSQL + Firebird as an example.

MarkMLl

[CharlyTango]

In such a case, I would always favour the use of a DBMS over flat file data.
If it is a single user, SQLite is a good choice.
You would then have to programme all the query options that you have with SQL, which is not worth the effort.
You also don't have to use database-sensitive controls and can do everything manually.

In your case, you only have the illusion that you are not hardwiring your data, because you are only choosing between a flat file variant and a DBMS. Both are hardwired as long as you do not programme a dynamic intermediate layer that is able to handle different file formats (for flatfile) or table structures (for SQL)

[JD]

I've got a project keeping its data in 24 CSV files. These tables each have a primary key, an integer, and a name, which is required and must be unique within each table (but could be duplicated across tables).

 I created a SQLite database that amounted to 2.2M, but then I questioned why I needed a database. Keeping in mind that:

• I've never been fond of the database-aware components since I don't like hard-wiring the UI to the DB.
• It's a single-user project.
• It'll see slow growth. I would be surprised if any of the tables reach two thousand rows.

As someone who used to write VBA code to maintain data in Excel spreadsheets, my humble opinion is using a database like SQLite makes your work easier to manage, easier to retrieve information and above all enforce data integrity and data security.

I have however written code to export the data to Excel/CSV files (using the fpspreadsheet library) to share with others if necessary.

Even for small volumes of data, I prefer to use a proper database.

Just my two cents. 

Cheers,

JD

[EganSolo]

While I appreciate the passionate and committed ( ) responses, I would like to offer a different perspective: When considering a problem, I tend to list the key architectural qualities that I consider important for that effort and let these qualities drive my choice.

In this case, the folks who replied are squarely against that idea, and their positions center on four qualities:
1. Ease-of-use             : SQLite (as an example) + DB-Components are easier to use. They are proven and battle-tested
2. Ease-of-Management: Relying on SQLite makes it easier to manage the application.
3. Speed of development: By leveraging these components, coding will be faster.
4. Speed of refactoring   : Rewriting SQL statements is much easier than coding.

These qualities are worthy of consideration. Nevertheless, there's another side to this coin:

1. Uniform debugging: Free Pascal doesn't treat SQL statements as first-class. It can't compile or debug them. By foregoing the DB, I'm able to leverage the IDE to its fullest
2. Transparency: My data is in CSV files. I can open them in a text file if I so wish, or leverage the full power of Excel
3. Reduction of bugs I don't understand: When using (or misusing) a component, I run into bugs I don't understand because I'm not the maintainer of the code or because I'm missing the author's original intent. By keeping the back-end of the app lightweight, I reduce these issues. You don't think that's important? Check the SQLite threads in the forum 

In this case, these last three trump the deck for me. Once I'm done writing this app, I'll return here and let you know how it went (the good, the bad, and the ugly).

Cheers, and thank you for your sincere and passionate statements. I always look forward to hearing what you have to say.

[TRon]

Reduction of bugs I don't understand: When using (or misusing) a component, I run into bugs I don't understand because I'm not the maintainer of the code or because I'm missing the author's original intent. By keeping
the back-end of the app lightweight, I reduce these issues. You don't think that's important? Check the SQLite threads in the forum 

Do note that most of these threads stem from users that ran into issues which did not bother to a) do proper research before starting (comprehension) and b) do not understand the concept of SQL databases in general.

As an additional note SQL statements /can/ be very daunting if you try to encapsulate complicated queries.

Sometime people do not understand basic concepts and differences as f.e. dropping components on a form vs creating them at runtime or setting properties at design-time vs at runtime (you fell for that as well).

Just like any tool/solution also SQL is not the (only) holy grail. It depends on the project.

[dbannon]

...
1. Uniform debugging: Free Pascal doesn't treat SQL statements as first-class. It can't compile or debug them. By foregoing the DB, I'm able to leverage the IDE to its fullest
2. Transparency: My data is in CSV files. I can open them in a text file if I so wish, or leverage the full power of Excel
3. Reduction of bugs I don't understand: When using (or misusing) a component, I run into bugs I don't understand because I'm not the maintainer of the code or because I'm missing the author's original intent. By keeping the back-end of the app lightweight, I reduce these issues. You don't think that's important? Check the SQLite threads in the forum 

Nicely reasoned arguments Egan, I thank your for your polite and focused contribution. I think I'd dispute item 1. - in an ideal world, you don't need to debug any deeper than the SQL level. But that assumes your SQL is perfect !

I was once a database manager, before the term existed. SQL was new then, relatively standard and I was, perhaps, reasonably good at it. If that person, me, 35 years ago was starting your project now, SQL would be the way to go. But me, now, I have forgotten it all, its changed anyway, I would certainly go with plain files. Or, maybe JSON ?  Not xml.

David

[egsuh]

MS Access? It does not need to write SQL directly. Just create links. It's not easy either.

[Handoko]

Using SQL connection + dropping data aware components on a form may sound great, as it follows the slogan "rapid application development". But in reality, it may introduce headache and make the program harder to maintain because the source is no longer purely code. We cannot maintaining it by using text searching feature. We need to select the related components and click the its properties to see the settings.

Data aware database components are good for prototyping. When working on real database programs, I write my own module for doing connections, reading, editing/writing, deleting and data validation. I do not use DBNavigator, DBEdit, DBCombobox, nor any data aware components. User forms only fetch and send data to the module I wrote. By doing so, it centralizes all the database related issues on a single location. Maintaining and debugging become much easier.

[paweld]

You didn't write what the purpose of this program is, and I think this also affects the answer.

Although already based on what you have written like everyone above I recommend switching to SQLite.
On the plus side:
- you have one file rather than 24 (or even later more) - easier to manage, archive
- for reporting you don't have to modify the program every time you want to add a new report or modify an existing one, all you need is a simple query editor
- easier to pull data if there are relationships between tables, of course you need to know a little SQL.
On the downside:
- if your “editor” is MS Excel, then here you have to create the interface yourself to manage (add / edit / delete) data.

Also, I don't know what the deal is with these DB-Aware components - there is no obligation to use them if you use databases, you can still rely on standard controls

[Nicole]

Ten years ago I was in exactly the same situation.
I had tables and tables of ASCII-format.

It was a hard decision and a long way. I changed to database.
Today I use Firebird, Flamerobin and IBX.

I am that glad I did it. 
No, it was not easy, but I am glad.
Here are the advantages I am happy about:


The data are much more compact.
Not sure how much, but may be a factor 10 or even 100.

The backup of all my code and the database is done within 8 seconds a day.
I save the data back to the year 2000.

You say, your data are accurate, - however there happens that quickly an error.
A database refuses a wrong entry.
cvs allows it.
I work alone as well. You carry errors with you for years. Nobody cares, if you do not find it.

The possibilities of a database are beyond compare, because you can use SQL.
SQL is an extremely sophisticated tool, quick, easy, code can be written by machines and you can ask nearly with it.
You get information about your data you have not dreamed of.

cvs is damaged that quickly.
And you are not aware of. One wrong click into the data, - destroyed.

The possibilities how database-data can be drawn and written are prefab.
You write yourself preshaped pipelines into your database and build it like lego.
E.g. I can say, "give me all information you can find about one stock and write them to a nicely formated string".
The function behind searches for old quotes, daily quotes, news, ticker, dividend, grabs some server for updates and uses SQL for this.

What I write is:
"RichEdit.Lines.Add (GetSTOCKInfo of....);"
Work done.

May be you want to write some interfaces, which read your csv-data and write them into your database.
So you have a smooth change.

You mentioned the good portability.
All my environment is in a Win 7 VM. So I  transport my Lazarus and my source code with the database.

[EganSolo]

Well, it's done.

Recap: I asked if it was a stupid idea to forgo using a database as a back-end and rely on csv files instead.
I asked that question on April 26. Nearly six months and 35,000 lines of code later, the minimum viable product (MVP) version of my program is up and running.

I don't want to bore you with lots of details, so I'll be brief.

Use-case: I needed a replacement for Protégé 3.5, an anthology management software that I used to manage the ancillary data of a High-Fantasy epic I've been writing and publishing under a pen name. In case you're curious, it's called The Epic of Ahiram. It's three times the size of The Lord of the Rings (I'm not bragging -- there are much larger stories) and has hundreds of characters, objects, locations, events, etc, that I needed to track to preserve the story's consistency. Protégé 3.5 did a great job, but it was discontinued, and I needed a replacement. The newer version went in a direction that didn't serve my needs.

The salient characteristics of the program I needed to write were:
-A small dataset (currently at two megs). Mostly textual.
-High relational density. The program needs to track relations between characters, objects, locations, sayings and a dozen other concepts.
-Flexible design: the beauty of Protégé 3.5 is that it allows you to modify your classes, fields and forms on the fly. MVP implemented a form editor that allows me to change the editor of a class on the fly, but the classes and fields are hardwired and map to the classes and fields I used in Protégé 3.5.

Selected MVP features

• Powerful search capability
• Powerful query capability
• Flexible data import/export.
• Timed save of the data.
• Auto-backup on app startup

Implementation Details

The back-end consists of an array of TStringGrids, one per table. Currently, there are 32 tables, so 32 TStringGrids. The DB unit is a little over 10k in code length, but the portion of the code for CRUDs operations is less than 1,000 lines—the rest deals with searching, and communication with the mid and front end.

Overall, the loading, storing, creating and deleting data operations are less than 10% of the overall code. Would using a DB engine have led to a faster time-to-market? Perhaps, but it would have been marginal. As an aside, I once wrote a full-blown sequencer for a real-time event bus, and I wrote it in SQL, using ORACLE's own Java framework. I know SQL and I love it, but I love even more the KISS principle that allowed me to debug my code end-to-end and keep clarity on the way operations were implemented.

MVP--by definition--is meant as a quick-and-dirty first implementation to get you up and running. My code is mostly clear, but some confusing bits and pieces are hardwired to the data. That will change in the next iteration. Would I switch to a DB engine? At this point, I don't see the advantage. Could I switch to a DB engine? Absolutely, since the core DB code is well contained.

Conclusion

Was not using a DB engine the right solution? It's _a_ right solution. It's not the only one, but given my constraints and the use case I'm solving for, it ended up being a good fit. Could that change in the future? Absolutely, but for now, I'm relatively satisfied with the result.