1

I am learning MySQL and have MariaDB installed in Fedora 19.

I have a scenario where I require a column to contain multiple values in order to reduce possible redundancy of column allocation.

In the example below, is it possible to have each value in the tags column of the log table reference the tag_id column in the tags table?

users

user_id        |
1              |

activities

activitity_id  |
1

log

user_id        |    activity_id    | tags
1              |    1              | 1,3,5 # multiple foreign keys?

tags

tag_id         |
1              |
2              |
3              |
4              |
5              |

If it is not possible, could anyone provide the logic for the most feasible solution based on the data scenario above?

Similar Questions:

Are multiple foreign keys in a single field possible?

MySQL foreign key having multiple (conditional) possible values

it is possible to reference one column as multiple foreign keys

Community
  • 1
  • 1
user1063287
  • 10,265
  • 25
  • 122
  • 218

1 Answers1

2

If you do not wish to make up a "middle man" table for linking the two tables you can have a comma separated value in the field, you would just need to use the find_in_set mysql function when doing queries

USING find_in_set

SELECT
   log.user_id, log.activity_id, log.tags,
   GROUP_CONCAT(tags.name) as taggedNames //This assumes there is a field called `name` in tags table
FROM
   log
LEFT JOIN tags
ON
   FIND_IN_SET(tags.tag_id,log.tags)
GROUP BY
   log.activity_id

GROUP_CONCAT will group together a field and separate them by a deliminator, default is ,

Patrick Evans
  • 41,991
  • 6
  • 74
  • 87
  • `ERROR 1054 (42S22): Unknown column 'log.user_id' in 'field list'` – user1063287 Jul 29 '13 at 15:51
  • 1
    @user1063287 forgot to add the `log` table to the `FROM` clause – Patrick Evans Jul 29 '13 at 15:54
  • `Empty set (0.01 sec)` - do i need to add further definition to the above query? The contents of record 1 in log.tags is `'1,2'` (VARCHAR(30)). – user1063287 Jul 29 '13 at 16:02
  • 1
    It might be that you need a left join instead, (edited answer to show), my server is down so i cant test properly – Patrick Evans Jul 29 '13 at 16:10
  • Both the `LEFT JOIN ON` and `log, tags WHERE` solutions are producing a result. The only anomaly I can see, in both solutions, is that where `log.tags` are ouput as `1,2` the corresponding `tags.name` are in what seems to be the reverse order ie `tag_name_two, tag_name_one`. But I will try and troubleshoot this, perhaps it is an ORDER BY thing. – user1063287 Jul 29 '13 at 16:27
  • 1
    Yea if you want them ordered you will have to do an order by on the tag names. But do it like `order by log.activity_id, tags.name` or similar order on some part of the main row as otherwise if you do it simply by tags.name the log results may be out of order – Patrick Evans Jul 29 '13 at 16:29