>Thanks for the reply Bob,
>
>May I ask then what is the purpose of the SET(...) datatype ?
It has some peculiarities that make it useful sometimes. Among other
things, the values are actually stored as numbers, which can be handy
when you're sorting.
>In the past, I've always avoided it since I assumed that any
>search that uses LIKE '%x%' in a query would be slow. However,
>I now have a table that will never grow to more than 5,000
>entries and using the SETs for 3 of the columns has made life
>way easier so far, especially when creating the administration
>screens for this application. 5,000 rows is very small for
>a database and my queries using LIKE '%x%' have been very
>fast. In fact, the only problem I've encountered is this inability
>to retrieve a list of all the SET values that are used at
>least once.
>
>I know it's not 'proper' database design and I should have
>normalized the one-to-many relationships to multiple tables.
>However, it was fast and gave me a result which worked quickly
>(and up against a mad deadline).
I think you've just identified the most common use of the SET datatype.
>
>Thanks again,
>
>chas
In your original posting, you said that a procedural solution
wouldn't scale well. If you database will never be more than 5,000
records, why are you concerned about scalability? Are you planning on
processing a hundred concurrent queries?
Bob Hall
>
> >Bob wrote:
> >Sir, the basic problem is that you've violated first normal form,
> >which states columns should only contain atomic (indivisible) data.
> >Since you plan to divide up the data in your columns, it's obviously
> >not indivisible.
> >
> >My recommendation is to brush up on the first three normal forms and
> >redesign your tables. There may be a way to do what you want with
> >MySQL functions, but that will require a lot of processing and slow
> >your queries down. You'll be back where you started, with a solution
> >that doesn't scale well.
>
> >>Chas wrote:
> >>I have a column that is defined as a SET. For simplicity's sake,
> >>let's say the column is defined as:
> >>
> >>set_col SET ('A', 'B', 'C', 'D', 'E', 'F', 'G', 'H', 'I', 'J', .... 'Z')
> >>
> >>After entering a few hundred rows, I know that some but not all
> >>values of the SET have been used at least once. How can one work
> >>out which values have been used at least once ?
> >>
> >>Doing a SELECT DISTINCT(set_col) on the table might provide
> >>something like this:
> >>
> >>A,B,D
> >>A,C,G,N,M,P
> >>C,E,F,H,I,R,S
> >>C,E,G,I
> >>D,F
> >>G,H,L,M,O
> >>etc
> >>
> >>But it then requires processing to work out which
> >>values have been used at least once. It's not very
> >>scalable.
> >>
> >>Is there a method to retrieve a list of all the values
> >>that have been used at least once ?
> >
>
>
>---------------------------------------------------------------------
>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
Know thyself? Absurd direction!
Bubbles bear no introspection. -Khushhal Khan Khatak
---------------------------------------------------------------------
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
