SQL-запрос для объединения таблиц

[zuze]

У меня есть таблица:

 -------------------------------------------
|id | cod1      | cod2          | author |
 -------------------------------------------
|1 |88.1,81.2|771.09,77.1|petrov  |
|2 |88.1,83.2|771.09        |ivanov  |
|...|....           |....               |....        |
|n |....           |....               |....        |
 -------------------------------------------

Но таблица в столбцах cod1 и cod2 не удовлетворяет первой нормальной форме (1НФ).

1НФ - требует, чтобы каждое значение атрибута или столбца было атомарным.

Я это исправил так:

Таблица2:

 ---------------------------
|id | cod1  | author_id |
 ---------------------------
|1 |88.1    |1               |
|2 |81.2    |1               |
|3 |88.1    |2               |
|4 |83.2    |2               |
 ---------------------------

Таблица3:

 ---------------------------
|id | cod2  | author_id |
 ---------------------------
|1 |771.09|1               |
|2 |77.1    |1               |
|3 |771.09|2               |
 ---------------------------

Таблица1:

 --------------
|id | author |
 --------------
|1  |petrov  |
|2  |ivanov  |
 --------------

Тогда SQL-запрос можно сделать так:

Код:

SELECT a.cod1, b.cod2, c.author FROM Таблица2 AS a, Таблица3 AS b, Таблица1 AS c
RIGHT JOIN Таблица2 AS a on a.author_id=c.id
RIGHT JOIN Таблица3 AS b on b.author_id=c.id

А что делать, если у разных авторов может повторятся author_id в таблицах 2 и 3?

Тогда же author_id в таблицах 2 и 3 не будет совпадает с id из таблицы 1.

[Алексей++]

а он не должен повторяться, по идее Если есть возможность - лучше создать ещё колонку с уникальным индексом

в таблице 1 id должен называться autor_id и быть уникальным

[RXL]

zuze, убедительнейшая просьба: выкладывать не картинки с "радикала", а нормальный текст!

id - это что? Имена у таблиц есть или это полная абстракция?

[zuze]

Алексей1153++, сейчас я поясню более подробно, что я имею ввиду. Представьте, что есть исходная таблица, которую нужно нормализовать:

 -------------------------------------------
|id | cod1      | cod2          | author |
 -------------------------------------------
|1 |88.1,81.2|771.09,77.1|petrov  |
|2 |88.1,83.2|771.09        |ivanov  |
|3 |89           |200.01        |sushkin|
|4 |88.1,81.2|200.01        |            |
|...|....           |....               |....        |
|n |....           |....               |....        |
 -------------------------------------------

Как видно из этой таблицы у авторов petrov и vilkin совпадает cod1, а следовательно будет совпадать author_id.
Как видно из этой таблицы у авторов sushkin и vilkin совпадает cod2, а следовательно будет совпадать author_id.

И что же в таком случае делать?

 

[RXL]

zuze, не совсем понятно: ты хочешь вынести коды в отдельную таблицу со своими идентификаторами? А есть ли смысл? Не все и вся нужно нормализовывать. Рассмотри, что у тебя первично в отношениях объектов (повторяю вопрос: что такое id - чей id?).

[zuze]

что такое id - чей id

В Таблице1 id - это уникальный идентификатор автора.
В Таблице2 id - это уникальный идентификатор строки, а author_id - это идентификатор кода (cod1) конкретного автора
В Таблице3 id - это уникальный идентификатор строки, а author_id - это идентификатор кода (cod2) конкретного автора

Может я вообще всё не так делаю и надо было всё, как-то иначе делать?

Не все и вся нужно нормализовывать.

Как можно выяснить нужно или не нужно нормализировать?

[Finch]

zuze, Это ты решаеш сам. Есть примерная область применения твоей базы. И запросы, которые будут применяться к твоей базе. Ты должен определить. Какое построение данных будет наименее затратным. Также стоит обратить внимание на дальнейшее безболезненное увеличение. Так чтоб, на "я хочу" твоего начальника, тебе не пришлось полностью перелапачивать код.

