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;
- metakey -> is something predefined, like 4 for countries,
- 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 |
|-----------|---------|
PRIMARY KEYs
??id
fields in both tables are primary keys. I forgot to paste the whole SQL dump.