flat file vs. MySQL

Anon

I'm currently building a web page for a charity that would like to keep track of their membership information. They anticipate a list of 500-1000 names, addresses and short comments.<p>
Now, if they only have ten members, you'd want to just use a flat file. If they have a hundred thousand members, you'd definitely want to use a database like MySQL or Postgres. <p>
Where's the breakpoint? How much information (number of fields, size of database in bytes, whatever) makes using a database worth the trouble? <p>
I'm leaning toward PHP and a database, but I don't have any real proof that's the best way to go. My coworker says to just do it in perl with a flat file. <p>
Does anyone have any solid information, (benchmarks, specs, whatever) to resolve this for me?

Anon

Jeff,

Unless I am mistaken, one of the real things to ask is 'data use'. How many people, especially at one time will be accessing this data.

Perl is a bad word 🙂

I remember years back using perl with flat files on some projects. The data the files held was very minimal really but the amount of access not only crash the application but the server as well.

Doing things in a php/mysql combo is so darn easy, why not just use it off the hop.

Personally, I always use a db engine like mysql for data retrival because of the past experiences with perl and flat files.

I would be curious just who uses flat files with php and why and what are the break points.

Anon

"Doing things in a php/mysql combo is so darn easy, why not just use it off the hop?"

That was my reaction also. However, the person across the table from me prefers the perl route, since it's "simpler" and more ISPs handle perl than handle PHP and MySQL.

He's also a better geek than me, so before I disagree, I'm hoping to get some numbers on my side.

I could be wrong. Perl and a flat file might be appropriate here. If all I want to do is keep track of a dozen phone numbers, I need Notepad, not Oracle. A scooter will beat a 747 in a quarter-mile sprint.

Where's the tradeoff between these two systems? Two concurrent users and 50 fields of data? Ten concurrent users and 500K of data?

Why do I think I just found a topic for the next PHPBuilder article?

[deleted]

Pro-flatfile:
- No database required.

Con-flatfile:
- All the functionality has to be written by you.
- No proper locking. A flatfile can only be locked as a whole or not at all.
- No indexes, every query MUST do a sequential scan.
(selecting all records that have 'Bert' in them requires that you read ALL records and manually filter out the ones with 'Bert' in them)
- Zero scalability (because of the lack of indexes)

And finally the real pincher:
- You end up writing your own 'SQL' interface to a flatfile, and MySQL has already done that for you.

Pro MySQL:
- Indexes, records can be located without sequencial scans.
- Caching, results of queries remain in cache so the next time you do the same query you get the results immediately.
- SQL language support: you can talk to the database in a powerfull standardized language.
- Scalability, because MYSQL uses the SQL language, it's portable to other database servers.

And an important but often overlooked argument:
- The flatfile scooter may outrun the MySQL 747 on the quarter mile, but if a scooter is all you have, you'll never try to outrun the 747 on anything more than the quarter mile.
Why not?
Because after a quarter mile fullspeed on a scooter, your bum hurts like hell.
In other words, flatfile-based applications are usually very simple in their functionality because making the functionality more complex requires oodles of work to create functions that can process the flatfile data.
I'd like to see that pro-perl guy to a flatfile version of this:
SELECT table2.age,
max(table2.column1),
avg(table1.column3),
substr(max(table1.lastname),1,4)
FROM table1, table2
WHERE table1.id = table2.id
AND table2.age>100
AND table2.age<200
GROUP BY table2.age
HAVING count(table2.bool) > 5
ORDER BY table2.age ASC
LIMIT 10,5

I'm sure he can do it, but I wouldn't hold my breath.

Oh, allmost forgot:
Con-MySQL
- MySQL needs to be installed. Which can take up to 5 minutes, 10 minutes if you have no experience :-)

Conclusion:
MySQL is so completely light-weight and function-rich, that you'd have to be a total pr*ck to prefer doing everything by hand.

Anon

Well if you have a database(here it is mysql) you may use it if it does not put a burden onto you.

Databases should make you feel more secure about your data, though sometimes mysql creates bad files that you need to fix with isamchk.

Not using databases requires you have advanced programming expertise and think of many extra cases (locking, read write at once etc.)

One improper use of databases and Mysql is with top list scripts. We are having problems with such scipts since they have heavy load on servers we provide as a hosting provider. Efficient and logical use of flat files is the best solution here.

Everyone can be a programmer but setting every work on sql and other means is very stupid. Programmers should concentrate on
the algorithm, efficiecy and reliablity.

Making a script that meets your needs should not make you a programmer.

regards.