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]
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_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
mysql browser
Hi. I was wondering how you can edit/change tables and data inside a table with mysql query browser. I run mysql 5.0.0 on windows xp sp2. It says in the help that you can edit/change tables and data inside tables if there is enough identifiable information inside the table. What does that mean and how do you change it? I cant seem to edit or change anything on any of the tables I have. -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
SET column type??
hi.. i am sort of confused about the SET column type... is there any place that i can go that will give a better example of it and how to use/create it than the manual does?? -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
create table problems??
hi..
i am trying to create this table:
CREATE TABLE rnjresort_events (
Id int(25) NOT NULL auto_increment,
Type enum('Annual','OneTime') binary NOT NULL default 'Annual',
StartDate varchar(200) binary NOT NULL default '',
EndDate varchar(200) binary NOT NULL default '',
Name varchar(100) binary NOT NULL default '',
County varchar(50) binary NOT NULL default '',
Notes varchar(255) binary NOT NULL default '',
StartingDay varchar(30) NOT NULL default '',
StartingMonth int(2) default NULL,
StartingYear year(4) default NULL,
EndingDay varchar(30) NOT NULL default '',
EndingMonth int(2) default NULL,
EndingYear year(4) default NULL,
PRIMARY KEY (Id)
) TYPE=MyISAM CHARSET=latin1;
it was created on a test server (mysql 4.1-alpha) and needs to be created on
the real server (mysql 4.0.18)...
i try and do a \. events2.sql to run the script its in to create it and all
i end up getting on the real server is this:
error 1064: syntax error. check the manual for the correct syntax to use for
your version of mysql near default 'Annual', StartDate varchar(200) binary
NOT NULL default '',
EndDate varchar(200) bina
any ideas what could be the problem with this? the table unfortunately needs
to be created exactly as it is shown above... i wondering if the default
'' sections of the column defines are giving it the problem...shrug
any ideas...?
tnx
--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
making mysql work with c++
hi... i was interested in making mysql work with c++... how would i go about doing this? to be more exact i use borland builder 6 and i have databases that i want to use with it and dont know how to use it with c++... -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: backup table/restore table question
possible but i find a very severe conflict with mysqldump between what the prod server will understand and what the test server will understand.. example: at the bottom of a create table section of mysqldump you will see the line : /*4 ALTER TABLE! LOCK READ--*/ and of course gives error 10064:syntax error so i usually have to edit/comment out those lines so backup tablename is faster and works better usually... - Original Message - From: Riaan Oberholzer [EMAIL PROTECTED] To: Andy B [EMAIL PROTECTED] Sent: Tuesday, April 13, 2004 2:20 AM Subject: Re: backup table/restore table question I use mysqldump which generates sql for you and it works like a charm. Eg, mysqldump --user=root databasename.table output.sql The output.sql file then has SQL statements to generate and exact copy. --- Andy B [EMAIL PROTECTED] wrote: hi... i have a server where there are 5 tables inside a database. i was wondering if i did the following command from inside a script: backup table dbname.tablename to /home/users/my_dir/ then with a different script gzip/tar them, after gzipped ftp them to my test server into say c:/db_backup/db_name, connect to my local test server and then type: restore db_name.tablename from c:/backup/db_name/ and have it work -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe: http://lists.mysql.com/[EMAIL PROTECTED] __ Do you Yahoo!? Yahoo! Small Business $15K Web Design Giveaway http://promotions.yahoo.com/design_giveaway/ -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
backup table/restore table question
hi... i have a server where there are 5 tables inside a database. i was wondering if i did the following command from inside a script: backup table dbname.tablename to /home/users/my_dir/ then with a different script gzip/tar them, after gzipped ftp them to my test server into say c:/db_backup/db_name, connect to my local test server and then type: restore db_name.tablename from c:/backup/db_name/ and have it work -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
reindex auto_incroment field in myism table?
would anybody know how to reindex an auto_increment field in a table??
table dumps and md5
Hi, Is it totally possible that when dumping a db to a text file with mysqldump that any encrypted stuff in the tables will be lost? when I mean lost I mean that it isnt the same in a text file as it is when it was originally encrypted. I have a problem with creating a table with encrypted passwords with md5, dumping it to a text file with mysqldump and then running that script on a different server. For some strange reason the encrypted passwords lose their encrypted state and are nothing more now than text strings. Is there any way to preserve the state of md5 encrypted fields when doing a mysqldump?
md5 and table field types
hi... what would be the best field type and length for an md5 encrypted password sort of thing??
table handler errors and lost data
Description: Users enter data from a php based web site and receive no errors or warnings of any kind that something is amiss, but the data isn't entered into the tables. When I go to the mysql command line and run commands i get a table handler error. How-To-Repeat: this happens every once in a while and i don't know how to make it happen Fix: what i do to fix this is to stop mysqld and run myisamchk on all the tables then restart mysqld. This works for a little bit, but the problem always reoccurs. If i upgrade to the lates version of mysql it fixes it for a few months, but every time the same thing has eventually started happening again Submitter-Id: submitter ID Originator: Organization: organization of PR author (multiple lines) MySQL support: [none | licence | email support | extended email support ] Synopsis: synopsis of the problem (one line) Severity: [ non-critical | serious | critical ] (one line) Priority: [ low | medium | high ] (one line) Category: mysql Class: [ sw-bug | doc-bug | change-request | support ] (one line) Release: mysql-3.23.49 (Official MySQL binary) Environment: machine, os, target, libraries (multiple lines) System: SunOS ccac 5.7 Generic_106541-04 sun4u sparc SUNW,UltraSPARC-IIi-Engine Architecture: sun4 Some paths: /usr/local/bin/perl /usr/local/bin/make /usr/local/bin/gcc /usr/ucb/cc GCC: Reading specs from /usr/local/lib/gcc-lib/sparc-sun-solaris2.7/2.8.1/specs gcc version 2.8.1 Compilation info: CC='gcc' CFLAGS='-O3 -fno-omit-frame-pointer' CXX='gcc' CXXFLAGS='-O3 -fno-omit-frame-pointer -felide-constructors -fno-exceptions -fno-rtti' LDFLAGS='' LIBC: -rw-r--r-- 1 bin bin 1696236 Apr 19 1999 /lib/libc.a lrwxrwxrwx 1 root root 11 Mar 22 2000 /lib/libc.so - ./libc.so.1 -rwxr-xr-x 1 bin bin 1118620 Apr 19 1999 /lib/libc.so.1 -rw-r--r-- 1 bin bin 1696236 Apr 19 1999 /usr/lib/libc.a lrwxrwxrwx 1 root root 11 Mar 22 2000 /usr/lib/libc.so - ./libc.so.1 -rwxr-xr-x 1 bin bin 1118620 Apr 19 1999 /usr/lib/libc.so.1 Configure command: ./configure --prefix=/usr/local/mysql '--with-comment=Official MySQL binary' --with-extra-charsets=complex --with-server-suffix= --enable-thread-safe-client --enable-local-infile --enable-assembler --disable-shared Perl: This is perl, version 5.005_03 built for sun4-solaris - 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
