Re: Multiple entries in 1 field

From: Lyle H. Gray <lylefitzw_at_no.spam.gmail.com.invalid>
Date: Sun, 06 Oct 2019 08:49:28 -0500
Message-ID: <XnsAAE063F0CC0FAgraynoibisspamcsumas_at_216.166.97.131>

Luuk <luuk_at_invalid.lan> wrote in news:5d99a464$0$10280 $e4fe514c_at_news.xs4all.nl:

> On 6-10-2019 10:00, J.O. Aho wrote:

>> On 06/10/2019 05.39, groovee_at_cyberdude.com wrote:
>>> So, say I have a "stories" db, with fields such as storyid, storytext,

>>> dateposted, and category. Now under "category" I want it to have 
>>> MULTIPLE CATEGORIES, ie. *one* story can be classified as "sci-fi" AS 
>>> WELL AS "romance" AT THE SAME TIME!! But I would like it to be 
>>> queryable, ie. if I want to find all the "sci-fi" stories in the db, 

I
>>> should be able to do so. How can I do this? (I would kind of not like >>> to fiddle with raw data structures when doing this type of thing.... -

>>> is there some way COMPLETELY inside MySQL?)

>>
>> The best way would be to have a stories_categories table, which just
>> have two columns, one the storyid and the other categoryid. Making a
>> joint primary key of both the storyid and cateoryid would make you
safe
>> from duplicate categories for stories.
>>
>> If you have a newer mysql/mariadb, then you have json data type, it
>> would be possible to use it to keep multiple values in one column, but
>> you loose some sql power queries that you can use if you use two
tables
>> instead.
>>

> 
> How would a solution which uses json be better than a solution like:
> - storing in category the value 'sci-fi, romance'
> - doing a query SELECT ... FROM ... WHERE category LIKE '%sci-fi%';

[Quoted] Using a list field like that would remove the possibility of placing an index on the category (indexes can really speed up data retrieval).

-Lyle Received on Sun Oct 06 2019 - 15:49:28 CEST