Multiple labels (tags) with same name in same org are allowed

[FlorisFokkinga]

Occur in production in the SRC organisation.

Specifically, this query:

select  count(tag_id) as aantal, ct.*, t.tag_value, o.short_name as org
from collaboration_tags ct 
left join collaborations c on c.id=ct.collaboration_id 
left join organisations o on o.id=c.organisation_id
left join tags t on t.id=ct.tag_id 
where o.short_name = "surf_rsc"
group by tag_id 
order by t.tag_value, aantal
;

gives:	aantal	id	collaboration_id	tag_id	tag_value	short_name
1	358	1752	53	atag	surf_rsc
1	649	3288	119	no_start_account	surf_rsc
1	496	1955	103	removethis_start_account	surf_rsc
1	505	1965	107	removethis_start_account	surf_rsc
1	497	1955	104	src_sane	surf_rsc
1	506	1965	108	src_sane	surf_rsc
1	541	2018	111	src_sane	surf_rsc
1	557	2276	114	src_sane	surf_rsc
1	650	3288	120	src_sane	surf_rsc
1	554	2273	112	start-account	surf_rsc
1	540	2018	110	start_account	surf_rsc
1	556	2276	113	start_account	surf_rsc
275	20	1078	17	start_account	surf_rsc
3	19	1078	16	start_budget	surf_rsc

I think that (tag_value,org_id) should be unique in the db, but that would require storing the organisation in the tags table. That would make sense, I guess, because tags are tightly coupled to an Org, and tags between orgs should not interact in any way.

[oharsta]

mysql> show create table tags;

| tags  | CREATE TABLE `tags` (
  `id` int NOT NULL AUTO_INCREMENT,
  `tag_value` varchar(255) NOT NULL,
  `organisation_id` int NOT NULL,
  PRIMARY KEY (`id`),
  UNIQUE KEY `tag_organisation_unique_name` (`tag_value`,`organisation_id`),
  KEY `collaboration_tags_ibfk_2` (`organisation_id`),
  CONSTRAINT `tags_ibfk_1` FOREIGN KEY (`organisation_id`) REFERENCES `organisations` (`id`) ON DELETE CASCADE
) ENGINE=InnoDB AUTO_INCREMENT=743 DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci |

[baszoetekouw]

Works! Also tested the migration on prod data: works without a hitch!