[RXL]

zuze, вариантов несколько:

1. Оставить исходный вид.

2. Вынести коды в отдельную таблицу.

Код:

CREATE TABLE authors
(
  id INT PRIMARY KEY,
  name VARCHAR(100) NOT NULL
);

CREATE TABLE authors_codes
(
  author_id INT NOT NULL,
  code VARCHAR(10) NOT NULL,
  PRIMARY KEY (author_id, code)
);

3. Вынести коды в отдельный справочник и создать таблицу связи с авторами.

Код:

CREATE TABLE authors
(
  id INT PRIMARY KEY,
  name VARCHAR(100) NOT NULL
);

CREATE TABLE codes
(
  id INT PRIMARY KEY,
  code VARCHAR(10) NOT NULL
);

CREATE TABLE authors_codes
(
  author_id INT NOT NULL,
  code_id INT NOT NULL,
  PRIMARY KEY (author_id, code_id)
);

[Dimka]

Если cod1, cod2 обобщить до N, то появится ещё одна таблица.

Author(AuthorID,Name), где Name='petrov','ivanov',...
CodeType(CodeTypeID,Type), где Type=1,2,...
Code(CodeID,CodeTypeID,Value), где Value=88.1,771.09,...
Table(AuthorID,CodeID)

Но если в исходной таблице ID имеет какой-то смысл (например, важно, чтобы все перечисленные в cod1, cod2 полях коды логически относились к одной записи), то в Table добавится и это поле.

Возможно, CodeTypeID относится не к справочнику Code, а к основной таблице Table - это зависит от совпадения или несовпадения доменов у cod1, cod2 и т.д.

[McZim]

Как уже сказали вариантов масса. Единственно что нужно понять, то как будут наполняться таблицы/таблица, то есть какой будет объем данных и как потом с этими таблицами намерен работать? Какие нужны данные по каким условиям?

Кардинальных вариантов два. Оставить все в одной таблице (не так как сейчас) с одним уникальным полем id, или разнести на три таблицы, все остальное это вариации этих методов.

Задача не ясна.

RXL, второй вариант у тебя не верен, не понятно где какой код, я так понял что автору нужно четкое разграничение. Или я ошибаюсь?

[Sla]

например,

|id | author_id | flag|value|
----------------------------------
|1 |1               |cod1|88.1  |
|2 |1               |cod2|771.09|
|3 |1               |cod2|77.09|
если id не уникально.
если требуется уникальность записи
то еще одно поле id записи = id самой первой таблицы

1|1 |88.1|cod1|petrov  |
2|1 |81.2|cod1|petrov  |
3|1 |771.09|cod2|petrov  |
4|1 |77.1|cod2|petrov  |

[McZim]

Sla, если ограничиваться одной таблицей, то id это и будет идентификатор уникально записи

[Sla]

в этом варианте есть определенное преимущество
1|1 |88.1|cod1|petrov  |
2|1 |81.2|cod1|petrov  |
3|1 |771.09|cod2|petrov  |
4|1 |77.1|cod2|petrov  |

его можно "улучшить"

CREATE TABLE authors
(
  id INT PRIMARY KEY,
  name VARCHAR(100) NOT NULL
);

CREATE TABLE codes
(
  id INT PRIMARY KEY,
  code VARCHAR(10) NOT NULL
);

CREATE TABLE values
(
  id INT PRIMARY KEY,
  id_record int,
  id_code int,
  id_authors int,
  value VARCHAR(10)
);

[McZim]

имхо, лишнее это.

[zuze]

объем данных

Десятки тысяч, а может и сотни строк.

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

С ними будет работать скрипт таким образом, если пользователь выбирает конкретную фамилию, то выводится она и все коды относящиеся к этой фамилии.

[McZim]

zuze, а как данные заносятся? Поля могут быть пустыми, если да то какие? Набор фамилий или кодов фиксированный?

