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, 5 2396
On 6-Jun-2005, el************* @yahoo.com wrote: 1) When creating a table why do I need to define the type of data and length so strictly, i,e int , varchar etc?
it improves calculation, data storage and indexing for mysql to know what
data type is contained in a column.
Can I make a flexible table with data of any type and length?
check out the blob data types. 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?
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 ja*********@wil lglen.net (it's reserved for spammers) el************* @yahoo.com wrote: 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?
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. . . 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:
* 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. 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,
--
PETER FOX Not the same since the poster business went to the wall pe******@eminen t.demon.co.uk.n ot.this.bit.no. html
2 Tees Close, Witham, Essex.
Gravity beer in Essex <http://www.eminent.dem on.co.uk>
"Tom Thackrey" <us***********@ nospam.com> wrote in message
news:0_******** *******@newssvr 21.news.prodigy .com... On 6-Jun-2005, el************* @yahoo.com wrote:
1) When creating a table why do I need to define the type of data and length so strictly, i,e int , varchar etc?
it improves calculation, data storage and indexing for mysql to know what data type is contained in a column.
Can I make a flexible table with data of any type and length?
check out the blob data types.
Ugh - that would be a mess of a table.
Usually, when I need that sort of flexibility, I use a VARCHAR. 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?
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.
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 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?
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).
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?
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 This thread has been closed and replies have been disabled. Please start a new discussion. Similar topics |
by: francescomoi |
last post by:
Hi.
I'm trying to build 'MySQL-python-1.2.0' on my Linux FC2:
----------------------------------
# export PATH=$PATH:/usr/local/mysql/bin/
# export mysqlclient=mysqlclient_r
# python setup.py clean
# python setup.py build
running build
running build_py
|
by: Randell D. |
last post by:
Folks,
I have installed MySQL v4 (client, server and development rpm's). I've
tried and failed to use the recommended mysqladmin to set a root password
after the installation (I have another post open on the MySQL newsgroup
about this). I'm attempting an alternative solution and using webmin
(www.webmin.com) to create/manage access to my...
|
by: mikey |
last post by:
Hi all,
I'm having great problems trying to install the latest MySQl RPM
package onto my Red Hat Linux OS.
There is already MySQL v 3.0 pre-installed with the RH Linux
distribution disk but I can not seem to get any of the functions
working on it. So I have now dopwnloaded the latest version hoping
that this will fix the error.
I have...
|
by: Mike Chirico |
last post by:
Interesting Things to Know about MySQL
Mike Chirico (mchirico@users.sourceforge.net)
Copyright (GPU Free Documentation License) 2004
Last Updated: Mon Jun 7 10:37:28 EDT 2004
The latest version of this document can be found at:
http://prdownloads.sourceforge.net/souptonuts/README_mysql.txt?download
|
by: weissborn |
last post by:
I have an application I have written in MS Access 2002. It is a very
basic inventory system for keeping track of information about our
Unix(solaris to be specific) servers. Other admins may want to use it
shortly and running MS Access over the WAN is no picnic. It was
suggested that I port to php and MySql. Having no experience with...
| |
by: trihanhcie |
last post by:
I m currently working on a Unix server with a fedora 3 as an os
My current version of mysql is 3.23.58. I'd like to upgrade the version
to 5.0.18.
After downloading from MYSQL.COM the package on the site, I made :
rpm -i MySQL-server-5.0.18-0.i386.rpm
then i have errors that relate to many conflicts. I cannot figure out
why -and- cannot...
|
by: manish deshpande |
last post by:
Hi,
When i'm installing MySQL-server-standard-5.0.24a-0.rhel3.i386.rpm by the following command:
rpm -i MySQL-server-standard-5.0.24a-0.rhel3.i386.rpm the following error is being shown:
warning: MySQL-server-standard-5.0.24a-0.rhel3.i386.rpm: V3 DSA signature: NOKEY, key ID 5072e1f5
file /etc/my.cnf from install of...
|
by: menzies |
last post by:
Hi, I"m new to this forum, but I have been trying all day to install DBD::mysql onto my Intel MacBook. I've read lots of forums pages and none have gotten me to a successful 'make test' or a successful 'sudo make install.' Before every attempt I even do a sudo make distclean to make sure I haven't gotten things mucked up from a prior attempt.
...
|
by: Richard Brooks |
last post by:
Does anyone here open a second MySQL database and use that as a straight
IDX type file that points to the record number in the main database, for
speed's sake?
Some time ago I'd set up a standard php login routine and hit a brick
wall as then found out that my host didn't allow external login's (at
that time) with MySQL (true!) so I got...
|
by: Hystou |
last post by:
Most computers default to English, but sometimes we require a different language, especially when relocating. Forgot to request a specific language before your computer shipped? No problem! You can effortlessly switch the default language on Windows 10 without reinstalling. I'll walk you through it.
First, let's disable language...
|
by: Oralloy |
last post by:
Hello folks,
I am unable to find appropriate documentation on the type promotion of bit-fields when using the generalised comparison operator "<=>".
The problem is that using the GNU compilers, it seems that the internal comparison operator "<=>" tries to promote arguments from unsigned to signed.
This is as boiled down as I can make it. ...
| |
by: jinu1996 |
last post by:
In today's digital age, having a compelling online presence is paramount for businesses aiming to thrive in a competitive landscape. At the heart of this digital strategy lies an intricately woven tapestry of website design and digital marketing. It's not merely about having a website; it's about crafting an immersive digital experience that...
|
by: Hystou |
last post by:
Overview:
Windows 11 and 10 have less user interface control over operating system update behaviour than previous versions of Windows. In Windows 11 and 10, there is no way to turn off the Windows Update option using the Control Panel or Settings app; it automatically checks for updates and installs any it finds, whether you like it or not. For...
|
by: tracyyun |
last post by:
Dear forum friends,
With the development of smart home technology, a variety of wireless communication protocols have appeared on the market, such as Zigbee, Z-Wave, Wi-Fi, Bluetooth, etc. Each protocol has its own unique characteristics and advantages, but as a user who is planning to build a smart home system, I am a bit confused by the...
|
by: conductexam |
last post by:
I have .net C# application in which I am extracting data from word file and save it in database particularly. To store word all data as it is I am converting the whole word file firstly in HTML and then checking html paragraph one by one.
At the time of converting from word file to html my equations which are in the word document file was convert...
|
by: adsilva |
last post by:
A Windows Forms form does not have the event Unload, like VB6. What one acts like?
|
by: 6302768590 |
last post by:
Hai team
i want code for transfer the data from one system to another through IP address by using C# our system has to for every 5mins then we have to update the data what the data is updated we have to send another system
| |
by: muto222 |
last post by:
How can i add a mobile payment intergratation into php mysql website.
| |