CODEWITHSUNDEEP
phpmysqltags
Lior
Lior

Reputation: 5664

What is the more efficient way to handle tags database-wise?

Is it more efficient to use a taglist field, with all the tags separated by a space, or use 2 more tables (tag: tagid tagtext, tagitem: tagid, itemid)?

Upvotes: 1

Views: 387

Answers (4)

GolezTrol
GolezTrol

Reputation: 116100

The second option. Store the tags separately. You won't be able to write good queries to search on a specific tag if you store them in a single field. You don't want to use MATCH or LIKE to filter on tags. By storing them in a separate table, you can easily find the tags you need, and the related articles too. Your tables do need to be properly indexed, though.

Never store comma/space/otherwise separated values in a database if you need to query for those values. The whole essence of a database is to store the data in a structured way. This way the database can optimize the retrieval of that data to a great extent.

Upvotes: 1

Explosion Pills
Explosion Pills

Reputation: 191729

The efficiency largely depends on what you are doing. If you want to query based on the tag name, it is probably faster if you have a tag table with the ID keyed on both the tag and items table (i.e. option #2). However, unless you have thousands of rows of either, it probably won't make a difference. If you don't have that many tags at all, the difference will be even less.

If you want to get tags by item IDs, though, the first method is ever so slightly faster. Again, I doubt you will notice.

There are other considerations to make: data integrity and normalization. If you use two tables and foreign keys, it is much easier for you to have your set of tags be consistent with the items. If a tag is removed and you are only using one table, old items will still have the old tags. Additionally, it's much easier to get a list of unique tags and keep it consistent. If you have tags in another table, this opens up a whole new world of organization: you can make timestamps for tag creation and modification, mark tags as active or inactive (and possibly other statuses), etc.

Upvotes: 3

nfechner
nfechner

Reputation: 17525

The second version, to split the data into two additional tables, is a lot more efficient, as it allows the database to use indexes to run the queries you mostly need (Get all texts with a certain tag, get a count of how often the tags are used sorted by count for the tag cloud, and get all tags for the given text)

Upvotes: 0

piotrp
piotrp

Reputation: 3854

One table will be more efficient, but having two tables is generally the proper way to store simple tags.

Upvotes: -1

Related Questions