Proper database structure for storing multiple foreign keys

Question

I am using MySQL 5.7.25 and need to store multiple foreign keys for multiple columns.

Scheme of my table:

CREATE TABLE `tours` (
  `id` int(10) UNSIGNED NOT NULL,
  `countries` text NOT NULL,
  `themes` text NOT NULL,
  `code` char(5) NOT NULL,
  `type` tinyint(3) UNSIGNED NOT NULL,
  `difficulty` int(10) UNSIGNED NOT NULL,
  `paceLevel` int(10) UNSIGNED NOT NULL,
  `status` tinyint(4) NOT NULL
) ENGINE=InnoDB DEFAULT CHARSET=utf8;

The countries column should store country IDs like: 10,12,14. Storing data that way forces me to use LIKE query which I have to avoid for better performance. How you would design this kind of tables?

UPDATE:

I created another table to store meta table of tours:

CREATE TABLE `tour_meta` (
  `id` int(10) UNSIGNED NOT NULL,
  `metakey` int(10) UNSIGNED NOT NULL,
  `metaval` int(10) UNSIGNED NOT NULL,
  `tour_id` int(10) UNSIGNED NOT NULL,
  `status` int(10) UNSIGNED NOT NULL
) ENGINE=InnoDB DEFAULT CHARSET=utf8;

1. metakey -> is something predefined, like 4 for countries,
2. metaval -> is real data id, for example country id

But it will be useless when I need to filter the data like:

SELECT ... WHERE COUNTRY = 'Brazil' AND THEME = 'Discovery'

Brazil ID = 5, Discovery ID = 3

SELECT * 
FROM tour_meta 
WHERE metakey = 4 -- (predefined meta key for country data) 
  AND metaval = 5 -- (country ID) 
  AND metakey = 5 -- (predefined meta key for theme data) 
  AND metaval = 3 -- (theme ID)

UPDATE 2:

@Akina helped me a lot and the following query solved my problem:

SELECT tour_id, COUNT(tour_id) as counted from metas WHERE (metakey, metaval) IN ((3, 5), (4, 6)) group by tour_id having counted = 2

But now I am very confused about how to count different meta keys.

Hypothetically, let's say we have the following resultset:

|--------|-------------|
|tour_id |  countries  |
|--------|-------------|
|   1    | 15, 18, 23  |
|--------|-------------|
|   2    | 19, 23, 25  |
|--------|-------------|

How can I get the following resultset?

|-----------|---------|
|country_id |  count  |
|-----------|---------|
|    15     |   1     |
|-----------|---------|
|    18     |   1     |
|-----------|---------|
|    19     |   1     |
|-----------|---------|
|    23     |   2     |
|-----------|---------|
|    25     |   1     |
|-----------|---------|

Answer 1

The countries column should store country IDs like: 10,12,14.

This is a poor design, which you've already realised, (through your dislike of "LIKE").

Normalise your data and split these repeated values out into separate tables.

select * from tours ; 
+------+ 
| id   | ... 
+------+ 
| 1234 | ... 
+------+ 

select * from countries ; 
+----+--------+
| id | name   |
+----+--------+
| 10 | Brazil | 
| 12 | Peru   | 
| 14 | Chile  | 
+----+--------+

select * from tour_countries ; 
+---------+------------+ 
| tour_id | country_id | 
+---------+------------+ 
|    1234 |         10 | 
|    1234 |         12 | 
|    1234 |         14 | 
+---------+------------+ 

select * from themes ; 
+-----+-----------+
| id  | name      |
+-----+-----------+
| 111 | Discovery | 
+-----+-----------+

select * from tour_themes ; 
+---------+----------+
| tour_id | theme_id | 
+---------+----------+
|    1234 |      111 |
+---------+----------+

Finding data?

SELECT ... WHERE COUNTRY = 'Brazil' AND THEME = 'Discovery'

select t.* 
from       tours          t 
inner join tour_countries tc  on  t.id = tc.tour_id 
inner join countries      c   on  tc.country_id = c.id 
inner join tour_themes    tt  on  t.id = tt.tour_id 
inner join themes         t   on  tt.theme_id = t.id 
where c.name in ( 'Brazil' )
and   t.name in ( 'Discovery' )
order by t.id ; 

Regards, Phill W.

Answer 2

You must use WHERE (metakey, metaval) IN ( (4, 5), (5, 3) ). (4, 5) value combination means "country data (4)" = "country_id = 5", and (5, 3) means "theme data (5)" = "theme_id = 3".

This allows to select tour_id values matched at least one of this conditions. But then you must group by tour_id, count distinct (metakey, metaval) pairs for each tour, and filter only those tours which shows this count equal to 2 (condition pairs count).

Obtaining tour_id you can obtain whole tour record from tours table. - Akina

If you don't have at least version 5.7.3, don't use "row constructors"; they are grossly inefficient. – Rick James