Re: Multiple entries in 1 field
Date: Sun, 6 Oct 2019 20:23:38 -0400
Message-ID: <qne0ib$are$1_at_jstuckle.eternal-september.org>
On 10/6/2019 4:00 AM, 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.
>
This is the proper way to do it.
What you have is a many-to-many connection (each category can have many stories and each story can have many categories). You would have something like (pseudo-code):
Story: Story_id, Title, Author....
Category: Category_id, Category_Name
Category_story: Story_id, Category_id
To get all Science Fiction books you would use
SELECT Title
FROM Story
JOIN Category_story ON Story.Story_id = Category.Category_id
JOIN Category ON Category.Category_id = Category_story.Category_id
WHERE Category = 'Sci-fi';
Multiple values in a field (i.e. "Sci-fi, Romance") in a single field is a violation of First Normal Form (see "Database Normalization" at https://en.wikipedia.org/wiki/Database_normalization - not perfect but a pretty good definition) and cannot easily be searched, even if they are in JSON format. They also cannot be indexed.
-- ================== Remove the "x" from my email address Jerry Stuckle jstucklex_at_attglobal.net ==================Received on Mon Oct 07 2019 - 02:23:38 CEST