Re: Fw: column choices for certain data
- Original Message -
From: "Harald Fuchs" <[EMAIL PROTECTED]>
To: <[EMAIL PROTECTED]>
Sent: Friday, October 29, 2004 7:37 AM
Subject: Re: Fw: column choices for certain data
> In article <[EMAIL PROTECTED]>,
> "Rhino" <[EMAIL PROTECTED]> writes:
>
> >> The chief advantage of 'SET', as far as I can tell from the manual, is
> > that
> >> it lets you control the specific values which can be in a column
without
> >> having to write application lookups to verify that the value you are
> >> supplying is one that is valid for the 'SET' column. Therefore, if you
had
> >> only 3 business types, sole proprietorship, partnership, and
corporation,
> >> you could put those 3 values in the set and be sure that those are the
> > only
> >> 3 values that would ever be allowed in the column. That's fine as far
as
> > it
> >> goes and is a very useful thing.
>
> It would be mildly useful if it were true. Unfortunately, it isn't.
> When you try to insert invalid values, MySQL doesn't complain.
> Instead, it silently does a conversion to something equally invalid:
>
> CREATE TABLE t1 (
> id INT UNSIGNED NOT NULL AUTO_INCREMENT,
> val SET ('foo', 'bar', 'baz') NOT NULL,
> PRIMARY KEY (id)
> );
>
> INSERT INTO t1 (val) VALUES ('foo');
> INSERT INTO t1 (val) VALUES ('foo,bar');
> INSERT INTO t1 (val) VALUES ('qux');
> INSERT INTO t1 (val) VALUES ('foo,qux');
>
> SELECT id, val FROM t1;
>
> returns
> ++-+
> | id | val |
> ++-+
> | 1 | foo |
> | 2 | foo,bar |
> | 3 | |
> | 4 | foo |
> ++-+
>
Damn! I only skimmed the article and then did a simple experiment with 'SET'
and got a rather incorrect impression of what it is doing.
I just did this experiment, which was more in-depth, due to your remarks and
learned a few things about 'SET' which are described below:
select 'Drop/Create table';
drop table if exists myset;
create table if not exists myset
(id smallint not null,
name char(10) not null,
sex set('M','F') not null,
primary key(id));
select 'Populate table';
insert into myset values(1, 'Smith', 'M');
insert into myset values(2, 'Jones', 'F');
insert into myset values(3, 'Green', 'M,F');
insert into myset values(4, 'Brown', null);
insert into myset values(5, 'White', 'G');
insert into myset values(6, 'Black', 'M,G');
select 'Display table contents';
select * from myset;
When I wrote this script, I assumed that I was limiting the values in the
'sex' column to 'M' and 'F' (and 'not null' ensured that one or the other of
these values would be stored so that the column wasn't left to default to
null).
When I ran this script, I got this result:
+---+
| Drop/Create table |
+---+
| Drop/Create table |
+---+
1 row in set (0.00 sec)
Query OK, 0 rows affected (0.00 sec)
Query OK, 0 rows affected (0.00 sec)
++
| Populate table |
++
| Populate table |
++
1 row in set (0.00 sec)
Query OK, 1 row affected (0.00 sec)
Query OK, 1 row affected (0.00 sec)
Query OK, 1 row affected (0.00 sec)
ERROR 1048: Column 'sex' cannot be null
Query OK, 1 row affected (0.00 sec)
Query OK, 1 row affected (0.00 sec)
++
| Display table contents |
++
| Display table contents |
++
1 row in set (0.00 sec)
++---+-+
| id | name | sex |
++---+-+
| 1 | Smith | M |
| 2 | Jones | F |
| 3 | Green | M,F |
| 5 | White | |
| 6 | Black | M |
++---+-+
5 rows in set (0.00 sec)
In other words, the first three rows, which stored 'M', 'F', and 'M,F'
respectively in the 'sex' column worked fine. The fourth row, which tried to
store null in the 'sex' column, failed because the column is defined 'not
null'. The fifth row, which tried to store 'G' in the 'sex' column failed
silently and a blank was stored instead of the invalid value 'G'. The sixth
row, which tried to store the a combination of a valid value, 'M' and an
invalid value 'G', failed silently and actually stored 'M' and a blank.
Accord
Re: Fw: column choices for certain data
In article <[EMAIL PROTECTED]>,
"Rhino" <[EMAIL PROTECTED]> writes:
>> The chief advantage of 'SET', as far as I can tell from the manual, is
> that
>> it lets you control the specific values which can be in a column without
>> having to write application lookups to verify that the value you are
>> supplying is one that is valid for the 'SET' column. Therefore, if you had
>> only 3 business types, sole proprietorship, partnership, and corporation,
>> you could put those 3 values in the set and be sure that those are the
> only
>> 3 values that would ever be allowed in the column. That's fine as far as
> it
>> goes and is a very useful thing.
It would be mildly useful if it were true. Unfortunately, it isn't.
When you try to insert invalid values, MySQL doesn't complain.
Instead, it silently does a conversion to something equally invalid:
CREATE TABLE t1 (
id INT UNSIGNED NOT NULL AUTO_INCREMENT,
val SET ('foo', 'bar', 'baz') NOT NULL,
PRIMARY KEY (id)
);
INSERT INTO t1 (val) VALUES ('foo');
INSERT INTO t1 (val) VALUES ('foo,bar');
INSERT INTO t1 (val) VALUES ('qux');
INSERT INTO t1 (val) VALUES ('foo,qux');
SELECT id, val FROM t1;
returns
++-+
| id | val |
++-+
| 1 | foo |
| 2 | foo,bar |
| 3 | |
| 4 | foo |
++-+
--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: column choices for certain data
Hi. Following documentation, we can see: "ENUM and SET columns provide an efficient way to define columns that can contain only a given set of values." http://dev.mysql.com/doc/mysql/en/constraint_enum.html But carefully read http://dev.mysql.com/doc/mysql/en/SET.html before you make a choice. "Andy B" <[EMAIL PROTECTED]> wrote: > Hi... > I have a db that I'm writing. It's for a business directory and one of the > fields/columns in the table needs to have a list of business types in it > (i.e. retail, auto, computer and so on). Since there may be more than one > category that a business fits under I was wondering if "SET" is the best > choice for that?? > > > -- For technical support contracts, goto https://order.mysql.com/?ref=ensita This email is sponsored by Ensita.NET http://www.ensita.net/ __ ___ ___ __ / |/ /_ __/ __/ __ \/ /Gleb Paharenko / /|_/ / // /\ \/ /_/ / /__ [EMAIL PROTECTED] /_/ /_/\_, /___/\___\_\___/ MySQL AB / Ensita.NET <___/ www.mysql.com -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: column choices for certain data
See my remarks interspersed below. Rhino - Original Message - From: "Andy B" <[EMAIL PROTECTED]> To: <[EMAIL PROTECTED]> Sent: Thursday, October 28, 2004 5:09 PM Subject: Re: column choices for certain data > Hi... > > Thanks for the info/help here. No problem; that's what the list is for ;-) > I understand everything up to the part where > it talks about referencing more than 1 table to each other and the foreign > keys and all that stuff... > > basically I got lost. Not because you explained it wrong but because I > haven't got the foggiest clue what keys/table linking is or how it works... > Fair enough. I don't know what you know or don't know so I just made an assumption and guessed your experience was more extensive than it is. > Is it possible we can start with a simpler example with linking/keys/foreign > keys and stuff so maybe I can follow it easier?? And of course if not valid > for the list maybe we can take further talking to private email?? (sorry I'm > very new at this stuff)... > Actually, the concept is not that hard. I'll try to explain it a little more clearly. The whole idea of both the SET datatype and the lookup table I showed you is to limit the values which can be stored in a given column to specific values. If you just defined your business type column as char(50) or something like that, you could put *any* SINGLE value you liked into the column as long as it didn't exceed 50 characters; if you added 'not null' to the column description, you would additionally be forced to put *something* in the column, even if that was a single blank. Other than those limitations though (and possibly some limitations with regards to specific characters, depending on the code page, such as accented letters or whatever), you could supply any SINGLE value you want. In your case, you want to limit the valid values for the business type to a few specific values. Also, you want to be able to store MULTIPLE business types for the same business. Now, I'm not precisely sure what values you are envisioning for this business type but let's say, for argument's sake, that it is a short description of the business, like pool hall or dry cleaner. You may have a slightly different idea of business type in mind; if so, substitute the values that you envision for my examples. Whatever the values you want, you've led me to believe that a given business could have more than one business type so I'm picturing a business that has a pool hall but also takes in dry cleaning as one example. (By the way, if I'm misunderstanding this and you really only want a business type column to contain ONE value for any given row, then you don't really need SET at all. Remember, the idea of SET is that you could store up to 64 values in the same column for a given row, so that you could, in theory, say that Smitty's is a combination pool hall and dry cleaner (plus up to 62 other things if the owner has some additional sidelines, like selling gardening supplies or magazines). Okay then, let's get Codd's objections to this out of the way first. If you built a table using SET and a company had just these two business types, the row would look like this: registration_numberbusiness_namebusiness_locationbusiness_type 1 Smitty's123 Main Street pool hall, dry cleaner If the business had 5 sidelines, the row would look like this: registration_numberbusiness_namebusiness_locationbusiness_type 1 Smitty's123 Main Street pool hall, dry cleaner, gardening supplies, magazines, gun repair [forgive the line wrapping; all 5 values should appear in the same line/row] E.F. Codd, the guy who came up with the theory upon which all relational databases, like DB2 and MySQL, are based, would object strongly to this because his theory said that you should NEVER have more than one value in a column of a given row. The use of SET clearly violates that rule. (That may be why I've never seen it in another relational database, like DB2). Instead, we are going to satisfy Codd's theory by putting the business types in a separate table, the Business_types table. That table is going to have one row for each business type for each business. I've shown you that table earlier; here it is again: registration_numberbusiness_type 1pool hall 1dry cleaner 2restaurant As you can see, it indicates that the business whose registration number is 1 is carrying on two different business types, pool hall and dry cleaner. The business whose registration number if 2 is carrying on only one business type, restaurant. We could stop there and probably satisfy y
Re: column choices for certain data
Hi... Thanks for the info/help here. I understand everything up to the part where it talks about referencing more than 1 table to each other and the foreign keys and all that stuff... basically I got lost. Not because you explained it wrong but because I haven't got the foggiest clue what keys/table linking is or how it works... Is it possible we can start with a simpler example with linking/keys/foreign keys and stuff so maybe I can follow it easier?? And of course if not valid for the list maybe we can take further talking to private email?? (sorry I'm very new at this stuff)... I want to try and take this 1 step at a time until I get it... let me know how I/we should go on from here. (the other side note) is I eventually have to work this db system into a php driven application (not like it matters on this list but...) anyways let me know how to continue with the matter... tnx for the help... - Original Message - From: "Rhino" <[EMAIL PROTECTED]> To: "Andy B" <[EMAIL PROTECTED]> Sent: Thursday, October 28, 2004 4:00 PM Subject: Re: column choices for certain data - Original Message - From: "Andy B" <[EMAIL PROTECTED]> To: <[EMAIL PROTECTED]> Sent: Thursday, October 28, 2004 1:15 PM Subject: column choices for certain data Hi... I have a db that I'm writing. It's for a business directory and one of the fields/columns in the table needs to have a list of business types in it (i.e. retail, auto, computer and so on). Since there may be more than one category that a business fits under I was wondering if "SET" is the best choice for that?? I wouldn't use SET if I were you. I have never used the 'SET' column type in MySQL and had to look it up in the manual to see what it did. However, I've worked with relational databases for 20+ years (mostly DB2) and it doesn't have a 'SET' column type in its repertoire; I've gotten used to doing things without 'SET' so maybe I'm just being stodgy ;-) The chief advantage of 'SET', as far as I can tell from the manual, is that it lets you control the specific values which can be in a column without having to write application lookups to verify that the value you are supplying is one that is valid for the 'SET' column. Therefore, if you had only 3 business types, sole proprietorship, partnership, and corporation, you could put those 3 values in the set and be sure that those are the only 3 values that would ever be allowed in the column. That's fine as far as it goes and is a very useful thing. However, on the negative side, there is a fixed maximum of 64 values in the set. While that may be sufficient for your immediate needs, I don't think you can be certain that it will be sufficient for your long term needs. For example, if this is an eclectic business that combines a lot of lines of business, you may find that it sells groceries, operates a dry cleaner, contains a movie theatre, and umpteen other things all under the same business name. You may find that 64 values isn't enough once you start making the set include all the different functions of the business. The second negative is that I don't think 'SET' is a datatype found in most other databases. Therefore, if you eventually port this table over to another database, you may have to rework the design somewhat to get the same effect, which could be a pain. The third negative is that putting multiple values in a single column of a single row violates Codd's Rules, which are the foundation of all relational databases. Codd is probably rolling in his grave at the mere thought of doing this ;-) Therefore, let me suggest this, which should give you the same benefits without the 64 value limitation while being portable to other databases: store the business type in a separate table, even if there is only one possible value for business type for most rows in your directory. For example, create one table to hold the basic information about your business: create table businesses (registration_number int not null, business_name char(50) not null, business_location char(100) not null, [etc.] primary key(registration_number)); Sample Contents: registration_numberbusiness_namebusiness_location 1 Smitty's123 Main Street 2 Bob's 456 Park Street create table business_types (registration_number, business_type char(20) not null, primary key(registration_number,business_types) foreign key(business_type) references business_types_lookup(business_type))TYPE=InnoDB; Sample Contents: registration_numberbusiness_type 1pool hall 1dry cleaner 2restaurant create table business_types_l
Fw: column choices for certain data
Oops, I meant to copy the list on this reply too. Rhino - Original Message - From: "Rhino" <[EMAIL PROTECTED]> To: "Andy B" <[EMAIL PROTECTED]> Sent: Thursday, October 28, 2004 4:00 PM Subject: Re: column choices for certain data > > - Original Message - > From: "Andy B" <[EMAIL PROTECTED]> > To: <[EMAIL PROTECTED]> > Sent: Thursday, October 28, 2004 1:15 PM > Subject: column choices for certain data > > > > Hi... > > I have a db that I'm writing. It's for a business directory and one of the > > fields/columns in the table needs to have a list of business types in it > > (i.e. retail, auto, computer and so on). Since there may be more than one > > category that a business fits under I was wondering if "SET" is the best > > choice for that?? > > > I wouldn't use SET if I were you. > > I have never used the 'SET' column type in MySQL and had to look it up in > the manual to see what it did. However, I've worked with relational > databases for 20+ years (mostly DB2) and it doesn't have a 'SET' column type > in its repertoire; I've gotten used to doing things without 'SET' so maybe > I'm just being stodgy ;-) > > The chief advantage of 'SET', as far as I can tell from the manual, is that > it lets you control the specific values which can be in a column without > having to write application lookups to verify that the value you are > supplying is one that is valid for the 'SET' column. Therefore, if you had > only 3 business types, sole proprietorship, partnership, and corporation, > you could put those 3 values in the set and be sure that those are the only > 3 values that would ever be allowed in the column. That's fine as far as it > goes and is a very useful thing. > > However, on the negative side, there is a fixed maximum of 64 values in the > set. While that may be sufficient for your immediate needs, I don't think > you can be certain that it will be sufficient for your long term needs. For > example, if this is an eclectic business that combines a lot of lines of > business, you may find that it sells groceries, operates a dry cleaner, > contains a movie theatre, and umpteen other things all under the same > business name. You may find that 64 values isn't enough once you start > making the set include all the different functions of the business. > > The second negative is that I don't think 'SET' is a datatype found in most > other databases. Therefore, if you eventually port this table over to > another database, you may have to rework the design somewhat to get the same > effect, which could be a pain. > > The third negative is that putting multiple values in a single column of a > single row violates Codd's Rules, which are the foundation of all relational > databases. Codd is probably rolling in his grave at the mere thought of > doing this ;-) > > Therefore, let me suggest this, which should give you the same benefits > without the 64 value limitation while being portable to other databases: > store the business type in a separate table, even if there is only one > possible value for business type for most rows in your directory. > > For example, create one table to hold the basic information about your > business: > > create table businesses > (registration_number int not null, > business_name char(50) not null, > business_location char(100) not null, > [etc.] > primary key(registration_number)); > > Sample Contents: > registration_numberbusiness_namebusiness_location > 1 Smitty's123 Main Street > 2 Bob's 456 Park Street > > create table business_types > (registration_number, > business_type char(20) not null, > primary key(registration_number,business_types) > foreign key(business_type) references > business_types_lookup(business_type))TYPE=InnoDB; > > Sample Contents: > registration_numberbusiness_type > 1pool hall > 1dry cleaner > 2restaurant > > create table business_types_lookup > (business_type char(20) not null, > business_type_description char(200) not null, > primary key(business_type)); > > Sample Contents: > business_typebusiness_type_description > pool hallgambling establishment or other den of iniquity > restaurant eating establishment that can serve alcohol > > Do you see how this works? > > Every time you add a new business to y
column choices for certain data
Hi... I have a db that I'm writing. It's for a business directory and one of the fields/columns in the table needs to have a list of business types in it (i.e. retail, auto, computer and so on). Since there may be more than one category that a business fits under I was wondering if "SET" is the best choice for that?? -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
