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

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