[zuze]

Поля могут быть пустыми, если да то какие?

Поля cod1 или cod2 могут быть пустыми, но это редкий случай. 

[McZim]

zuze, но все же могут!

[zuze]

McZim, к сожалению такой вариант не подходит, так как повторяется фамилия несколько раз, это было ничего страшного если бы был только столбец фамилия.

Дело в том, что я специально для примера упростил таблицу, а в реальной таблице ещё куча столбцов и если они все будут повторятся, таблица будет накапливать очень большой объём дублирующей информации.

[Sla]

Не, я так не играю

Тут что главное?
Думаю что вот это
1 |88.1,81.2|771.09,77.1|petrov  |....
т.е. id записи и другие

[McZim]

zuze, я же говорю все зависит от того что нужно в итоге, я не знаю твоей задачи и твоих требований. Если не устраивает так, то разноси, как будешь разносить это тоже дело твое, так как id авторов могут тоже повторяться в таблицу.

[zuze]

Sla, в основной таблице есть столбцы id cod1, cod2, author и куча ещё других столбцов которые относятся к конкретному автору, вот по этому я и не стал их указывать.

Если нужно для полноты картины написать полную таблицу, я с удовольствием это сделаю.

[McZim]

zuze, ты лучше расскажи что ты хочешь сделать в итоге? Какая цель? И почему у тебя в таблице не могут повторяться авторы?

[zuze]

что ты хочешь сделать в итоге? Какая цель?

Разработка информационно-поисковой системы

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

Так как все другие поля столбцов которые я не указал для простоты тоже зависят от автора и их тоже придётся дублировать, а там приличный объём информации.

[McZim]

У тебя так и так будет объем информации одинаков. Например ты разнесешь данные по таблицам, в одной из таблиц у тебя будет имя автора и его id в другой таблице куча записей для этого id. Если для одного автора сопоставима куча информации в разный момент времени, что ты хочешь куда разделить что бы не было дубля. Да и вообще я не вижу дубля, каждая запись уникальна. Если у тебя все же укладываются в БД одинаковые записи, то это уже вопрос не к БД.

[Sla]

например
CREATE TABLE books
(
  id INT PRIMARY KEY,
  author VARCHAR(100) NOT NULL,
  name VARCHAR(100) NOT NULL
);
CREATE TABLE parameters
(
  id INT PRIMARY KEY,
  id_books int,
  name_parameter  VARCHAR(100) NOT NULL,
  value_parametr VARCHAR(100)
)

вижу такое

[zuze]

У меня появилась такая идея, Таблица2 и Таблица3 остаётся без изменений, а Таблица1 имеет вид:

 -------------------------------
|id | cod1 | cod2 | author |
 -------------------------------
|1 |1        |1        |petrov  |
|2 |2        |2        |ivanov  |
|3 |3        |3        |sushkin|
|4 |1        |3        |vilkin    |
|...|....      |....      |....        |
|n |x        |y         |z          |
 -------------------------------

Как Вы считаете это хорошая идея?

[Sla]

не понятно

[Алексей++]

Код:

Таблица2:
 ---------------------------
|id_cod1 | cod1  | author_id |
 ---------------------------
|1 |88.1    |1               |
|2 |81.2    |1               |
|3 |88.1    |2               |
|4 |83.2    |2               |
 ---------------------------

Таблица3:
 ---------------------------
|id_cod2 | cod2  | author_id |
 ---------------------------
|1 |771.09|1               |
|2 |77.1    |1               |
|3 |771.09|2               |
 ---------------------------

Таблица1:

 --------------
|id_author | author |
 --------------
|1  |petrov  |
|2  |ivanov  |
 --------------

[zuze]

Sla, сейчас попробую пояснить более подробно.

Есть исходная таблица:

 -------------------------------------------
|id | cod1      | cod2          | author |
 -------------------------------------------
