[SOLVED] MySQL Tables and Tagging

[kingnutter]

Hi Everyone,

 

I am creating a specialist online thesaurus to which users can contribute.

 

To lessen the need of a heirarchical directory I would like to have the site searchable with tags that will be user defined when each entry is made by users.

 

Is it wise to start a separate "tags" table consisting of just two columns: wid (word id - the PRIMARY KEY of the 'words' table') and the tags themselves, which can be searched individually? Or can anyone tell me the more usual way?

 

Also, what is best in the data entry field for tags? Tags split by spaces, or tags split by commas. I wouls expect these to be split into arrays at the time of search but someone please correct me if I'm wrong...

 

Thanks all,

Gary

[friedemann_bach]

I think you should create a 1:n table with the fields

 

wid -> PRIMARY KEY and

tag -> VARCHAR(x),

 

so that each tag gets a single entry. This might be useful if you want to output the tags alphabetically. Depending on the size of your database, indexing the tag field might be helpful for your performance.

 

I would avoid to store multiple tags in one field, as the number of tags could be inpredictable.

 

[kingnutter]

That sounds like an interesting approach. The main use for the tags would be as a simple search tool.

 

So as an example, the table you suggest could look something like this:

 

wid          tag

0001        splice

0001        cut

0001        trim

0001        snip

0002        movie

0002        flick

0004        dissolve

0004        dip

0004        fade

 

This is where wid is the PRIMARY KEY for entries in the word database. 0001 is the wid of entry "Edit", 0002 is "Film" and 0004 is "Transition".

 

I have left out 0003 as though the submission did not include any tags, or should it default to NULL?

 

Please correct me if I'm wrong, but I see the entry process as follows:

1) When entering a word into the thesaurus, the user enters list of tags separated by commas into the "tags" field.

2) The code strips this of commas and creates an array (a php thing I think)

3) The array is entered into a special tags table as separate records using the wid of the word.

 

Thanks for your help.

[friedemann_bach]

I think that my above suggestion was misunderstandable, so I revise it here. The complete structure of the tags table would look like this:

 

id -> PRIMARY KEY, auto_increment

wid -> word id, refers to the other table

tag -> one tag entry

 

So your tags table would look like this:

 

id      wid          tag

0001  0001        splice

0002  0001        cut

0003  0001        trim

0004  0001        snip

0005  0002        movie

0006  0002        flick

0007  0004        dissolve

0008  0004        dip

0009  0004        fade

 

I would not create empty entries for words without tags. This could be lead to complications when you want to insert new tags (you would either have to update the empty tag or insert a new one, which would result in two tags, one of them empty). It is the more practicable way to have no entries.

 

The tags could be inserted either by a comma separated list (you should then check the user input for double commas or empty elements, like "tag1, , , tag2, tag3" or ",,,tag1,,,"). Or one by one, which might be easier for you but less comfortable for the user.

The comma separated list can then be converted to an array by the PHP explode() command, like $tag_array = explode (",",$comma_separated_list).

With a foreach() and MySQL > INSERT INTO tags (wid,tag) VALUES ($wid,'$tag') you can fill the tags table with the posted tags. Before inserting, you should check the existing tags to avoid double entries.

 

[ergecs]

I recommend this

 

Entry Table

entry_id -> PRIMARY KEY

entry_title -> VARCHAR

 

Tags Table

tag_id -> PRIMARY KEY

tag_name -> VARCHAR

 

Link Table

entry_id

tag_id

 

in friedemann_bach solution you will have to duplicate tag if you want to link 0009 with snip

[friedemann_bach]

I agree to ergecs (I was already writing about this when you posted your reply). That approach would be a N:M table that connects the tags to the words. Thus, every tag will only be saved once. This might be more complicated to implement, but it works more efficiently with large data masses.

 

[kingnutter]

All incredibly helpful.

 

However, as this is a thesaurus the tags will need to be repeated in different wids. I.e the user will be looking for all definitions that match the tag "snip". Or maybe the tags themselves should aqcuire an array of wid for each one?

 

I shall weigh up all the info above to determine how I optimise my tables but I expect a user search to initiate a query along the lines of:

 

SELECT wid WHERE tag = $users_search_keyword

 

and then echo iterating through the results.

 

Is it really that simple?

[friedemann_bach]

With the n:m approach your query would look much different. To get all words and their respective ids, a SELECT would look like as follows (I am assuming the three tables proposed by ergecs):

 

SELECT e.entry_id, e.entry_title

FROM entries e, tags t, links l

WHERE e.entry_id=l.entry_id

  AND l.tag_id=t.tag_id

  AND t.tag_name='$user_entry'

[ORDER BY entry_title]

 

As I said, this looks complicated but it works still very efficiently when your database size reaches more than 1,000 tags and/or words. The other approach might be more simple to implement but it would result in larger data masses. The output is all the same - you can store as many tags for every word, as well as any tag can be attached to every word you like.

 

[kingnutter]

That all looks good to me. I'll have a go at implementing it and let you know if I have any problems if that's ok.

 

This solves my concerns about duplicate tags I think, as in the Tags Table, all entries would be unique (which I expect is checked upon user entry. If the tag already exists, the Link Table assigns the entry_id to an existing tag_id).

 

Genius.

 

 

[friedemann_bach]

You're welcome.

 

If you're interested, have a look at

 

http://www.bbaw.de/forschung/avh/biblio/titel.php?id=4732

 

to see the frontend of a similarly structured database (n keywords are assigned to m books).

 

[kingnutter]

Hi everyone,

 

To return to the fourth post in this topic, could somebody explain how to check the user input for adjacent multiple commas.

 

Also, should I do this BEFORE exploding the tag list into an array, or for each array element after exploding.

 

I have a feeling I should now be posting this in the PHP boards, but as it was still part of an MySQL topic I have continued the thread. Apologies in advance if I have broken any protocol.

 

Many thanks,

Gary

[kingnutter]

Sorry. Didn't see that the thread was marked solved. I'll take this over to the PHP board.

 

Cheers.