Есть следующие таблицы:
Клиенты: ClientId | Name
Товары: CommId
Заказы: OrderId | ClientId
Заказанные товары: OrderCommId | OrderId | ClientId | CommId
Выданные товары: IssueCommId | OrderCommId | OrderId | ClientId | CommId

---------------------------------

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

Вариант 1
Так и дублировать столбцы в каждой таблице.

Плюсы:
1) Достаточно одного SELECT'а без использования JOIN'ов - вся нужная информация выбирается сразу одним запросом

Вариант 2
Использовать JOIN'ы.
Но в этом случае, чтобы из выданных товаров получить имя клиента, над сделать несколько JOIN'ов...

1. Сначала выбрать выданные товары;
2. Затем найти соответствующие им заказанные товары (по столбцу OrderCommId), чтобы
3. Найти заказ, в котором есть эти товары (OrderId) и уже только потом
4. Выбрать пользователя ClientId и узнать его имя.


Плюсы:
1) Меньше столбцов - меньше размер БД
2) Проще изменять данные, т.к. они не дублируются в других таблицах

---------------------------------

Если ещё учитывать того человека, который выдал товар, плюс ещё склад, откуда выдан товар и Id того товара на складе, то будет ещё +3 столбца в таблицы...
Получается,
- с одной стороны, будет много дубликатов данных в разных таблицах;
- с другой стороны, будет много JOIN'ов.

---------------------------------

Хотелось бы использовать JOIN'ы, но при 1 000 000 записей в каждой таблице не будет ли это медленней по сравнению с дублированием столбцов?

Это сообщение отредактировал(а) fridkaratel - 25.11.2012, 12:10

Какие вам данные сложно изменять при первом подходе?
Заказ на этапе выдачи товара может вдруг внезапно поменять клиента?

Вобще обычно заказы это заказы, а расходные накладные это расходные накладные. Когда речь идет о документах, на всех этапах жизни документа, он должен печататься одинаково, не зависимо о того как поменялось окружение, справочники. Вплоть до того, что если по товару с id=100500 на этапе оформления и утвреждения значились "апельсины", а потом кто-то заменил дескрипшн для этого товара  на "магнитолы", документ, будучи распечатанным повторно, не должен при этом измениться.

@Zloxa:


Разрабатываю не для себя, и не для фирмы... а для группы людей...
Например, такой (выдуманный) случай:
1. Клиент позвонил по телефону и заказал яблоки.
2. Менеджер набил в заказ артикул яблок и зарезервировал их на складе.
3. Клиент пришёл получать яблоки, смотрит на них и говорит - я не эти красные хотел, я хотел те красные...
4. В итоге, надо в выдаче изменить одни яблоки на другие.

Или вообще убрать яблоки из выдачи.
Грузчик нёс яблоки клиенту и уронил их.
Всё, нет яблок...
А фактура на выдачу товара есть...
И в базе выдача этих яблок есть.
Надо удалить яблоки из выдачи.

Пример с яблоками привёл для наглядности.

----------

То есть, Zloxa, рекомендуешь использовать 1-й вариант?

То есть, по вашему, выдача записывается в момент заказа? И где тут логика? У вас 2 дублирующееся таблицы вышли - заказ и выдача.
Если заказ и товары записываются в момент заказа, а выдача уже после самой выдачи, то никаких конфликтов в вашем случае не видно.

Если частичная выдача товара не предусмотрена, то сделайте на подобии этого:

Где status - это одно из четырех значений, эквивалентных "выполнен", "не выполнен", "отменен заказчиком", "отменен продавцом".
А заказанные товары, в данном случае, изменению не подлежат. Если покупатель желает изменить заказ, если тот еще не принят к выполнению, он должен отменить старый и создать новый.
Если есть склад, то учет товаров должен вестись отдельно, с указанием доступного количества товара. Чтобы потом несуществующие яблоки не заказывали.

И ну и что, что на основе товара узнать клиента надо через 3 таблицы. Вам надо оформлять более 100000 заказов в секунду? Если нет, то JOIN никаких последствий не принесет.