|1 |88.1,81.2|771.09,77.1|petrov  |
|2 |88.1,83.2|771.09        |ivanov  |
|3 |89           |200.01        |sushkin|
|4 |88.1,81.2|200.01        |vilkin    |
 -------------------------------------------

Таблица2:

 ---------------------------
|id | cod1  | author_id |
 ---------------------------
|1 |88.1    |1               |
|2 |81.2    |1               |
|3 |88.1    |2               |
|4 |83.2    |2               |
|5 |89       |3               |
 ---------------------------

Таблица3:

 ---------------------------
|id | cod2  | author_id |
 ---------------------------
|1 |771.09|1               |
|2 |77.1    |1               |
|3 |771.09|2               |
|4 |200.01|3               |
 ---------------------------

Таблица1:

 -------------------------------
|id | cod1 | cod2 | author |
 -------------------------------
|1 |1        |1        |petrov  |
|2 |2        |2        |ivanov  |
|3 |3        |3        |sushkin|
|4 |1        |3        |vilkin    |
 -------------------------------

Надеюсь так стала понятно. Как Вы считаете это хорошая идея?

[zuze]

Алексей1153++, не понял что ты своим постом #28 хочешь сказать?

[McZim]

zuze, а теперь наполни эти таблицы несколькими сотнями тысяч и посмотри план выполнения

[Sla]

т.е. cod's заранее известные значения?
Все равно не понятно. У тебя cod's может принимать несколько значений.

1. Таблица авторов
2. Таблица кодов
3. Таблица соответствий кодов таблице авторав

[zuze]

а теперь наполни эти таблицы несколькими сотнями тысяч и посмотри план выполнения

Вы хотите сказать, что будут жуткие торможения.  

[zuze]

Sla, cod1 и cod2 это совершенно разные коды. cod1 может принимать одно или несколько значений и cod2 может принимать одно или несколько значений. Также cod1 или cod2 может быть пустым, но это крайне редко. Все значения в полях столбца cod1 и cod2 известны заранее.

[McZim]

zuze, ты посмотри сначала на план, на стоимость, если тебя устроит то ради бога

Кстате, можно сделать так по тому примеру что я приводил.

[Sla]

zuze, я поясню.
Тебе понадобится добавить еще, например, cod3
Будешь делать alter table?

Проще создать таблицу cod3, а таблицы cod2 и  cod1 уже созданы, и привязать эту таблицу к таблице авторов, через таблицу параметров.

Если понадобится, то написать view, удовлетворяющую твоим требованиям.

[Алексей++]

zuze, я предложил осмысленные названия к id_...

[zuze]

1. Таблица авторов
2. Таблица кодов
3. Таблица соответствий кодов таблице авторав

Я же такое писал в посте #29, но вот твои пункты 1 и 3 там совмещены в одну таблицу. Я так понял это не правильно, так как это будет медленно работать и надо их разделить. Я правильно тебе понял? И таблиц кодов у меня ни одна, а две.

[Sla]

мне все равно сколько у тебя таблиц кодов

Основная таблица
id
name

таблица ПАРАМЕТРЫ
название параметра
значение параметра
id записи которой принадлежит этот параметр.

Две таблицы, крути как хочешь

[Dimka]

Ужас какой-то!

Всем предлагаю в качестве наряда вне очереди дать определения нормальных форм вплоть до 3УНФ, объяснить, зачем и в каких случаях делается переход от формы к форме, и представить декомпозицию исходной таблицы по каждой нормальной форме.

[McZim]

Dimka, н всегда нужны нормальные формы

[Sla]

У меня есть таблица:

 -------------------------------------------
|id | cod1      | cod2          | author |
 -------------------------------------------
|1 |88.1,81.2|771.09,77.1|petrov  |
|2 |88.1,83.2|771.09        |ivanov  |
|...|....           |....               |....        |
|n |....           |....               |....        |
 -------------------------------------------

Но таблица в столбцах cod1 и cod2 не удовлетворяет первой нормальной форме (1НФ).

