flat file vs. mySQL ???

fairhousing,

The flatfile (text) database is the most basic form of data storage you can get (besides pen & paper ). You also have no ability to take advantage of relations, and as your database grows your querys will get slower and slower because it has to go through the whole database. Whereas with a SQL type database you can use relations, setup indexs, and do querys on single tables which greatly increase the query speed.

The funny thing is back in the old days (roughly ten years ago) database programs like DB2 weren't much more than a glorified flat-file, how times have changed.

Regards,
Peter J. Boettcher

is there a big difference in query speed even with a flat file db under 100k?

thx again

------------------
Thumbs up or down ratings of the best and worst ways to make $$$ on the net. CLICK 4 CASH! from Affiliate Programs and Ad Networks

No. not 100k, but if you have something over 1 meg then that will slow down the server just a bit using a flat database.

fairhousing,

If it's possible I would stay away from flatfile. Why start off less then perfect technology. After using relational databases for awhile now I couldn't imagine going back to one big table to hold everything. I guess if you're just experimenting with something then a flatfile is ok, but if you plan to use this data for anything I at least recommend using an Access database, and a SQL 7.0 or mySQL solution would be even better.

Regards,
Peter J. Boettcher

cool, thx for the feedback folks!

------------------
Thumbs up or down ratings of the best and worst ways to make $$$ on the net. CLICK 4 CASH! from Affiliate Programs and Ad Networks

Hi Peter,

I agree with you that relational is a beautiful thing. (I was one of the original database reviewers for PC Magazine back in the days when PC Labs was called the Toy Shop.)
However, I can think of three off-the-cuff reasons why flatfiles may be more apt.
1)Cost
2)size restraints (file & server); and,
3)permission rights.
For example, I have a site on aol (or as someone else put it: Almost OnLine). I do not have the budget for an expensive rdbms. I would run out of my "free" space before even loading the dbms. And, I do not have rights to write to the server.
In other words: to a hungry man, even a crumb looks like cake.

Vinny

------------------
GrassBlade: cut&paste javascript

Vincent,

I can't argue on the cost side since a flatfile is free. As far as size goes, you definately lost that argument. A small database will be smaller in flatfile form, but a larger database will be smaller in a relational database form since you don't have to save redundant data. For example, say you're saving A user's name, favorite color, and favorite car. In a flatfile every row will contain something like:

Joe Smith, Red, Ferrari

In a relational database it would contain:

Joe Smith, 1, 3

Since I would store colors and cars in seperate tables. Like I said a small database will be smaller in flatfile form, but think about 50,000 rows of data, the relational will be smaller.

Permisions are a little tricky but with DSN-less databases you can do alot even when your host doesn't support it.

I guess there are some areas where a flatfile gets the job done, I just have a hard time thinking that way anymore.

Regards,
Peter J. Boettcher

Hi Peter,

Yes, redundancy would be gone (ah if only life were so
However, by size I was thinking more of the Exes, Dlls, etc. Seems as though as the years go on, everything gets larger (and not necessarily better).

Re the following:
>Permisions are a little tricky but with DSN->less databases you can do alot even when >your host doesn't support it.

More info, some examples, please.

BTW: If interested, I have a client-side javascript text-based database script (Rolodex 3) at my site you might find interesting.

Vinny

------------------
GrassBlade: cut&paste javascript

Vincent,

By DSN-less I meant that you woudln't have to install a System DSN on the server, but you would still need some form of server-side scripting, this is an example in ASP using an Access DB:

Set Conn = Server.CreateObject("ADODB.Connection")
Conn.ConnectionTimeout = 15
Conn.CommandTimeout = 30

Conn.Open "DBQ=" & Server.MapPath("yourdb.mdb")& ";Driver={Microsoft Access Driver (*.mdb)};DriverId=25;MaxBufferSize=8192;Threads=20;"

Set SQL = Server.CreateObject("ADODB.Command")
SQL.ActiveConnection = Conn
SQL.CommandText = "SQL Statement here" SQL.CommandType = 1

Set RSOpen = Server.CreateObject("ADODB.Recordset")
RSOpen.Open SQL, , 0, 1

I guess if your host supports ASP they probably at least support Access databases and DSN's so maybe this isn't the best example.

Like I said before, there are some areas where a flatfile gets the job done, but it can always be done better with a relational database.

Regards,
Peter J. Boettcher

fairhousing,

I have been helping a friend set up a shopping cart that uses a flatfile db that is right at 1MB in size (1,024,841 to be exact) and from the time you press the search button until you get a result takes about 20-25 seconds on a 56k connection. About 5500 lines in it. How fast if it were in a mySQL db, who knows but probably would be a little faster.

Dan

Hi Peter,

Thanks for the info; unfortunately as far as AOL knows, ASP is Cleo's pet.

Vinny

------------------
GrassBlade: cut&paste javascript

thx, the biggest file i've used so far has been 60 kb so i should be ok with flat file. the biggest i see my db going is 75 kb.

thx again

------------------
Thumbs up or down ratings of the best and worst ways to make $$$ on the net. CLICK 4 CASH! from Affiliate Programs and Ad Networks