Добавлено @ 03:30Никогда так не делаете ни в коем случае ни для чего, кроме уникального идентификатора... Не хочу преувеличить, но при использовании такой мощной структуры как БД, это противоречит логике.
ID для того и нужен, чтобы логически связывать между собой данные в таблицах.

Это сообщение отредактировал(а) Arantir - 26.11.2012, 03:48

Есть интересное правило построения структуры БД на примере графов. Если знаете теорию графов хоть немного, могу привести пример.

Это сообщение отредактировал(а) Arantir - 26.11.2012, 04:14

@Arantir, спасибо за развёрнутый ответ ;)

Возьмём не заказ и выдачу, а заказ и резерв...

Клиент заказал товар, менеджер закинул его в резерв.
Клиент изменил свой заказ - резерв автоматически изменился (если количество стало меньше, чем было раньше).
То есть, клиент может сколь угодно менять заказ до тех пор, пока он не получит его.

--------------------

По теории графов...
Не знаю, но приведи, пожалуйста, пример...
Почитаю на досуге, изучу... ;)

--------------------

Что касается "недублирования" столбцов...
Получается, выборка через 3-4-5 JOIN'ов, чтобы докопаться до "истины" не будет существенно медленней, чем выборка одной строки?
Более 100000 заказов оформлять не надо, но вот просматривать-то надо...
И даже при просмотре 10-20-30 заказов надо дёргать несколько таблиц чере JOIN.
Вот это меня как бы "пугает"... 

Например...
Требуется посмотреть, кому был выдан товар с артикулом таким-то...

Фактически, в этом случае (без дублирования данных) получится, что надо:
1. Сначала выбрать из заказанных товаров этот артикул
2. Затем через JOIN проверить, есть ли он в выданных
3. Затем через JOIN выбрать заказ, где хранится Id клиента
4. Затем из таблицы клиентов через JOIN выбрать его имя...

Получается, 3 JOIN'а.

В случае же дублирования достаточно только одного JOIN'а с таблицей `client`.

--------------------

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

Это сообщение отредактировал(а) fridkaratel - 26.11.2012, 04:17

Для вариантов с JOIN и без него разница в одну секунду начинает проявляться при выборке нескольких тысяч строк за один раз. Вам в данном случае надо сделать всего лишь выбор одной строки из каждой таблицы: заказа из заказанных товаров, клиента из заказов и имя из клиентов. Ну будет с JOIN там 2 миллисекунды, а без него - 1 миллисекунда... "Не экономьте на спичках" =)

На счет построения таблиц:
Постройте графы связей между полями данных.
Например, "ClientID --> Name" означает, что каждый клиент указывает (имеет) имя. Для заказа такой граф будет таким "OrderID --> ClientID", каждый заказ имеет клиента. 
Стрелочки расставляем по правилу: стрелочка ведет из уникального поля в не уникальное. Если оба поля уникальные, то стоит задуматься о целесообразности связи между ними.



Теперь совместим все графы:
Видите, какой ужас получился. 

Удалим все лишние ребра, оставив связь между всеми вершинами, пользуясь правилами:
1. На уникальные поля не должно указывать больше одной стрелочки. 
2. Должна быть одна и только одна вершина, у которой все ребра исходящие.
Такой граф называется деревом. Именно приведение графа к виду дерева было нашей целью.


Теперь есть правило: если у вершины только одно ребро и оно ведет ИЗ нее, то его направление можно сменить, если это не нарушает правило 2 предыдущего пункта. Пользоваться правилом или нет - зависит от логики связей между этими вершинами. Можно было и оставить, но попробуем уменьшить количество таблиц:


Вот, в итоге у нас получился красивый граф с однозначными связями.

Как по нему строить таблицы? Очень просто:
1. Для любой вершины, ИЗ которой выходит ребро, нужно создать таблицу со всеми вершинами (полями), в которые она непосредственно ведет (путь равен одному ребру).
2. По желанию можно создать таблицы для тех вершин, что имеют только входящие ребра.

