0

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     |
|-----------|---------|
2
  • Where are the PRIMARY KEYs??
    – Rick James
    Apr 17, 2019 at 17:23
  • @RickJames An id fields in both tables are primary keys. I forgot to paste the whole SQL dump.
    – Shahin
    Apr 18, 2019 at 9:28

2 Answers 2

2

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.

1
  • Thank you for your answer. It is an old question, however, my current database design is exactly the same as you showed above. It means I properly normalized my database. :) Thanks again!
    – Shahin
    Mar 22, 2020 at 12:24
0

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

0

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge that you have read and understand our privacy policy and code of conduct.

Not the answer you're looking for? Browse other questions tagged or ask your own question.