mysql vs. flatfiles

I have been using flat files for a while but thought I should learn
mysql. There are two things I dont like about mysql compared to using
flatfiles. They are:

1) When creating a table why do I need to define the type of data and
length so strictly, i,e int , varchar etc? Can I make a flexible table
with data of any type and length?

2) When creating a record I am using autoincrement ID. This works
great except when I delete a record it leaves a "hole" in the table.
How can I set it up to work like a flatfile where all records shift
"up" when one is deleted?

Any good tutorials that cover this?

Thanks,

On 6-Jun-2005, el_roachmeister@yahoo.com wrote:
[color=blue]
> 1) When creating a table why do I need to define the type of data and
> length so strictly, i,e int , varchar etc?[/color]

it improves calculation, data storage and indexing for mysql to know what
data type is contained in a column.
[color=blue]
> Can I make a flexible table
> with data of any type and length?[/color]

check out the blob data types.
[color=blue]
>
> 2) When creating a record I am using autoincrement ID. This works
> great except when I delete a record it leaves a "hole" in the table.
> How can I set it up to work like a flatfile where all records shift
> "up" when one is deleted?[/color]

There is no 'hole' in the table, only a missing number in the sequence. If
you really need to you can resequence a table but it's better to stop
thinking in terms of flat files and take advantage of the features of tables
and relations. For example you can retrieve the records from a table in
sequence by the auto-increment field, then count them as you retrieve them.
If all you want is the count, you can do that with a "select count()..."

The only way to close up a hole in a flat file is to rewrite all the records
from the hold forward, that's not practical in the case of a large number of
records. With a database, the 'cost' of deleting a row is usually minimal
because the records do not need to be stored sequentially.


--
Tom Thackrey
www.creative-light.com
tom (at) creative (dash) light (dot) com
do NOT send email to jamesbutler@willglen.net (it's reserved for spammers)

el_roachmeister@yahoo.com wrote:
[color=blue]
> 2) When creating a record I am using autoincrement ID. This works
> great except when I delete a record it leaves a "hole" in the table.
> How can I set it up to work like a flatfile where all records shift
> "up" when one is deleted?[/color]

Tom has already explained this rather well, but a short sidenote on
the autoincrement is that there is a reason why it leaves "holes" (in
the sequence). Have a look at:
http://dev.mysql.com/doc/mysql/en/ex...increment.html

If you specify a column as auto_increment, and then reuse the index,
how can you assure that each row has a unique identity? If you don't
need unique identities, just don't use auto_increment ;-)

/Marcin

Following on from 's message. . .[color=blue]
>I have been using flat files for a while but thought I should learn
>mysql. There are two things I dont like about mysql compared to using
>flatfiles. They are:[/color]
* Go with MySQL (almost) every time
* The documentation is clear and extensive
* Installation and integration is a doddle
* myadmin takes the hassle out of learning all the DDL stuff
* You are bound to need database features sooner rather than later so
bite the bullet - the learning curve is not difficult.
* Your questions will soon be answered when you get more familiar
* Flat files have lots of overheads - both computationally and hassle of
use. (Even a log file such as an audit trail, if it is to be analysed,
is probably better as a table.) - Don't even mention multi-user
aspects.





[color=blue]
>
>1) When creating a table why do I need to define the type of data and
>length so strictly, i,e int , varchar etc? Can I make a flexible table
>with data of any type and length?
>
>2) When creating a record I am using autoincrement ID. This works
>great except when I delete a record it leaves a "hole" in the table.
>How can I set it up to work like a flatfile where all records shift
>"up" when one is deleted?
>
>Any good tutorials that cover this?
>
>Thanks,
>[/color]

--
PETER FOX Not the same since the poster business went to the wall
peterfox@eminent.demon.co.uk.not.this.bit.no.html
2 Tees Close, Witham, Essex.
Gravity beer in Essex <http://www.eminent.demon.co.uk>

"Tom Thackrey" <use.signature@nospam.com> wrote in message
news:0_9pe.656$bv7.395@newssvr21.news.prodigy.com. ..[color=blue]
>
> On 6-Jun-2005, el_roachmeister@yahoo.com wrote:
>[color=green]
>> 1) When creating a table why do I need to define the type of data and
>> length so strictly, i,e int , varchar etc?[/color]
>
> it improves calculation, data storage and indexing for mysql to know what
> data type is contained in a column.
>[color=green]
>> Can I make a flexible table
>> with data of any type and length?[/color]
>
> check out the blob data types.[/color]

Ugh - that would be a mess of a table.

Usually, when I need that sort of flexibility, I use a VARCHAR.
[color=blue][color=green]
>>
>> 2) When creating a record I am using autoincrement ID. This works
>> great except when I delete a record it leaves a "hole" in the table.
>> How can I set it up to work like a flatfile where all records shift
>> "up" when one is deleted?[/color]
>
> There is no 'hole' in the table, only a missing number in the sequence. If
> you really need to you can resequence a table but it's better to stop
> thinking in terms of flat files and take advantage of the features of
> tables
> and relations.[/color]

The relational aspect is the main reason I use ID's like that - it's
something that is used internally, and rarely seen by the end user. So it
doesn't really matter if it's not perfectly sequential.

>I have been using flat files for a while but thought I should learn[color=blue]
>mysql. There are two things I dont like about mysql compared to using
>flatfiles. They are:
>
>1) When creating a table why do I need to define the type of data and
>length so strictly, i,e int , varchar etc? Can I make a flexible table
>with data of any type and length?[/color]

Stating the type of data and length can let the database help you
in validating data, for example, the inventory count of widgets
on hand is unlikely to be 'Green' and dates can be stored more
efficiently if the database knows it's a date.

Look at the 'blob' types if you really want arbitrary data (some people
put raw binary image files into blob fields).
[color=blue]
>2) When creating a record I am using autoincrement ID. This works
>great except when I delete a record it leaves a "hole" in the table.
>How can I set it up to work like a flatfile where all records shift
>"up" when one is deleted?[/color]

Are you sure you want it to work that way? Imagine the havoc if your
bank account number changed every time someone who had an account before
you did closed theirs?

I suggest that you use a field you want to sort by (for example, date/time
entered) and assign the numbers on the fly when you retrieve the data.

Gordon L. Burditt