Итого 3 таблицы:
1. Клиенты: Номер Клиента, Имя
2. Заказы: Номер Заказа, Номер Клиента
3. Заказанные товары: Номер Заказанного Товара, Номер Заказа, Номер Товара, Выдача
В данном случае ассортимент товаров ограничен, по-этому добавим:
4. Товары: Номер Товара, Название # а можно только с названием, без номера. но вдруг разные апельсины попадутся =)

Ну и пример некоторых возможных данных в этих таблицах:
-- Товары:
1, Яблоки
2, Апельсины
3, Груши

-- Клиенты:
1, Степан
2, Андрей
256, Петр

-- Заказы:
134, 2
167, 2
345, 256

-- Заказанные товары: 
543, 134, 1, true # выдано
587, 134, 2, true 
645, 167, 2, true 
758, 345, 3, false # не выдано

Все однозначно, без повторений, без пересечений и по любому полю можно узнать все поля, связанные с ним. Товары клиента; имя заказчика; не выданные заказы; заказчиков, не получивших товар; товары, которые когда-либо не были выданы.... По-моему, идеальная структура для именно этого частного случая.

Это сообщение отредактировал(а) Arantir - 26.11.2012, 06:19

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

Мне в твоей схеме не нравится что ты объединил документы заказа и отгрузки. Обосновать это не могу. Озвученной ТС версии задачи это не противоречит, я видел системы где так поступают.... но все равно не нравится. Обычно у заказа и отгрузки свои циклы жизни. Да, отгрузку инициирует заказ, и закрытие отгрузки инициирует закрытие заказа, но на один заказ вполне может быть несколько отгрузок. Заказ изменяет статус(структуру) остатка, а отгрузка списыват остаток. Заказ может быть оформлен на более абстрактом уровне товара, отгузка всегда ведется на конкретном уровне, а если учет партионный, то на уровне партии а не товара.  

В общем не знаю... Думаю, если копнуть задачу поглубже, разделять эти сущности таки придется. 

Zloxa, ну что за данные должны были содержаться в "IssueCommId" так и осталось загадкой. А остальные 4 колонки - точная копия таблицы заказов. Выданы могут быть только заказанные, значит там просто дублируется четверка связанных значений из заказов. На первый взгляд выгоднее добавить все в одну таблицу.

Я схему привел как раз для того, чтобы когда ТС улучшит организацию своих данных саму по себе, то ему было проще организовать под нее БД. По-моему, сейчас его система не очень информативная =) По-любому потом какие-то новые поля придется добавить.

"Отгрузка" звучит немного по другому, чем "выдача" =) Выдача со склада доставщику? Выдача покупателю доставщиком? От этого и зависит структура таблиц.

Конечно же, для учета заказов и учета взятия товара со склада надо иметь 2 параллельных таблицы. Но тут не очень понятно было с этим =) Как минимум, значения там не должны бы были дублироваться. 

Это сообщение отредактировал(а) Arantir - 26.11.2012, 10:20

@Arantir:

Мне действительно надо только одну строку выбрать.
А не будет JOIN по всей таблице проходиться, пытаясь найти все доступные значения?
Или установка UNIQUE индекса решит этот вопрос?

Большое спасибо за такой развёрнутый ответ по графам! ;-)
Не знал о таком способе построения.

@Zloxa, @Arantir:
По поводу выдач товаров, а также статуса "Выдано/Не выдано"...
Товар может быть выдан за два, за три раза...

Пример...
Клиент заказал... ну 10 айпадов 
В наличии есть 3 штуки - он забрал их.
Следующая поставка через 5 дней.
Но таможня разрешает за раз ввести только 6 айпадов.
Получается, во второй выдаче он получит ещё 6 - и останется один в долге.
В итоге, 10 айпадов клиент получит за 3 выдачи.

Поэтому нужна доп. таблица, связанная с заказанными товарами через столбец OrderCommId.
А столбец IssueCommId, как я понял, мне уже не нужен, так как я узнал, что его можно заменить составным UNIQUE индексом.
(из созданной мной темы Использование CHAR в качестве PRIMARY, Стоит или не стоит?)

Это сообщение отредактировал(а) fridkaratel - 27.11.2012, 06:47

В дополнение решил ещё кое-что написать...

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

------------------------------------------------

Описание таблиц




------------------------------------------------

Доп. примечания

По таблице "client_commodity_issue"
Товар может быть выдан не за один раз, а за два-три-четыре...
То есть, нельзя столбец IssueQty создать в таблице "client_commodity_booking".
Для учёта выданного товара должна быть отдельная таблица со связью через BookingCommodityId.

По таблице "client_commodity_reserve"
То же самое, что и выше.
Плюс... можно выдать только зарезервированный товар, то есть сначала в резерв, а потом на выдачу.
Дата резерва не нужна.


По таблице "client_issue"
Дело в том, что товар может быть не выдан сразу клиенту, может быть такая схема:
- Менеджер выдал другому менеджеру (в другой офис)
- Далее менеджер выдал опять менеджеру... (так может быть 2-3-4-5-6 раз... то есть, цепочка выдач от менеджера к менеджеру)
- Конечный менеджер уже выдал товар клиенту

------------------------------------------------

Запрос к БД
Это запрос на получение списка всех доступных для выдачи товаров, которые есть в резерве.
Выбираются все товары, зарезервированные менеджером с ID=7 для всех клиентов.

Потом через PHP-скрипт результат группируется на два массива - список клиентов и список товаров по каждому из клиентов.

Вот такой вот монстр... 



------------------------------------------------

Вопросы:
1. Не слишком ли громоздко получается?
2. Не будет ли это медленно при бОльших объёмах данных?

Это сообщение отредактировал(а) fridkaratel - 27.11.2012, 11:42

Добавлено @ 15:08


Да не будет, не будет =) В вашем случае вообще трудно представить, чтобы все это еще как-то тормозило. Не загрузите вы БД так сильно.

Это сообщение отредактировал(а) Arantir - 27.11.2012, 17:48

Чет с утра в спешке не все внимательно прочел.
Теперь структура кажется более-мене нормальной.


Клиенты регистрируются на сайте? Тогда им следует выдать уникальный идентифицирующий ключ (проще говоря - логин =). Ну если имя - это логин, то тогда все ОК, но не забудьте обеспечить его уникальность (добавить и ClientId, и Name  в первичный ключ). Раскрывать во внешний мир внутренние идентификаторы - не очень безопасно.

Кстати, почему вы всегда пытаетесь получить всю информацию одним запросом? Не пробовали в php-скрипте разделить на несколько запросов?

Это сообщение отредактировал(а) Arantir - 27.11.2012, 17:57

У вас тут ни одного уникального поля нету, получается.
А почему же не сделать вот так:

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


--------------------
Почему посты при разнице в 5 минут соединяются, а при разнице в 10 минут уже даблпостинг =///////

Это сообщение отредактировал(а) Arantir - 27.11.2012, 18:15

@Arantir:


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


Структуру таблицы `clients` я привёл минимальной, чтобы не отвлекаться.
Конечно, клиенты имеют логин, имеют права доступа, у них есть телефон, адрес эл. почты и т.п.
Но в данном вопросе логин, телефон и т.п. не имеют значения, поэтому я их не стал указывать в структуре.


Наверно, можно и одним...
Но что-то посмотрел я на вышеприведённого "монстра" - и не знаю, где там "распилить" запрос на два...


Оно, в принципе, и не нужно - не с чем связывать выданный товар. То есть, это конечная таблица, содержащая просто значения.
В этой таблице я сделал составной UNIQUE ключ по полям BookingCommodityId + IssueId


К выдаче ещё добавляется комментарий, например, "Забрал на синем седане" или "забрал после 19 часов", чтобы на что-то опираться, если возникнут вопросы.
Поэтому придётся этот комментарий дублировать для каждого выданного товара...

