----- Original Message -----
Sent: Tuesday, June 15, 2004 11:16 AM
Subject: Test greeting & question
> Hi, once again!
> \I tend to get on and off this list.
> I have myCC and I was wondering what this means in my CREATE file.
> ) TYPE=MyISAM;
> Could somebody please translate this into English?
The 'TYPE=' parameter identifies the TYPE or ENGINE used to store your data.
(Until recently, the term used was 'TYPE' but now they prefer 'ENGINE' and
will probably drop support for the 'TYPE' syntax in MySQL 5.) Check out
http://dev.mysql.com/doc/mysql/en/CREATE_TABLE.html. About halfway down the
page, you'll see an HTML table that lists all of the Engine types that are
currently available in MySQL.
Each type/engine has different features/capabilities. There are links
describing each type in that HTML table. Basically, you have to select the
Engine that you prefer for your tables. That is a non-trivial decision so
you'll want to read the description of each engine type before selecting the
engine you want for your table(s).
> I am interested in the UNIQUE property.
> Would making my replies to survey subject to UNIQUE make thej less easy to
> enter in multiples for purposes of ballot-stuffing?
I think you'd be better using a PRIMARY KEY than UNIQUE to prevent
duplicates in a table. They are closely related concepts but not quite the
A primary key's main function is to ensure uniqueness of rows in a
relational table. A primary key can contain one or many columns. A table can
only have a *single* primary key (or none at all). Most inserts, updates, or
deletes are done on the basis of the primary key and ensure that only a
single row is affected by the insert, update, or delete.
A unique key also indicates uniqueness of a value within a column. A unique
key can also contain one or many columns. A table can have *many* unique
For example, consider an Employee table. Most Employee tables that you see
will have an internally-generated employee number that will serve as the
primary key. By internally generated, I mean that a person or program in the
organization generates it. Primary keys that are short,
internally-generated, stable and reside in a single column are preferred
over primary keys that are long, multi-column, externally-generated and
subject to change. That's why the employee's name isn't a very good choice
for a primary key: it could change (in the case of a woman marrying and
taking her husband's name or in the case of someone changing their name
legally); it is multicolumn (a primary key based on the last name alone
would fall apart pretty quickly: as soon as you tried to add a second
'Smith' or 'Jones', you'd realize that a last name made a lousy primary
key); and it is long, which makes it easier to mistype. That is not to say
that primary keys are always generated internally; that is far from true,
although it is quite common.
That same employee table may have one or more unique keys. For instance, a
Social Security Number for each employee might be stored on the table and
would also represent a unique quantity. However, it would be less desireable
than an employee number as a primary key because there is some risk that it
could change. For instance, the government might issue a new SSN if the
employee loses her SSN card. However, the SSN is still a unique value (or
should be!!) so it should be defined as unique.
By the same token, the employee may have a private parking space in the
company lot so their parking space number could also be a unique column in
the table, assuming that parking spaces weren't shared by multiple
employees, as might be the case for shift workers.
Primary keys are normally determined via a process called "normalization"
which is way beyond the scope of this note to explain. It is a data
modelling technique that uses "normal forms" to help decide which columns
belong in each table and what the primary key of each table is, among other
Normalization is not terribly hard to learn but is involved enough that it
just doesn't lend itself to a note like this. I would suggest you do a
Google search on "normalization" to find a tutorial that will guide you
through the steps involved. I've never looked for one - I already know how
to normalize ;-) - but there must be some tutorials out there that will show
you how to do it. Very few professionals go beyond Third or Fourth Normal
Form so don't go further than that in the tutorial unless you really find it
For your situation, you will want to consider carefully what the primary key
of your survey table is, then define the column(s) that make up the key as a
primary key. This will likely be problematic assuming your survey is
anonymous. You're not likely to ask for - or receive - the full name or SSN
or phone number of the person completing the survey so you will have to
improvise something that precludes the same person from completing
additional surveys. I'm really not sure what you could use short of
biometrics (fingerprints, retina scans) that would really be reliable.
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql