store tags in mysql table - one field for all tags or one for each

Each of my users (clients) of my website has the possibility to store in his accounts different text 'documents' contained in a table field 'text'. He should have the possibility to add up to 7 tags per document (record). These tags should then make it possible to extract all documents with the same tag. Question: What is easier for handling (register, search, extract): declare 1 field per tag in a mysql table (therefore tag1, tag2, tag3...) or have only one field with tags separated with spaces or comas.
swissofficeAsked:
Who is Participating?
 
Scott MadeiraConnect With a Mentor Commented:
I would have two tables in your design.

First table would be the document table that had a documentID field and a document text field (you will probably have more than that but these are the minimum.)

Second table would be a tags table that had three fields.  A tagID field to identify the row, a documentID field to identify the document the tag applied to and a tag field that had the value of one tag.

so, a document with 6 tags would have one row in the document table and six rows in the tags table.  you can query the tags table to get the documentIDs that have your tag of interest and then get the documents from your document table based on the documentIDs.  Hope that helps.


For example, if you want all documents with the tags red and ball you would have sql liek this:

 
select 
    d.documentID, 
    d.text 
from
   tags t 
   left join document d on (t.documentid = d.documentid)
where
     t.tag in ('red', 'ball')

Open in new window

0
 
Ray PaseurCommented:
Completely agree with smadeira about the table structure.  This design is really about two things: documents and tags.  Documents can go into their own table and tags can go into a separate table, perhaps with pointers to the client id and the document.  Tags might be uniquely associated with the client who created them.  For example, I might post my CV and tag it "cv" - then you might do the same thing.  Your search should find the files you tagged, not necessarily all the files with tags that anyone created.

In data design there are three numbers that matter: Zero, One and Infinity.  With an open-ended tag table, you meet the requirement that tags have a one-to-many relationship with the documents.
0
 
swissofficeAuthor Commented:
Thank you, exactly what I needed. Great Job.
0
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

All Courses

From novice to tech pro — start learning today.