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

Question

Я испытываю свои первые тесты MySQL INSERT, и я использую эти таблицы:

table employees
-> employee_id
-> employee_name
-> employee_surname
-> employee_url

table areas
-> area_id
-> area_city
-> area_address
-> area_country

table agencies
-> agency_id
-> agency_name
-> agency_url

table node_employees
-> node_id
-> employee_id
-> area_id
-> agency_id

Я хотел бы хранить данные в table_employee, table_areas и table_agency, но я не обязан сохранять все данные одновременно, чтобы я мог создать сотрудника, а затем агентство или адрес.

В случае единичной вставки данных, должен ли я использовать что-то подобное или просто использовать таблицу node_employees, если да, как я могу это сделать?

INSERT INTO employees (employee_name, employee_surname, employee_url)
VALUES ('Roger', 'Waters', 'http://pinkfloyd.com')

INSERT INTO agencies (agency_name, agency_url)
VALUES ('Google', 'http://google.com')

INSERT INTO areas (area_city, area_address, area_country)
VALUES ('Rome', 'Via Roma, 123', 'Italy')

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

SELECT employee_id FROM employees WHERE employee_name = 'Roger'
SELECT agency_id FROM agencies WHERE agency_name = 'Google'

// I'll get their ids in php
$php_employee_id
$php_agency_id

// and then
INSERT INTO node_employees (employee_id, agency_id)
VALUES ('$php_employee_id', '$php_agency_id')

У меня также есть другое сомнение, что я должен делать, если мне нужно связать сотрудника с областью? Должен ли я использовать другой запрос, так что запрос для каждой возможности?

// so, not this
$php_employee_id = 12;
$php_agency_id = 7;
$php_area_id = null;
INSERT INTO node_employees (employee_id, agency_id, area_id)
VALUES ('$php_employee_id', '$php_agency_id', '$php_area_id') // will this remove the previous data with null in area_id?

Answer 1

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

Это должны быть две таблицы - одна для привязки сотрудников к агентствам, а другая - для привязки сотрудников к областям. Вы не упомянули ничего, касающегося агентств с областями, создавая впечатление, что они независимы друг от друга...

Независимо от node_employees.employee_id должен быть внешним ключом к EMPLOYEES.employee_id, чтобы гарантировать, что сотрудник уже должен существовать как действительное значение в системе. Аналогично для agency_id между node_employees а также agencies столы.

Из-за этих отношений ценности должны существовать в EMPLOYEES а также AGENCIES таблицы, прежде чем они существуют в NODE_EMPLOYEES Таблица. Это делает NODE_EMPLOYEES таблица в "дочерних" отношениях с двумя другими таблицами, так что ваши три оператора INSERT должны будут вставляться в родителей перед ребенком и использовать значения из родителей в ребенке.

Answer 2

Я предполагаю, что агентства и области - это отношения один ко многим. другими словами, каждый сотрудник может быть назначен нескольким агентствам и областям. Если нет, то я просто добавил бы поля agency_id и area_id в таблицу сотрудников и даже не создал бы таблицу node_employees.

Учитывая вышесказанное...

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

Затем при вставке сотрудника используйте mysql_insert_id(), чтобы вернуть идентификатор только что созданной записи. (Я предполагаю, что employee_id - это поле с автоматическим приращением)

Тогда делай, как ты сказал

SELECT agency_id FROM agencies WHERE agency_name = 'Google'

// I'll get their ids in php
$php_employee_id <== from mysql_insert_id() after inserting employee record
$php_agency_id <== from above agency Select

// and then
INSERT INTO node_employees (employee_id, agency_id)
VALUES ('$php_employee_id', '$php_agency_id')

для вашего последнего запроса

Инструкция INSERT добавляет новую запись, она не заменит существующую запись. если существующая запись имеет уникальный индекс, такой как employee_id, она завершится ошибкой. Вы должны использовать UPDATE, чтобы сделать это, ИЛИ оператор ЗАМЕНИТЬ, который ОБНОВЛЯЕТ запись, если она существует, или ВСТАВИТЬ, если запись не существует. убедитесь, что вы читаете его, потому что он работает через уникальные индексы.

ОКРУГ КОЛУМБИЯ