Как правильно связывать таблицы в данном случае?

Question

[Muranx]

Здравствуйте!

Есть таблица с машинами :

+----+-----------+-----------+-----------------+--------------+
| id | car_firm  | car_model | year_of_produce | car_color_id |
+----+-----------+-----------+-----------------+--------------+
|  1 | Toyota    | Sprinter  |            1994 |            1 |
|  2 | Nissan    | Patrol    |            2001 |            1 |
|  3 | Toyota    | Harrier   |            2004 |            2 |
|  4 | Toyota    | Camry     |            1999 |            1 |
|  5 | Toyota    | Camry     |            2010 |            3 |
|  6 | Mazda     | Demio     |            2005 |            1 |
|  7 | Toyota    | Sprinter  |            1994 |            1 |
|  8 | Nissan    | Note      |            2003 |            2 |
|  9 | Nissan    | Teana     |            2003 |            3 |
| 10 | Nissan    | Teana     |            2014 |            1 |
| 11 | Honda     | Accord    |            2000 |            1 |
| 12 | Honda     | Fit       |            2000 |            1 |
| 13 | Honda     | Fit       |            2014 |            1 |
| 14 | Nissan    | Fuga      |            2005 |            3 |
| 15 | Nissan    | Fuga      |            2017 |            1 |
| 16 | Toyota    | Vanguard  |            2004 |            2 |
| 17 | Nissan    | Juke      |            2005 |            3 |
| 18 | Mitsubisi | Pajero    |            1999 |            3 |
| 19 | Mitsubisi | Outlander |            2005 |            3 |
| 20 | Mitsubisi | Outlander |            2019 |            1 |
| 21 | Toyota    | MarkX     |            2016 |            1 |
+----+-----------+-----------+-----------------+--------------+

Чисто ради эксперимента решил вынести поле car_color_id в отдельную таблицу, в виде :

+----------+------------+
| color_id | color_name |
+----------+------------+
|        3 | blue       |
|        2 | green      |
|        1 | red        |
+----------+------------+

т.е. соотвественно есть айди цвета, ну и из основной таблицы мы можем по этому айди найти нужный нам цвет ( вроде не сложно )! Запрос выглядит так :

SELECT `car_firm`, `car_model` 
FROM `cars_info` 
WHERE `car_color_id` = (SELECT `color_id` FROM `car_colors_info`  WHERE `color_name` = 'blue' );

В целом запрос работает как надо, НО пока изучаю скл везде приводятся примеры таблиц связанных с помощью PRIMARY KEY , что в данном случае мне представляется возможным лишь если сделать id из основной таблицы вторичным ключём второй таблицы (ну или как там переводится FOREIGN KEY)! НО тогда вторая (таблица потомок с цветами) будет иметь размерность таблицы родителя, что сообственно разумеется замедлит запросы и выборку данных (ну наверное), НО зато таблицы будут связаны, так вот в чем вопрос , то что я сделал (в примере выше) имеет место в этом мире? Нужно ли стараться связывать такие таблицы каким-то образом? Или вполне можно чтобы в одной схеме присутствовали две таблицы связаны не "жёзтко" а чисто логически друг с другом? Или есть способ в данном случае связать этот id из первой таблицы с color_id из второй таблицы, при этом не делая из второй таблицы здорового монстра, а лишь оставить по одному соотвествию color_id и color_name?

Answer 1

[ThunderCat]

Чисто ради эксперимента решил вынести поле car_color_id в отдельную таблицу,

А надо делать это во первых на постоянной основе, а во вторых и для других справочных полей сделать то же самое.

Запрос выглядит так : ... бред поскипан...

Запрос должен быть с джоином, со связью через первичный ключ таблицы car_colors_info и соответственно car_color_id.

т.е. соотвественно есть айди цвета, ну и из основной таблицы мы можем по этому айди найти нужный нам цвет

На деле же вы почему то ищете по имени цвета - айди цвета, и по нему уже синие машины...

Comments

[Muranx]

ThunderCat,

Запрос должен быть с джоином, со связью через первичный ключ таблицы car_colors_info и соответственно car_color_id.

Я так понял что ты имел ввиду что-то вроде :

SELECT `car_firm`, `car_model` 
FROM `cars_info` JOIN `car_colors_info` 
ON `color_id` = `car_color_id` 
WHERE `color_name` = 'red';

?
То есть не обязательно в схеме связывать таблицы, чтобы между ними были стрелочки указывающие на отношения PK к FK? Ведь я могу как мы видим целыми двумя способами связать эти таблицы... Или это всё же не правильно? И должно быть жёзткое отношение между таблицами по PK и FK?

[ThunderCat]

Muranx,

SELECT `car_firm`, `car_model` 
FROM `cars_info` JOIN `car_colors_info` 
ON `color_id` = `car_color_id` 
WHERE `color_name` = 'red';

Не очень понимаю зачем ты выбираешь машины по имени цвета, когда у тебя скорее всего все сущности будут использовать его индекс (и вообще для чего тебе такая выборка???), и для таких выборок обычно еще используются алиасы таблиц или их префиксы (названия).

SELECT `ci`.`car_firm`, `ci`.`car_model`, `cci`.`color_name`
FROM `cars_info` ci
JOIN `car_colors_info` cci
ON `ci`.`color_id` = `cci`.`car_color_id` 
WHERE `color_id` = 3

Для выбора всех машин достаточно убрать условие where

То есть не обязательно в схеме связывать таблицы, чтобы между ними были стрелочки указывающие на отношения PK к FK?

Не обязательно, но крайне желательно, кроме того еще и поведение желательно указать, так как если удалится какой-то цвет, то потеряется связанность данных, и все машины с индексом удаленного цвета не будут выбираться вообще, или цвет будет нулл, в зависимости от типа джоина. Настроить можно на запрет удаления связных данных, или на их каскадное удаление.