List:General Discussion« Previous MessageNext Message »
From:Brent Baisley Date:April 8 2005 6:37pm
Subject:Re: set type and normalize
View as plain text  
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

Thread
set type and normalizeMarilyn Davis7 Apr
  • Re: set type and normalizeBrent Baisley7 Apr
    • Re: set type and normalizeMarilyn Davis8 Apr
      • Re: set type and normalizeBrent Baisley8 Apr
        • Re: set type and normalizeMarilyn Davis9 Apr
  • Re: set type and normalizeMichael Stassen8 Apr