Я думал так сделать, но в таком случае получается, что четыре столбца будут дублировать значения: IssueDate, UserIdFrom, UserIdTo и Comment.

А как быть в таком случае?



Менеджеру необходимо сформировать заявку на основной склад.
Для этого необходимо получить список долгов его клиентам: DebtQty = BookingQty - ReserveQty - IssueQty

Получается, если не дублировать данные в другой таблице, то выходит такой запрос:


Не медленный он будет?
Ведь, получается, чем больше клиенты заказывают, тем больше данных надо проверить...

С ходу я вижу, что тут надо добавить столбец Status = TINYINT(1) unsigned, который будет содержать 1, если нет долгов и 0, если есть...
Тогда не придётся просчитывать DebtQty...

Как вариант, не добавлять столбец Status, а сделать столбец DebtQty, в который будет записываться количество долга.
Но в этом случае, надо будет при изменении количества заказа (или резерва) изменять ещё и количество долга.

К тому же, меня смущает то, что мне надо делать LEFT JOIN на таблицу `client`, чтобы узнать, относится ли этот клиент к этому менеджеру.
А если менеджеров будет тысяч 5-10 и клиентов на каждого менеджера тоже тысяч по 5-10?

----------------------------------------------------------------

Попутно возник ещё вопрос...

1. Мне надо отобразить список долгов, сгруппированных по артикулу, чтобы оформить заявку на основной склад.
2. Но мне так же надо получить BookingCommodityId и DebtQty, чтобы выставить на резерв товары, которые есть на складе.

Получается такой запрос...
Постарался в SQL-комментариях расписать, что да как...



Имеет ли смысл делать два конкретных запроса?
Или можно обойтись одним, просто группируя в PHP-скрипте по артикулу?

Это сообщение отредактировал(а) fridkaratel - 29.11.2012, 05:52

Если вы не будете пробовать получить все пару сотен тысяч записей сразу, то разницы с JOIN и без него вы не заметите.
В базах данных логарифмические алгоритмы посика. Видели график логарифма? Вот, увеличиваете количество на очень-очень много, а время увеличивается на чуть-чуть. И сортировка тоже логарифмическая. Невозможно сделать логарифмической только выборку. Так как N штук меньше чем за N действий не выберешь.

У вас на пинг между серверами времени больше будет тратится, чем у БД на обработку запросов. Посмотрите на этот форум: [ Время генерации скрипта: 0.3409 ]. А страница появляется далеко не моментально из-за пинга и загрузки информации.

Если вас беспокоит, придется ли менеджеру ждать несколько секунд после нажатия на кнопочку - БД тут точно будет ни при чем.
Если вас беспокоит, не упадет ли БД от множества неэффективных запросов - даже средний домашний компьютер вытянул бы 1тыс. INSERT'ов в секунду, в SELECT'ов и того больше.
Посчитайте: если 15000 менеджеров будут каждые 15 минут оформлять заказ и выполнять при этом по 10 запросов, то это выйдет в среднем 160 запросов в секунду. Если не считать утрированности этой ситуации, то это вообще очень мало.



Далеко не все должно иметь вид лога =) Можно изменять и уже имеющиеся записи. Впрочем да, если надо к каждому изменению комментарий, то надо отдельную таблицу для этого.


Вот и спрашиваю, почему вы все в один запрос пытаетесь вместить...
Посмотрите на это форум: [ Использовано запросов: 35 ], 223 гостей, 15 пользователей. И сервер же не падает.

Я не буду отрицать того факта, что оптимизированные скрипты и запросы и т.д. т.п. - это очень хорошо. Но, думаю, у вас в данный момент недостаточно опыта, чтобы взять так вот с сделать все совершенным. Сделайте для начала просто хорошо. Так чтобы все просто работало. А если уж там будет тормозить - попробуете оптимизировать.

Это сообщение отредактировал(а) Arantir - 29.11.2012, 08:26

@Arantir, спасибо за развёрнутый ответ ;)

Буду делать