I may have been reaching a little on the dynamic sorting since some of
what I had in mind entails adding more data to the categories. But at a
minimum, you would be able to sort ascending or descending and possibly
use an index on the sort depending on the query. Depending on how you
join your tables in the query, you would be able to replicate the main
data so it appears under each category it is assigned to. Think of
sorting/grouping.
You would be able to sort on the second word in each category if you
wanted. You could also added more data to your categories, like a sort
order field or a weighting field for relevance sorting. Since you are
actually using a database table, you are not just stuck with using a
word, you can link other data to the category and use it for
calculations or sorting. You could even create subcategories, and sort
on category/subcategory.
On Apr 8, 2005, at 12:25 PM, Marilyn Davis wrote:
> On Thu, 7 Apr 2005, Brent Baisley wrote:
>
> Thank you Brent.
>
>> As mentioned in the manual you specified, there are limits to how many
>> items you can have in a set, among other issues. My biggest issue
>> against using sets is that you need alter the table structure whenever
>> you want to change the set. Usually I would want the user to be able
>> to add, modify or delete list items. You do NOT want a user being able
>> to alter the table structure.
>> The three table layout gives you basically unlimited scalability,
>> easier and quicker modification, dynamic sorting, more flexible
>> searching and no character restrictions.
>
> I can see the unlimited scalability, quicker modification, and no
> character restrictions. "Dynamic sorting"? Do you mean there's a
> sort that the three table layout can do that can't be done with the
> set type? Or a search?
>
> I'm sorry to be so dense, but can you give me an example?
>
> Thank you again.
>
> Marilyn Davis
>
>>
>>
>> On Apr 7, 2005, at 5:20 PM, Marilyn Davis wrote:
>>
>>> Hi MySQL people.
>>>
>>> I'm trying to understand the pros and cons of the "set" column type.
>>>
>>> Here is an excerpt from the article:
>>>
>>> http://dev.mysql.com/tech-resources/articles/mysql-set-datatype.html
>>>
>>>
>>> Why You Shouldn't Use SET
>>>
>>> "Third, using a set means your data is not normalized. In our above
>>> example, we are tracking a person's interests for a hypothetical
>>> dating site. In a normalized schema, there should be three tables:
>>> one
>>> for the person, one for all possible interests, and one that links a
>>> person to their particular interests."
>>>
>>> The "above example" is:
>>>
>>> CREATE TABLE set_test(
>>> rowid INT UNSIGNED NOT NULL AUTO_INCREMENT PRIMARY KEY,
>>> myset SET('Travel','Sports','Dancing','Fine Dining')
>>> );
>>>
>>> I guess I'm not understanding the point here. What practical
>>> advantage is there to creating three tables instead of using a set
>>> column type?
>>>
>>> Thank you for any help.
>>>
>>> Marilyn Davis
>>>
>>>
>>>
>>>
>>> --
>>> MySQL General Mailing List
>>> For list archives: http://lists.mysql.com/mysql
>>> To unsubscribe:
>>> http://lists.mysql.com/mysql?unsub=1
>>>
>>>
>>
>
> --
>
>
--
Brent Baisley
Systems Architect
Landover Associates, Inc.
Search & Advisory Services for Advanced Technology Environments
p: 212.759.6400/800.759.0577
