PHPMyAdmin如何做数据类型的关系集

Question

today I decided to little automate my work in PHPMyAdmin, so i create many relationships, but there is one problem. For example i have two tables: foodRaws and allergens. In foodRaws table I have columns like name, tags, etc.. and allergens. So I created relationship between foodRaws.allergens and allergens.ID. Now, when I'm editing or inserting into foodRaws, I can directly select ID from allergens into foodRws.allergens field. But only one? When I have for example selected allergen ID 6 and I want to add ID 7 it replaces ID 6. But I need to create something like "6;7". So I create for this field datatype SET ('1','2',...,'14'), but it working in same way, I cannot select more than 1 ID for this field. Do you have any ideas how to solve that?

Tables:

CREATE TABLE IF NOT EXISTS `raws` (
  `id` int(10) unsigned NOT NULL,
  `nameCzech` varchar(50) COLLATE utf8_czech_ci NOT NULL,
  `nameEng` varchar(50) COLLATE utf8_czech_ci NOT NULL,
  `tags` varchar(100) COLLATE utf8_czech_ci NOT NULL,
  `allergens` set('','1','2','3','4','5','6','7','8','9','10','11','12','13','14') COLLATE utf8_czech_ci DEFAULT NULL
) ENGINE=MyISAM AUTO_INCREMENT=78 DEFAULT CHARSET=utf8 COLLATE=utf8_czech_ci;

CREATE TABLE IF NOT EXISTS `allergens` (
  `id` enum('1','2','3','4','5','6','7','8','9','10','11','12','13','14') COLLATE utf8_czech_ci NOT NULL,
  `nameCzech` varchar(50) COLLATE utf8_czech_ci NOT NULL,
  `nameEng` varchar(50) COLLATE utf8_czech_ci NOT NULL,
  `detailCzech` varchar(150) COLLATE utf8_czech_ci NOT NULL,
  `detailEng` varchar(150) COLLATE utf8_czech_ci NOT NULL,
  `img` varchar(15) COLLATE utf8_czech_ci NOT NULL
) ENGINE=MyISAM DEFAULT CHARSET=utf8 COLLATE=utf8_czech_ci;

and Data:

INSERT INTO `allergens` (`id`, `nameCzech`, `nameEng`, `detailCzech`, `detailEng`, `img`) VALUES
('1', 'Obiloviny obsahující lepek', 'Cereals containing gluten', 'pšenice, žito, ječmen, oves, špalda, kamut nebo jejich hybridní odrůdy', 'wheat, rye, barley, oats, spelled, kamut or their hybrids', 'cerreals'),
('2', 'Korýši', 'Crustaceans', 'a výrobky z nich', 'and products made from them', 'crustaceans'),
('3', 'Vejce', 'Eggs', 'a výrobky z nich', 'and products made from them', 'eggs'),
('4', 'Ryby', 'Fishs', 'a výrobky z nich', 'and products made from them', 'fish'),
('5', 'Arašídy', 'Peanuts', 'a výrobky z nich', 'and products made from them', 'peanuts'),
('6', 'Sójové boby', 'Soya beans', 's výjimkou zcela rafinovaného sójového oleje a tuků', 'except for fully refined soybean oil and fats', 'soya'),
('7', 'Mléko', 'Milk', 'a mléčné výrobky', 'and products made from milk', 'milk'),
('8', 'Suché skořápkové plody', 'Dry nuts', 'mandle, lískové ořechy, vlašské ořechy, kešu ořechy, pekanové ořechy, para ořechy, pistácie, ořechy makadamie a queensland', 'almonds, hazelnuts, walnuts, cashews, pecans, Brazil nuts, pistachios, macadamia nuts and Queensland', 'nuts'),
('9', 'Celer', 'Celery', 'a výrobky z celeru', 'and products made from celery', 'celery'),
('10', 'Hořčice', 'Mustard', 'a výrobky z hořčice', 'and products made from mustard', 'mustard'),
('11', 'Sezamová semena', 'Sesame seeds', 'a výrobky z nich', 'and products made from them', 'sesame'),
('12', 'Oxid siřičitý', 'Sulphur dioxide', 'a siřičitany v koncentracích vyšších než 10mg/kg nebo 10mg/l', 'and sulphites at concentrations greater than 10mg/kg or 10mg/l', 'sulphurs'),
('13', 'Vlčí bob', 'Big-leaved Lupine', 'a výrobky z vlčího bobu', 'and products made from lupine', 'lupine'),
('14', 'Měkkýši', 'Molluscs', 'a výrobky z nich', 'and products made from them', 'molluscs');

INSERT INTO `raws` (`id`, `nameCzech`, `nameEng`, `tags`, `allergens`) VALUES
(1, 'Vepřová plec bez kosti', 'Pork shoulder, boneless', '2', ''),
(2, 'Olej rostlinný', 'Vegetable oil', '', ''),
(3, 'Cibule loupaná', 'Peeled onion', '', ''),
(4, 'Paprika mletá', 'Paprika powder', '', ''),
.
.
.
(29, 'Majonéza', 'Mayonnaise', '', ''),
.
.
.

In ID 29 I need to have allergens 3,6 and 7. How to do that, if PHPMYAdmin allows me to add only one, although I have datatype SET?

Answer 1

This would be better modelled as a many-to-many relationship.

You would need to add a link table that contains the columns (for example) allergen_id and raw_id. Then you can have multiple records in that table per entry in raws, and attach each record to multiple records in allergens without using the SET datatype at all