Проектируем схему БД

Надоело писать всякие сопли/слезы на тему «как меня все задолбало». В конце концов, мужику 31 год, нехер в жилетку плакаться.

Попробую сочинить некий конструктив. В качестве темы возьму «Как более-менее правильно спроектировать схему базы данных (БД) по предложенной предметной области» Поводов вижу два:
1) Студенты в большинстве своем не могут по словесному описанию системы создать схему БД.
2) Друг написал программу, которая облегчает создание веб-страниц с применением скриншотов, надо ее потестить и попиарить.

Если делать схему по-умному, то для ее проектирования необходимо вспомнить «нормальные формы» (НФ) и тот факт, что на практике обычно используется третья НФ. В умных книжках перечисляются различные способы приведения схемы к 3НФ.

Например, выписать все предполагаемые поля в один большой список, разделить их на логически связанные группы (которые потом станут отдельными таблицами), создать ключевые поля, создать связи между таблицами. Причем имеется последовательность действий, приводящая к требуемому результату: своего рода рецепт – делай все по пунктам и «будет тебе щастье». Я попытался применить этот способ в работе со студентами. Безуспешно. Деление на таблицы студенты вроде бы выполняют, но с ключами и связями – караул.

Второй способ: составить список таблиц (для начала вообще без полей) и сообразить, как эти таблицы между собой будут связаны. Мне этот вариант больше нравится, т.к. в моей голове процесс происходит именно так. Проблема в том, что термины «сообразить», «догадаться» и «понять» не получается оформить в виде рецепта.

Основные вопросы, которые возникают у студентов:
1) Сколько должно быть таблиц?
2) Какое поле в этой таблице будет первичным ключом?
3) Как связать эти две таблицы, где будет «много», где «один»?
4) В случае, если для разработки используется MS Access, на какие поля делать подстановки?

Как я обычно отвечаю:
1) Вопрос глупый, на него не может быть единственно верного ответа. Таблиц может быть сколько угодно. Главное, чтобы пользователю было удобно пользоваться системой, а разработчику удобно сопровождать. Т.е., в принципе, таблица вообще может быть вообще одна, но умудритесь такой интерфейс разработать, чтобы юзер не мог глупостей наделать своими кривыми ручками. А по-хорошему: см. 3НФ.
2) Здесь без головы никак не обойтись. Это после 10-й, 20-й схемы начинаешь их спинным мозгом придумывать, а поначалу – работает голова. Нужно среди столбцов таблицы определить один (или сочетание нескольких), значение которого ни при каких обстоятельствах не повторится и по смыслу может являться ключом. Если определить такое поле не получается, можно тупо ввести суррогатное поле «Код» и сделать его счетчиком, чтобы не заморачиваться его заполнением. Правильно определив первичный ключ, и зная, какие таблицы следует между собой связать, мы практически сразу ответим на вопросы 3 и 4. Ибо, если две таблицы связаны между собой, то одно из полей в этой связи будет первичным ключом, а другое – внешним.
3) Где первичный ключ – там будет «один», где внешний – «много».
4) Подстановку (т.е. формирование списка возможных значений) нужно выполнять для заполнения значений внешнего ключа.

Причем перечисленные вопросы-ответы звучат на каждом уроке. В такой обстановке начинаю чувствовать себя идиотом. Почитав старших товарищей, решил применить дрессуру, т.е. многократное решение однотипных (не одинаковых!) задач. Теперь каждые два-три урока мы начинаем рассматривать новую задачу. На теории все вместе проектируем схему БД, потом на практике пытаемся эту схему реализовать и написать ряд запросов. Результат в принципе есть, но далеко не 100%, даже не 50. Может быть, это просто не каждому дано. Как не дано мне умение рисовать.

Для примера можно рассмотреть деятельность некоей абстрактной фирмы по продаже чего-либо. Фирма реализует товары нескольких видов. Для каждого товара имеется базовая цена реализации. Для оформления отношений с клиентами заключаются договоры. По одному договору может быть несколько заказов на приобретение различных товаров. По каждому договору может быть открыта кредитная линия. Оплата по договору может поступать частями. Возможны скидки на продукцию.

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

1) Фирма реализует товары нескольких видов. Значит должна быть таблица «Товары», которая связана с таблицей «Виды_товаров» (или «Категории»). Правильный вариант связи: первичный ключ Категории.Код_категории связан с внешним ключом Товары.Код_категории. Все остальное – от лукавого.

2) Для оформления отношений с клиентами заключаются договоры. Должны быть таблицы «Клиенты» и «Договоры», связанные между собой. Связь: первичный ключ Клиенты.Код_клиента связан с внешним ключом Договоры.Код_клиента. Иначе может получиться ситуация «одноразовый клиент», когда для каждого нового договора придется добавлять нового клиента (даже если он уже есть в имеющемся списке).

3) По одному договору может быть несколько заказов на приобретение различных товаров. Должна быть таблица «Заказы», которая связана с таблицами «Товары» и «Договоры». Заказы нужны, чтобы в рамках одного договора можно было приобретать различные товары. Если попытаться связать договоры и товары напрямую, то получим связь «многие-ко-многим», которая не может быть реализована в реляционных базах. В нашем случае получится промежуточная таблица «Заказы» и две связи:
первичный ключ Договоры.Код_договора связан с внешним ключом Заказы.Код_договора и
первичный ключ Товары.Код_товара связан с внешним ключом Заказы.Код_товара.

4) Оплата по договору может поступать частями. Должна быть таблица «Оплата», которая связана с таблицей «Договоры».

Все остальные слова в условии касаются распределения некоторых полей по таблицам. Общая схема будет выглядеть примерно так:

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

Для учебных целей эта схема выглядит неплохо. О том, как ее сделать более менее пригодной для использования – в другой раз.

ЗЫ. Сочинять конструктив оказалось значительно сложнее, чем нести всякую херь. Схему сляпал за полчаса, описание готовил несколько дней. Ну не люблю я оформительские работы, не люблю.