Re: more about using sets
Three reasons to keep a table of card types: 1. Generally as an added check, you ask the user to specifiy the card type, then ask for the number. If that card type check is data-driven, you will need less code modification over time. 2. Once MySQL comes to support stored procs, there is a case to be made for storing the early-digit rules as data in such a table. 3. You will likely want a gif for each card type. PB - >> From: David T-G [mailto:[EMAIL PROTECTED]] >> ...I still have to figure >> out how to make sure that our credit card types and skill levels don't >> get corrupted (MC, MasterCard, mastercard, ...), but I guess that gets >> enforced in the software interface, right? >Isn't the card type a piece of derived data? You should never have to enter it directly; it's >computable from (the first digit(s) of) the card number. - Before posting, please check: http://www.mysql.com/manual.php (the manual) http://lists.mysql.com/ (the list archive) To request this thread, e-mail <[EMAIL PROTECTED]> To unsubscribe, e-mail <[EMAIL PROTECTED]> Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php
Re: more about using sets
Paul DuBois wrote:
I just tried this script:
[ ... ]
I don't see anything getting flipped.
Wow, I'm impressed :-). I've not actually tried this myself (as it has
been a known bug in at least other db software). Good to know that
MySQL has no such problem.
Now testing:
create table testenum (
val enum('false','true')
);
insert into testenum values ('true'),('true'),('false');
alter table testenum change val val enum('unknown','true','false');
select val from testenum;
> true
> true
> false
*is impressed* ... data consistency is good (remarks about it being a
problem with MySQL withdrawn).
--
Michael T. Babcock
C.T.O., FibreSpeed Ltd.
http://www.fibrespeed.net/~mbabcock
-
Before posting, please check:
http://www.mysql.com/manual.php (the manual)
http://lists.mysql.com/ (the list archive)
To request this thread, e-mail <[EMAIL PROTECTED]>
To unsubscribe, e-mail <[EMAIL PROTECTED]>
Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php
Re: more about using sets
Anderson, Alan R wrote:
Isn't the card type a piece of derived data? You should never have to enter it directly; it's computable from (the first digit(s) of) the card number.
Also note, the type of card isn't a SET option at all (it would be an
ENUM) and if its used as a foreign-key relation to a table of card
types, the card type verification (how to check the digits, etc.) could
be stored in the table:
CCTypes
---
ID ... primary key
Name { entries: "mastercard", "visa", "amex", "diners club", "..." }
VerifyMethod { ... }
--
Michael T. Babcock
C.T.O., FibreSpeed Ltd. ... sql
http://www.fibrespeed.net/~mbabcock
-
Before posting, please check:
http://www.mysql.com/manual.php (the manual)
http://lists.mysql.com/ (the list archive)
To request this thread, e-mail <[EMAIL PROTECTED]>
To unsubscribe, e-mail <[EMAIL PROTECTED]>
Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php
RE: more about using sets
> From: David T-G [mailto:[EMAIL PROTECTED]] > ...I still have to figure > out how to make sure that our credit card types and skill levels don't > get corrupted (MC, MasterCard, mastercard, ...), but I guess that gets > enforced in the software interface, right? Isn't the card type a piece of derived data? You should never have to enter it directly; it's computable from (the first digit(s) of) the card number. - Before posting, please check: http://www.mysql.com/manual.php (the manual) http://lists.mysql.com/ (the list archive) To request this thread, e-mail <[EMAIL PROTECTED]> To unsubscribe, e-mail <[EMAIL PROTECTED]> Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php
Re: more about using sets
Luc Foisy wrote:
We use enum extensively for 'Y' 'N' values, sort of true false.
That way the values are forced to be one or the other. So any end user has to put one of those values in (if we allow it in that way, though we usually force them to use a checkbox). And its a little more viewable/understandable than 0 or 1, for the end user.
True enough, and I use enums for two-valued options myself quite often
enum('false','true') for example, makes a good replacement for bool.
However, you must remember to always do them in the same order and not
alter it to enum('true','false') by accident and end up flipping all the
data in your table.
I also was under the impression that JOINs did take a little extra time/resource rather than a direct value from the same table.
They do, but I've found it negligable so far. The joined table with
only 3 or 4 values in it should sit in memory forever, and the index
would be as big as the table if you bothered :).
We have a number of tables that have multiple "true/false" values (one with about 50), is not joining that table to 50 different fields not a little expensive?
Again, YMMV and I do this too. I'm just saying that for most things
that people enum (colours of vehicles, brands of product, shipping
company, ...) it just shouldn't be done. Its unfortunately familiar to
C programmers who go "I know how to use that" (I'm a C programmer
myself) and JOINs don't come naturally to.
Maybe we are just doing it all wrong? If we never expect the values to be different (EVER) then is it still wrong?
According the the SQL specs, its not wrong at all, of course. Its just
IMHO (and a few others', I'd bet) that you should avoid it unless its
obviously ok. Remember to document somewhere that you always use
('false','true') or vice versa so that if data needs to be rebuilt,
everyone knows which value 0 stands for and which one 1 stands for.
From C experience, I always put false first.
--
Michael T. Babcock
C.T.O., FibreSpeed Ltd.
http://www.fibrespeed.net/~mbabcock
-
Before posting, please check:
http://www.mysql.com/manual.php (the manual)
http://lists.mysql.com/ (the list archive)
To request this thread, e-mail <[EMAIL PROTECTED]>
To unsubscribe, e-mail <[EMAIL PROTECTED]>
Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php
RE: RE: more about using sets
> >What exactly is wrong about using ENUM's?
> Changing them will kill you unless you're _very_ careful.
> Using them is usually unnecessary as you could've used an ID value
> pointing to another table of values. That table can then be added to
> with no risk to your existing queries. As a contrived example:
> Employee
> --
> ID ... primary key
> Gender enum('male','female')
> vs.
> GenderID tinyint
>
> Gender
> --
> ID tiny int ... primary key
> Name varchar(10)
> INSERT into Gender(Name) values ('male'),('female');
> Later, you might need:
> INSERT into Gender(Name) values ('unknown');
Is not enum forced to any of the values used when created?
We use enum extensively for 'Y' 'N' values, sort of true false.
That way the values are forced to be one or the other. So any end user has to put one
of those
values in (if we allow it in that way, though we usually force them to use a checkbox).
And its a little more viewable/understandable than 0 or 1, for the end user.
I also was under the impression that JOINs did take a little extra time/resource
rather than a direct value from the same table. We have a number of tables that
have multiple "true/false" values (one with about 50), is not joining that table
to 50 different fields not a little expensive?
Maybe we are just doing it all wrong? If we never expect the
values to be different (EVER) then is it still wrong?
Bah, sql, mysql
-
Before posting, please check:
http://www.mysql.com/manual.php (the manual)
http://lists.mysql.com/ (the list archive)
To request this thread, e-mail <[EMAIL PROTECTED]>
To unsubscribe, e-mail <[EMAIL PROTECTED]>
Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php
Re: more about using sets
Luc Foisy wrote:
What exactly is wrong about using ENUM's?
Changing them will kill you unless you're _very_ careful.
Using them is usually unnecessary as you could've used an ID value
pointing to another table of values. That table can then be added to
with no risk to your existing queries. As a contrived example:
Employee
--
ID ... primary key
Gender enum('male','female')
vs.
GenderID tinyint
Gender
--
ID tiny int ... primary key
Name varchar(10)
INSERT into Gender(Name) values ('male'),('female');
Later, you might need:
INSERT into Gender(Name) values ('unknown');
--
Michael T. Babcock
C.T.O., FibreSpeed Ltd. SQL
http://www.fibrespeed.net/~mbabcock
-
Before posting, please check:
http://www.mysql.com/manual.php (the manual)
http://lists.mysql.com/ (the list archive)
To request this thread, e-mail <[EMAIL PROTECTED]>
To unsubscribe, e-mail <[EMAIL PROTECTED]>
Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php
RE: RE: more about using sets
What exactly is wrong about using ENUM's? Luc sql,mysql - Before posting, please check: http://www.mysql.com/manual.php (the manual) http://lists.mysql.com/ (the list archive) To request this thread, e-mail <[EMAIL PROTECTED]> To unsubscribe, e-mail <[EMAIL PROTECTED]> Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php
Re: more about using sets
-BEGIN PGP SIGNED MESSAGE- Hash: SHA1 Hi again -- Well, the general consensus seems to be to stay violently away from SETs and ENUMs :-) I'll certainly give that a shot. I still have to figure out how to make sure that our credit card types and skill levels don't get corrupted (MC, MasterCard, mastercard, ...), but I guess that gets enforced in the software interface, right? Thanks again & HAND & Happy Holidays mysql query, :-D - -- David T-G * There is too much animal courage in (play) [EMAIL PROTECTED] * society and not sufficient moral courage. (work) [EMAIL PROTECTED] -- Mary Baker Eddy, "Science and Health" http://www.justpickone.org/davidtg/Shpx gur Pbzzhavpngvbaf Qrprapl Npg! -BEGIN PGP SIGNATURE- Version: GnuPG v1.0.7 (FreeBSD) iD8DBQE9/o7yGb7uCXufRwARAiydAKCbAOb/tOdioAWx8vG2k2aX1saIygCg0Kx/ xJVcxeAOLDQTB/YHEqd0P6E= =1iKe -END PGP SIGNATURE- - Before posting, please check: http://www.mysql.com/manual.php (the manual) http://lists.mysql.com/ (the list archive) To request this thread, e-mail <[EMAIL PROTECTED]> To unsubscribe, e-mail <[EMAIL PROTECTED]> Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php
Re: more about using sets
Adam, > I agree entirely. SETS and ENUMS should be avoided by any normal user > (frankly, I think they should be deprecated). They are not portable and > it's just horrific to be changing data with an ALTER statement. To add some more to this discussion, a customer of ours is having problems with something as simple as male/female coding in databases. This attribute is coded as "m/f", "1/0", "true/false", etc. What's making it a real big problem is, that it's coded in table _structures_ instead of in relational lookup tables. The smart guy who put ENUM (and SET, if that's ANSI-SQL) into the SQL standard should be punished with a life sentence, cleaning up our customer's mess ;-) Regards, -- Stefan Hinz <[EMAIL PROTECTED]> CEO / Geschäftsleitung iConnect GmbH <http://iConnect.de> Heesestr. 6, 12169 Berlin (Germany) Telefon: +49 30 7970948-0 Fax: +49 30 7970948-3 - Original Message - From: "Adam Nelson" <[EMAIL PROTECTED]> To: <[EMAIL PROTECTED]>; <[EMAIL PROTECTED]> Sent: Monday, December 16, 2002 10:47 PM Subject: RE: more about using sets > I agree entirely. SETS and ENUMS should be avoided by any normal user > (frankly, I think they should be deprecated). They are not portable and > it's just horrific to be changing data with an ALTER statement. > > Foreign Key relationships (even if they aren't real as in standard > MySQL) are the way to go. Get InnoDB and use foreign keys before it's > too late and you're stuck with a hodge podge system. > > > -Original Message- > > From: Harald Fuchs [mailto:[EMAIL PROTECTED]] > > Sent: Monday, December 16, 2002 11:39 AM > > To: [EMAIL PROTECTED] > > Subject: Re: more about using sets > > > > > > In article <[EMAIL PROTECTED]>, > > David T-G <[EMAIL PROTECTED]> writes: > > > > > Hi, all -- > > > I'm still trying to get a good handle on how sets can be > > useful to me. I > > > have three scenarios so far. > > > > > 1) A set of states (US Mail type, not turing type :-) > > > I can pick from the list of states when entering address data, and > > > storing the set entry should take less space than storing > > even a 2-char > > > string. > > > > > 2) A set of ccard types (MC, Visa, AmEx) > > > It's easy to have a pick list to avoid misspellings and such > > > > > 3) A set of pay scale levels (master, journeyman, > > apprentice, intern) > > > Each staff member needs to be at a certain scale so that > > the software > > > knows how much to pay him or her per session. We don't > > want to make up > > > pay levels that aren't in our list. > > > > > For each of these, is a set the way to go, and is it saving > > me anything? > > > > > In the third case, I want to restrict the level in the > > personnel table to > > > one of the defined levels. Do I just use a set in the > > field definition > > > and then list from there (and then it gets messy if we add > > a new level) > > > or do I create a "jobscalelevels" table and define the > > levels in there > > > and then set the personnel.level field to match and forget > > about the idea > > > of a set? > > > > I avoid SETs whenever possible - i.e. always unless > > storage/performance is extremely important. > > They give you nothing which you can't do with a separate value table > > and foreign keys, but they compromize portability. > > > > > - > Before posting, please check: >http://www.mysql.com/manual.php (the manual) >http://lists.mysql.com/ (the list archive) > > To request this thread, e-mail <[EMAIL PROTECTED]> > To unsubscribe, e-mail <[EMAIL PROTECTED]> > Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php > - Before posting, please check: http://www.mysql.com/manual.php (the manual) http://lists.mysql.com/ (the list archive) To request this thread, e-mail <[EMAIL PROTECTED]> To unsubscribe, e-mail <[EMAIL PROTECTED]> Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php
RE: more about using sets
I agree entirely. SETS and ENUMS should be avoided by any normal user (frankly, I think they should be deprecated). They are not portable and it's just horrific to be changing data with an ALTER statement. Foreign Key relationships (even if they aren't real as in standard MySQL) are the way to go. Get InnoDB and use foreign keys before it's too late and you're stuck with a hodge podge system. > -Original Message- > From: Harald Fuchs [mailto:[EMAIL PROTECTED]] > Sent: Monday, December 16, 2002 11:39 AM > To: [EMAIL PROTECTED] > Subject: Re: more about using sets > > > In article <[EMAIL PROTECTED]>, > David T-G <[EMAIL PROTECTED]> writes: > > > Hi, all -- > > I'm still trying to get a good handle on how sets can be > useful to me. I > > have three scenarios so far. > > > 1) A set of states (US Mail type, not turing type :-) > > I can pick from the list of states when entering address data, and > > storing the set entry should take less space than storing > even a 2-char > > string. > > > 2) A set of ccard types (MC, Visa, AmEx) > > It's easy to have a pick list to avoid misspellings and such > > > 3) A set of pay scale levels (master, journeyman, > apprentice, intern) > > Each staff member needs to be at a certain scale so that > the software > > knows how much to pay him or her per session. We don't > want to make up > > pay levels that aren't in our list. > > > For each of these, is a set the way to go, and is it saving > me anything? > > > In the third case, I want to restrict the level in the > personnel table to > > one of the defined levels. Do I just use a set in the > field definition > > and then list from there (and then it gets messy if we add > a new level) > > or do I create a "jobscalelevels" table and define the > levels in there > > and then set the personnel.level field to match and forget > about the idea > > of a set? > > I avoid SETs whenever possible - i.e. always unless > storage/performance is extremely important. > They give you nothing which you can't do with a separate value table > and foreign keys, but they compromize portability. > - Before posting, please check: http://www.mysql.com/manual.php (the manual) http://lists.mysql.com/ (the list archive) To request this thread, e-mail <[EMAIL PROTECTED]> To unsubscribe, e-mail <[EMAIL PROTECTED]> Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php
Re: more about using sets
David T-G wrote:
I'm still trying to get a good handle on how sets can be useful to me.
You're thinking of ENUMs. SETs are for when you want multiple things
selected out of a list. So perhaps, favorite colours:
You have a column Colours
SET('black','purple','blue','green','yellow','orange','red','white');
Then you can do a query like:
UPDATE FavoriteColours SET Colours = ('yellow','blue','green') WHERE ID = 4;
(The 'SET' keyword in the UPDATE command is purely coincidental)
Anyhow, ENUMs are the same as having a second table with a foreign key
relation to the current table and SETs are the same as having a table
that you double-join against to match up multiple values.
PS 'same' here means it has pretty much the same functionality as ...
--
Michael T. Babcock
C.T.O., FibreSpeed Ltd.
http://www.fibrespeed.net/~mbabcock
-
Before posting, please check:
http://www.mysql.com/manual.php (the manual)
http://lists.mysql.com/ (the list archive)
To request this thread, e-mail <[EMAIL PROTECTED]>
To unsubscribe, e-mail <[EMAIL PROTECTED]>
Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php
more about using sets
Hi, all -- I'm still trying to get a good handle on how sets can be useful to me. I have three scenarios so far. 1) A set of states (US Mail type, not turing type :-) I can pick from the list of states when entering address data, and storing the set entry should take less space than storing even a 2-char string. 2) A set of ccard types (MC, Visa, AmEx) It's easy to have a pick list to avoid misspellings and such 3) A set of pay scale levels (master, journeyman, apprentice, intern) Each staff member needs to be at a certain scale so that the software knows how much to pay him or her per session. We don't want to make up pay levels that aren't in our list. For each of these, is a set the way to go, and is it saving me anything? In the third case, I want to restrict the level in the personnel table to one of the defined levels. Do I just use a set in the field definition and then list from there (and then it gets messy if we add a new level) or do I create a "jobscalelevels" table and define the levels in there and then set the personnel.level field to match and forget about the idea of a set? TIA & HAND & Happy Holidays mysql query, :-D -- David T-G * There is too much animal courage in (play) [EMAIL PROTECTED] * society and not sufficient moral courage. (work) [EMAIL PROTECTED] -- Mary Baker Eddy, "Science and Health" http://www.justpickone.org/davidtg/Shpx gur Pbzzhavpngvbaf Qrprapl Npg! - Before posting, please check: http://www.mysql.com/manual.php (the manual) http://lists.mysql.com/ (the list archive) To request this thread, e-mail <[EMAIL PROTECTED]> To unsubscribe, e-mail <[EMAIL PROTECTED]> Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php
