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?

[Product update] Infrastructure Analysis Tool is now available with Business Accounts.Learn More

x
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

Scott MadeiraCommented:
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

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
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
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
PHP

From novice to tech pro — start learning today.