Mysql – best way to store tags

database-designMySQLweb server

I'm a university student building a web application for a project with three collegues , I took the database portion.

Our site is basically a link sharing site and supports tags, what is an efficient way to store tags in Mysql database ? I thought of these ways :

1- table for tags : the relation between link and tag is many-to-many so I must break it with an intermediate table, I can't think of an SQL statements to fetch links with specific tags example : the user might search for links tagged with (php , mysql ) or (hardware-issues , gigabyte).

Also even if I did , it needs join , we're making the site global, that means the database will be very very huge some day , I don't think we can bare a join.

2-putting tags as an array in links table : I can put an array of strings, this means only one select statement(better than join I think) but manipulating the array will be hard, the link's poster might edit the tags on his/her link, I can figure out a way to deal with this but I'm not sure if this is the most efficient way , of course it's not normalized and the database will grow faster but I think on a server , speed is more important than size.

So is there a better way ?

Best Answer

Why do you think that you will not be able to bare a join? In fact if I understand correctly, your other idea is to put a list of tags into a varchar column (am I correct?) and search links by searching text in that column and that will definitely be much slower since you will have to full-scan the links table for every search.

Joins are normal, every-day operations in relational databases and are not something that you should avoid.

So say you have 3 tables,

links(link_id, url)
tags(tag_id, tag)
tags_links(tag_id, link_id)

You want to find all links with tags in (php , mysql)?

select l.link_id, l.url 
from links l 
    inner join link_tags lt on (l.link_id = lt.link_id)
    inner join tags t on (lt.tag_id = t.tag_id)
where t.tag in ('php', 'mysql')

links.link_id and tags.tag_id must be primary keys. In tag_links you can put primary key on combination (tag_id, link_id) - the supporting index will make searches by tag_id fast. You can an should also index the link_id column in that table, so that you can find all tags for a given link fast. Also, put an unique index on tags.tag column so that you can find tag_id from tag fast (and tags should be unique anyway, so you're enforcing an important business rule with such a constraint and index).

Try that out, fill some imaginary test data into such a schema, say, 1M links, 10k tags and 10M tags_links, and test the performance. I think you'll be surprised with the results.

Related Solutions

Sql-server – Best way to store units in database

Since you mention hundreds of columns I would consider an EAV design. While Joe Celko warns against this, I think it may be applicable in your use case. It sounds like all of your "amounts" are numbers, so you would avoid the casting issues Joe describes and the need to make every "value" a string. It will work even better if all the amounts are whole numbers, but can work also if some are decimal. Given the Units of Measure, you could go one step further and implement an "universal data model" style model based off this article by David Hay and also outlined in his book Data Model Patterns: Conventions of Thought. This model has the additional advantage of configuring which "amounts" apply to which "things" if you need that. One additional step shown in the book on page 162 is a Unit of Measure Conversion table that you can use to convert between the different Units of Measure. Here is an example:

UOM Conversion              

UOM From    UOM To        Cal Step  Operator Factor Constant
Kilograms   Pounds        1         *        2.2
Celsius     Fahrenheit    1         *        1.8
Celsius     Fahrenheit    2         +               32

This says that to convert from Kg to Lb the first step is to multiply Kg by 2.2. There is also a constant if a conversion must also include a constant value, and the ability to create multiple steps. So when converting say Celsius to Fahrenheit you multiply Celsius by 1.8 and then add 32. The key would be the from UOM, the to UOM, and the Calculation Step.

That is my 2 cents worth. I hope these references give you some good food for thought should you ever get the chance to do a reboot on the current design.

SQL Server – Best Way to Store Immutable Read-Only Data for Logging

You could treat your reference tables as slowly changing dimensions and perform type 2 maintenance i.e. add a new reference row when any reference data changes. Over time you may choose to horizontally partition your reference tables into "active" and "archive" parts, depending on the data churn you experience. This can be achieved using using SQL Server's built-in partitioning functionality or a roll-your-own approach with two separate tables. Your needs at the time will dictate which.

There is no good reason why your active OLTP table and your logging table should look the same. They perform different roles and have different read and write requirements. If the logging table needs to be wide and sparse then that's what it needs to be. Create the objects to solve the problem you have.

And one last suggestion, which is defininetly from the "clutching at straws" bucket: define TASK_DONE (id int, reference_values xml). Extract your pertinent values at the point in time and save them away. This allows for changes in the reference data schema without having to bring existing log records up to the new schema. That will make historical searching more complicated, of course.

Best Answer

Related Solutions

Sql-server – Best way to store units in database

SQL Server – Best Way to Store Immutable Read-Only Data for Logging

Related Question