1НФ - требует, чтобы каждое значение атрибута или столбца было атомарным.

Я это исправил так:

Таблица2:

 ---------------------------
|id | cod1  | author_id |
 ---------------------------
|1 |88.1    |1               |
|2 |81.2    |1               |
|3 |88.1    |2               |
|4 |83.2    |2               |
 ---------------------------

Таблица3:

 ---------------------------
|id | cod2  | author_id |
 ---------------------------
|1 |771.09|1               |
|2 |77.1    |1               |
|3 |771.09|2               |
 ---------------------------

Таблица1:

 --------------
|id | author |
 --------------
|1  |petrov  |
|2  |ivanov  |
 --------------

Тогда SQL-запрос можно сделать так:

Код:

SELECT a.cod1, b.cod2, c.author FROM Таблица2 AS a, Таблица3 AS b, Таблица1 AS c
RIGHT JOIN Таблица2 AS a on a.author_id=c.id
RIGHT JOIN Таблица3 AS b on b.author_id=c.id

А что делать, если у разных авторов может повторятся author_id в таблицах 2 и 3?

Тогда же author_id в таблицах 2 и 3 не будет совпадает с id из таблицы 1.

По требованию Dimka прочитано , в том числе и первый пост

А что делать, если у разных авторов может повторяться author_id в таблицах 2 и 3?
zuze, А как ты это представляешь?

[zuze]

А что делать, если у разных авторов может повторяться author_id в таблицах 2 и 3?
zuze, А как ты это представляешь?

Я это писал в посте #3, но напишу ещё раз  

-------------------------------------------
|id | cod1      | cod2          | author |
 -------------------------------------------
|1 |88.1,81.2|771.09,77.1|petrov  |
|2 |88.1,83.2|771.09        |ivanov  |
|3 |89           |200.01        |sushkin|
|4 |88.1,81.2|200.01        |vilkin    |
 -------------------------------------------

А вот и повторения о которых Вы спрашивали.
Как видно из этой таблицы у авторов petrov и vilkin совпадает cod1, а следовательно будет совпадать author_id.
Как видно из этой таблицы у авторов sushkin и vilkin совпадает cod2, а следовательно будет совпадать author_id.

И что же в таком случае делать?

Может можно обойтись без переделки таблиц, а добавить какие-то дополнительные условия в SQL-запрос

Код:

SELECT a.cod1, b.cod2, c.author FROM Таблица2 AS a, Таблица3 AS b, Таблица1 AS c
RIGHT JOIN Таблица2 AS a on a.author_id=c.id
RIGHT JOIN Таблица3 AS b on b.author_id=c.id

[Sla]

тогда скажи что такое author_id?

[zuze]

тогда скажи что такое author_id?

author_id - это идентификатор кода (cod1 и cod2) конкретного автора.

И из-за того что author_id из Таблицы2 или Таблицы3 не всегда равен id Таблицы1 у меня возник вопрос, то ли SQL-запрос надо модифицировать, то ли таблицы иначе составлять.

[RXL]

zuze, ужас!

По всем понятиям author_id — это идентификатор автора. Не кода!!!
А потом уже к автору привязываются коды.

[zuze]

По всем понятиям author_id — это идентификатор автора. Не кода!!!

Если уж такая путаница с этими идентификаторами давайте их перепишем с нормальными названиями, что бы путаниц не было.

Таблица2:

 -----------------------------------
|id_cod1 | cod1  | num_cod1 |
 ----------------------------------- |
|1           |88.1    |1                |
|2           |81.2    |1                |
|3           |88.1    |2                |
|4           |83.2    |2                |
 -----------------------------------

Таблица3:

 -----------------------------------
|id_cod2 | cod2  | num_cod2 |
 ----------------------------------- |
|1           |771.09|1                |
|2           |77.1    |1                |
|3           |771.09|2                |
 -----------------------------------

Таблица1:

 ------------------------
|id_author | author |
 ------------------------
|1              |petrov   |
|2              |ivanov   |
 ------------------------- 

