[RESOLVED] MySQL tagging system

[kows]

I've created a tagging system to relate one or more rows in one table to multiple rows in another table. the only problem that I can see is that I'm using a stored function (undesirable), and I have repetitive SQL that I don't like. plus, I have to hardcode the number of tags to find, I can't just .. find all tags, or something.

I can easily accomplish what I'm trying to do using a mix of server-side programming with SQL, but I'd prefer to keep this on the database-side of things if possible. The stored function I'm using is simply a string delimiting function (used in the fashion: STRSPLIT(string, delimiter, position)):

CREATE FUNCTION `STRSPLIT`(x varchar(255), delim varchar(12), pos int) RETURNS varchar(255) CHARSET latin1
return replace(substring(substring_index(x, delim, pos), length(substring_index(x, delim, pos - 1)) + 1), delim, '')

which I can use with the following SQL to bring up 10 rows from tagTable.

SELECT * FROM `tagTable` WHERE shortName IN (

  (SELECT STRSPLIT(`tag`, ',', 1) FROM `mainTable` WHERE shortName='abcde'),
  (SELECT STRSPLIT(`tag`, ',', 2) FROM `mainTable` WHERE shortName='abcde'),
  (SELECT STRSPLIT(`tag`, ',', 3) FROM `mainTable` WHERE shortName='abcde'),
  (SELECT STRSPLIT(`tag`, ',', 4) FROM `mainTable` WHERE shortName='abcde'),
  (SELECT STRSPLIT(`tag`, ',', 5) FROM `mainTable` WHERE shortName='abcde'),
  (SELECT STRSPLIT(`tag`, ',', 6) FROM `mainTable` WHERE shortName='abcde'),
  (SELECT STRSPLIT(`tag`, ',', 7) FROM `mainTable` WHERE shortName='abcde'),
  (SELECT STRSPLIT(`tag`, ',', 8) FROM `mainTable` WHERE shortName='abcde'),
  (SELECT STRSPLIT(`tag`, ',', 9) FROM `mainTable` WHERE shortName='abcde'),
  (SELECT STRSPLIT(`tag`, ',', 10) FROM `mainTable` WHERE shortName='abcde')

);

as you can see, this is kind of ridiculous. using 10 sub-queries is probably just a little inefficient, too. the SQL would be generated dynamically, but it's still kind of an ugly block of code that I wouldn't want to put into my application unless needed. my alternative solution would be to fetch the tag field from mainTable, and build a string server-side instead, which would produce SQL similar to:

SELECT * FROM `is_career` WHERE shortName IN ('value1', 'value2', 'value3', 'value4');

so, SQL gurus, is there a better way that I could maybe go about this? any and all suggestions are welcome.

[si_the_geek]

You haven't explained how this "tagging system" works, but having read between the lines a bit, am I right in thinking that one table has a comma delimited string, each element of which contains an ID number for a record in the other table?

If so it is significantly better (in terms of query speed/functionality, time to write/maintain queries, and probably database size too) to not use a column, but to use an extra junction table with two fields (the ID numbers of both tables), and one row for each delimited element.

[kows]

thanks for the response, si! and whoops. yes, you were correct in your thinking. I guess I forgot to mention that the table had a comma delimited string in one field.

the web application I'm building will be used for students to find information about careers. so, there are: careers, industries, companies, schools, videos. companies would have related careers, industries, and videos. schools would have the same. if I were to use your idea, I would need a table for each of these relationships -- SchoolsToCareers, SchoolsToIndustries, SchoolsToVideos -- would I not? this is not exactly desirable either, but it would work.

after thinking about having a junction table, I thought I might instead have a table with four fields. along with the two fields for both IDs, I'd have two enums that described what those IDs represent (one enum would be company/school, the other would be career/industry/video). this wouldn't do much other than lessen the number of tables used, I guess. do you think this would be as efficient, or just less so?

[si_the_geek]

if I were to use your idea, I would need a table for each of these relationships -- SchoolsToCareers, SchoolsToIndustries, SchoolsToVideos -- would I not?

Correct.

after thinking about having a junction table, I thought I might instead have a table with four fields. ... do you think this would be as efficient, or just less so?

It would be less efficient (in terms of query complexity and speed, and data size), but not as bad as the comma delimited method.

While reducing the amount of tables seems like a good thing, in this kind of situation it rarely gives enough benefit to compensate for the down sides.

[kows]

I'll try out the original idea and see how it goes. thanks, again.