Hi again Bob,
>>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.
>
>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?
I meant scalability in terms of the number of items in the SET
(not the number of rows in the table). For example, consider a
SET field with 26 items -
eg. set_col SET ('A','B','C'..... 'Z')
If I wish to find out which values of the SET have been used
at least once, I have to either:
a) Query for each member of the SET and find out which gives a result:
SELECT * FROM my_table WHERE set_col LIKE ('%A%')
SELECT * FROM my_table WHERE set_col LIKE ('%B%')
etc.
b) Select DISTINCT(set_col) and then parse the results
for for each member of the set .. which is a worse method.
I was hoping that since each member of the SET really
represents a number, that there would be some bitwise
operation that would painlessly produce all the members
of the SET that appear at least once.
If not, no worries - I'll redo the database properly
when I get some time.
Thanks again,
chas
>> >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
>
>
---------------------------------------------------------------------
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
