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: 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.
According to the manual if you set a SET column to an unsupported value,
the value will be ignored. In other words, MySQL doesn't even think it is
an error to try to store something that isn't in the defined values for the
set ('M','F') in this case, so that is why you don't get an error message.
Therefore, it is clear that the SET column type is NOT working in the way I
first thought: it does not give you an error if you try to store a value
that is not in its defined list of values, although it won't store invalid
values in the table. As a result, I still feel vindicated in suggesting that
the business types be stored in their own table, which could be joined back
to the original Businesses table any time that someone needs to know which
business types
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 your database, you add one row to the Businesses table, assigning a registration number to the business and recording company name, location, etc. Then, for each of the possible business types that apply to that business, you add a row to the Business_Types table. For example, Smitty's is both a pool hall and a dry cleaner so you add one row for each business type, linking it back to the Businesses table via the registration number that uniquely identifies one business. Any given business can have as many business types as you like, not just a maximum of 64. The third table is not strictly necessary but it is
