PHP: Tagging system JOIN statements

Question

I've implemented an SO-style tagging system to my comics site.

Each comic can have 1 or many tags.

The way I have it set up currently is: There's a comics table, tags table, and a relational comictags table. The query will check if there is a matching comic id and tag id...

$sql = 
 "SELECT c.*, t.* 
 FROM comics c 
 INNER JOIN comictags ct ON (c.id = ct.comicID)
 INNER JOIN tags t ON (t.tagid = ct.tagID)
 WHERE ct.tagID IN ('" . implode(', ', $_SESSION['tagids']). "')
". $catquery ." " . $order;

enter image description here

The problem with this is it forces me to add a new entry to comictags table each time I want to add a new tag to a comic id.

Is it possible to have more of this schema:?

comicid_1: tagid_1, tagid_2, tagid_3, etc...

Meaning, I make 1 entry for each comic id, and add a comma separated tag id in the tagid field for each tag I want associated.

Thanks!

The only thing I would do is drop ComicsTags.id. Your primary key is (imgid,tagid), why add more unneeded data? — Wrikken
– Wrikken, Commented Mar 5, 2013 at 18:15
The id column in your ComicsTags table. What is it good for? It doesn't add anything. — Wrikken
– Wrikken, Commented Mar 5, 2013 at 18:24
@Wrikken I suppose you're right. Maybe I'll need it at some point if I want to delete a specific record based on that ComicTag id? idk haha — user3871
– user3871, Commented Mar 5, 2013 at 18:43
A specific record, of which you know the imgid & tagid? Well, that would be tricky, up to 2 WHERE clauses... Madness! — Wrikken
– Wrikken, Commented Mar 5, 2013 at 19:03

Oswald · Accepted Answer · 2013-03-05 18:04:56Z

2

The way you are doing it at the moment is perfectly fine. What you intend to do is not. It violates the first normal form: each attribute may only contain atomic values.

It is, of course, possible. For example by using a string type for the tagid column. But this complicates a lot of queries and is probably not good for performance.

edited Mar 5, 2013 at 18:04

answered Mar 5, 2013 at 17:59

Oswald

31.8k3 gold badges45 silver badges72 bronze badges

Sign up to request clarification or add additional context in comments.

3 Comments

user3871 Over a year ago

Okay, but I don't see how it's atomic in the current state. And I still don't see how adding comma separated tags per comic id would be problematic

Wrikken Over a year ago

@Growler: imagine querying for all comics having a specific tag-id. It's horrendous if the id's are in 1 column. Multiple bad performing LIKE's are the best you can manage at that point, which requires a full table scan.

PaulP Over a year ago

@Growler: I agree with Oswald. Many to many relations, like many tags to one comics and many comics to one tag has been always solved by creating table like yours: ComicsTags.

PaulP · Accepted Answer · 2013-03-05 17:59:22Z

1

I don't know any simpler sql expresion like that:

INSERT INTO ComicsTags VALUES (1, 10), (1,20), (1,30);

For scripting language level you can write method for model for that table which allow you do that a little simpler:

$comicsTags = new ComicsTags();
$tags = array(10, 20,30);
$comicsTags->add(1, $tags);

answered Mar 5, 2013 at 17:59

PaulP

1,9452 gold badges21 silver badges25 bronze badges

Collectives™ on Stack Overflow

PHP: Tagging system JOIN statements

2 Answers 2

3 Comments

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

3 Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Related