Storing Tags

[adam84]

Hello,

 

In my site a user can add an article; one of the options they can fill out is to enter up to 10 tags that described the article. My question is I am not sure on how to save that tag information in my database.

1. Store all the tags in one column separated by a space.

2. Store one tag in a row. 2 columns – articleID and tag

3. Store all the tags in a row. 11 rows – articleID and 10 tag columns

These are the ways I though how it might work, any ideas or better methods to solve this? Thanks

 

[BlueSkyIS]

as there will always be a max 10 tags, I would store them in one column separated by the pipe symbol or something else "odd", but not spaces as a tag may contain spaces, no? then you could explode the stored value on pipe symbol to get an array of the tags. (I'm assuming PHP.)

[adam84]

Ya I am using PHP.

By doing it that way, will it be hard on the system when I run a search based on the tags

[Adam]

Storing the tags within 1 column will prevent (or at least impede) you performing any 'intelligent' queries on them. Consider how you'd return the most popular tag if they're all strung together? Or even selecting articles matching that tag?

 

You should store each tag within a separate row, linked to the article by the ID. Don't worry about using plenty of rows, MySQL is more than capable of handling it.

[adam84]

awesome thanks

[Adam]

No problem. You could even increase performance by adding a unique constraint across the two fields, which will also prevent duplication. Something like:

 

 

create table tags (    article mediumint unsigned, -- use same data type as ID column in articles table    tag varchar(20) not null,    unique(article, tag));

 

[adam84]

Thanks, what would be the best search option, using the 'like' or 'match against'