Excel и базы данных

Сохранение в базе данных информации о заказах

Если бы база данных офиса РР хранила данные только о выпускаемых книгах и заказчиках, то полезность ее была бы невысока. Расширим эту базу данных за счет того, что будем хранить в ней данные о заказах, которые делают заказчики на книги, выпускаемые редакцией. Это расширение сразу же придает некий содержательный характер нашему тестовому примеру. Прежде всего, таблицы в базе данных станут взаимосвязанными, как им и полагается быть в любой приличной базе данных. С другой стороны на такой базе данных можно демонстрировать решение ряда типичных офисных задач. Обработка заказов, хранящихся в базе данных, позволяет руководству офиса в дальнейшем принимать обоснованные решения, например, назначать срок и объем последующего тиража, определять объем средств, вкладываемых в рекламную компанию, оценивать деятельность дилеров, осуществляющих заказы. Я предполагаю в последующих главах продемонстрировать некоторые возможности по обработке заказов, а пока давайте сосредоточимся на том, как сохранить данные о заказе в базе данных.

Каждый заказ, как правило, содержит требования на разные товары. В нашем случае это означает, что в одном заказе содержится заявка на книги разных авторов. В таких ситуациях целесообразно данные о заказе хранить не в одной, а в двух взаимосвязанных таблицах. Я назвал эти таблицы "Заказы" и "Заказано". В первой из них каждый заказ задается одной записью, хранящей общие сведения о заказе. Во второй таблице одному заказу соответствует несколько записей, - по одной записи на каждый заказываемый товар. Такой способ хранения информации в двух таблицах позволяет всю общую информацию о заказе сохранять лишь один раз. Есть целая наука о том, как правильно проектировать базы данных, но я сейчас не буду ее затрагивать, ограничившись лишь упоминанием о ней. Пока нам достаточно продолжить рассмотрение примера и определить структуру таблиц "Заказы" и "Заказано":

• Заказы. Эта таблица имеет следующие поля: КодЗаказа, Заказчик, Сотрудник, ДатаЗаказа, Стоимость. Код заказа играет роль ключа и является уникальным полем, автоматически заполняемым в момент записи заказа в базу данных. Следующие два поля совпадают с соответствующими полями таблиц "Заказчики" и "Сотрудники" и, тем самым, связывают эти таблицы. Я неявно предполагаю, что база данных офиса РР уже частично расширена за счет естественного добавления таблицы "Сотрудники". Действительно, было бы странно хранить информацию о заказчиках и не иметь информации о собственных сотрудниках. Поэтому каждый заказ хранит информацию не только о заказчике, но и о сотруднике офиса РР, оформляющего этот заказ. Следующие два поля - ДатаЗаказа и Стоимость (общая стоимость заказа) не требуют особых пояснений. Так выглядит общая информация о заказе.
• Заказано. Эта таблица имеет следующие поля: КодЗаказа, НазваниеКниги, Количество, Стоимость. Как я уже говорил, в этой таблице одному заказу будет соответствовать, как правило, несколько записей. Каждая запись - строка таблицы - содержит данные об одной из заказываемых книг. Все записи одного заказа будут иметь один и тот же код заказа, совпадающий с кодом заказа таблицы "Заказы". Поле "КодЗаказа" связывает между собой таблицы "Заказы" и "Заказано". Заметьте, повторяющийся код заказа не может быть ключом для таблицы "Заказано". Роль ключа в этой таблице играют два поля - КодЗаказа и НазваниеКниги, которые в совокупности являются уникальными для каждой записи таблицы "Заказано", хотя каждое из них в отдельности уникальным полем не является и может многократно повторяться в записях таблицы. Поля этой таблицы - Количество и Стоимость - задают количество экземпляров заказываемой книги и их суммарную стоимость.

Вот как выглядит определение этих таблиц в Конструкторе Access:

Рис. 4.11. Определение таблицы "Заказы" в конструкторе

Рис. 4.12. Определение таблицы "Заказано" в конструкторе

Теперь база данных офиса РР приобрела некоторый законченный вид. Первый этап ее создания можно считать завершенным. В ней есть запросы, есть формы, а главное созданы 5 таблиц: Книги, Сотрудники, Заказчики, Заказы, Заказано. Все таблицы базы данных можно связать между собой общими полями. Взгляните на схему, отражающую связи между таблицами:

Рис. 4.13. Схема связей между таблицами базы данных офиса РР

Заметьте, все связи между таблицами имеют тип "один ко многим". Так, например, одному коду заказа из таблицы "Заказы" соответствуют несколько записей с аналогичным кодом в таблице "Заказано". Аналогично, одной фамилии (полю ФИО) из таблицы "Сотрудники" соответствует несколько записей в таблице "Заказы", поскольку понятно, что один сотрудник может оформлять множество заказов.

Списки Excel, как локальная база данных

Говоря об Excel и базах данных нельзя не упомянуть и о такой возможности Excel, как ведение собственной локальной базы данных, не использующей никаких внешних источников данных. Небольшие базы данных можно вести непосредственно на Excel. Такую базу данных можно представлять как совокупность таблиц. Таблицы естественны для Excel и их создание не вызывает никаких трудностей. Для поиска данных в таблицах, их отбору, сортировке предусмотрены специальные средства. Когда объем данных невелик и нет необходимости в сложных запросах, требующих одновременной работы с несколькими таблицами, работать с данными в Excel проще и быстрее. Умение вести базу данных на Excel особенно важно для тех, кто не имеет доступа к другим базам данных и работает с версией Office 2000, не содержащей Access.

Базу данных Excel удобнее всего располагать на нескольких листах по числу таблиц, хранящих данные. На каждом листе обычно располагается одна таблица. Это ограничение накладывает метод AdvancedFilter, без которого, как правило, не обходится обработка данных в таблице. Многие запросы к таблице можно реализовать с помощью этого метода. Таблицу, хранящую данные, в Excel принято называть списком. Список, как и всякая таблица, состоит из строк и столбцов. Столбцы - это поля списка (таблицы БД). Первая строка списка обычно содержит имена полей. Вот несколько правил, которым должны удовлетворять списки, часть из них носит рекомендательный характер:

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