Mysql – Proper database structure for storing multiple foreign keys

database-designforeign keyMySQL

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     |
|-----------|---------|

Best Answer

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.

Related Question