Вот теперь author_id, обозначает, то как написал RXL.

[RXL]

И теперь видно, что связь между таблицами отсутствует - требуется еще одна или две таблицы для связей. А num_cod1 и num_cod2 вообще не понятно для чего...


Вопрос: а какая СУБД используется?

[zuze]

Вопрос: а какая СУБД используется?

mysql

Если у меня всё так косо и криво я попробую привести ещё раз начальную таблицу и поясню её более подробно.

 -------------------------------------------
|id | cod1      | cod2          | author |
 -------------------------------------------
|1 |88.1,81.2|771.09,77.1|petrov  |
|2 |88.1,83.2|771.09        |ivanov  |
|3 |89           |200.01        |sushkin|
|4 |88.1,81.2|200.01        |vilkin    |
|...|....           |....               |....        |
|n |x,y           |u,v              |z          |
 -------------------------------------------

Подробности:
1. Я хочу эту таблицу нормализовать.
2. После столбца "author", есть ещё куча столбцов. Я их убрал для простоты примера, но каждое значение столбца связанно с другим значением другого столбца относящегося к конкретному автору (Например, есть столбец "Полное название учебного заведения").
3. Значения в столбцах cod1, cod2 и author известны и вбиваются оператором, значения в столбце id генерируется СУБД.
4. В столбцах cod1 или cod2 значение относящаяся к конкретному автору может быть пустое.
5. Значения столбцов cod1 и cod2 у разных авторов может совпадать.

[Dimka]

Dimka, н всегда нужны нормальные формы

Все наши беды от пуговицы. Специально повторяю ключевую фразу:

ОБЪЯСНИТЬ, ЗАЧЕМ И В КАКИХ СЛУЧАЯХ ДЕЛАЕТСЯ ПЕРЕХОД ОТ ФОРМЫ К ФОРМЕ

[zuze]

ОБЪЯСНИТЬ, ЗАЧЕМ И В КАКИХ СЛУЧАЯХ ДЕЛАЕТСЯ ПЕРЕХОД ОТ ФОРМЫ К ФОРМЕ

Я к сожалению знаю, только такое, что нормализация нужна, что бы избежать проблем при увеличении таблиц в будущим.
А будут ли увеличиваться таблицы или нет в будущем это обычно заранее не известно.

Если в моём случае можно не нормализировать таблицу, то могли бы Вы объяснить подробно почему, а если есть про все формы нормализации такая информация, я только буду рад её почитать.

К сожалению в моих книгах кроме определения нормализации и их форм с примерами, не даны обоснования когда нужно нормализировать, а когда нет.

[Oldy]

Разбейте по сущностям:
Таблица авторов, таблица для cod1, для cod2 и сводная, куда заносятся id из каждой вышеназванной. Или я что-то не понял?

[Dimka]

К сожалению в моих книгах кроме определения нормализации и их форм с примерами, не даны обоснования когда нужно нормализировать, а когда нет.

Так прямо нигде и написано про аномалии удаления, добавления, изменения?

[zuze]

Так прямо нигде и написано про аномалии удаления, добавления, изменения?

У меня всего три книги:
1. Бен Форта. Освой самостоятельно SQL. 10 минут на урок. 3-е издание
В этой книге описаны только примеры операторов и некоторые различия языка SQL для разных СУБД.

2. Люк Веллинг, Лора Томсон. Разработка Web-приложений с помощью PHP и MySql. 2-е издание.
В этой книге описаны только стандартные таблицы и использование индексов.

3. Люк Веллинг, Лора Томсон. MySql учебное пособие.
В этой книге они есть. Сейчас буду читать и вникать.

[RXL]

zuze, у нас есть несколько статей по нормализации - думаю. что это будет лучше, чем "10 минут на урок".

https://club.shelek.ru/viewart.php?id=177
https://club.shelek.ru/viewart.php?id=311

Еще статьи здесь: https://club.shelek.ru/view.php?id=15