Глава 3. Проектирование баз данных для коммерческих приложений
Прежде чем переходить к программированию сайта, необходимо предварительно разобраться со структурой всех его ключевых компонентов. В основе любого интерактивного Web-сайта лежит хорошо спроектированная база данных.
В этой книге используется система управления базами данных Microsoft SQL Server. Вместо нее также можно использовать Oracle и другие базы данных с поддержкой интерфейсов ODBC и OLE DB. При изложении материала программный код SQL был по возможности избавлен от привязки к конкретному серверу для того, чтобы его можно было использовать на разных платформах баз данных.

Microsoft SQL Server
SQL Server предлагается фирмой Microsoft как решение промышленного уровня в области баз данных, Microsoft Access является предложением начального уровня, предназначенным для разработки простых приложений. Осенью 1998 года появилась версия SQL Server 7, заметно усовершенствованная по сравнению с версией 6.
SQL Server - мощная платформа для построения реляционных баз данных, используемых при построении коммерческих Web-сайтов с большим объемом проводимых операций. Эта серверная технология заложена в основу таких коммерческих сайтов, как Martha Stewart (www.marthastewart.com), Electronics Boutique (www.ebworld.com) и 1-800 Flowers (www.1800flowers.com).
СОВЕТ
Хотя базы данных, описанные в этой книге, создавались на Microsoft SQL Server, они также могут использоваться в Oracle или даже Microsoft Access. Однако на этих платформах приходится вносить исправления в некоторые запросы SQL, хранимые процедуры и т. д.

Программирование SQL
Взаимодействие с SQL Server осуществляется в основном через Visual Studio, а точнее - через средства работы с базами данных, входящие в Visual InterDev и Visual Basic 6.0. В обоих продуктах предусмотрены возможности построения запросов, управления таблицами и т. д.
Для непосредственной работы с SQL Server можно воспользоваться программой SQL Enterprise Manager. Ее мощные административные средства позволяют управлять сразу несколькими серверами. При помощи SQL Enterprise Manager можно настраивать, запускать, приостанавливать и завершать экземпляры SQL Server, отслеживать текущие операции и просматривать журнал ошибок SQL Server. Вы можете создавать устройства, базы данных и т. д., управлять параметрами системы безопасности, в том числе регистрационными данными пользователей и правами доступа к базам данных. SQL Enterprise Manager и служба SQL Executive позволяют включать режим оповещения о различных серверных событиях, а также планировать выполнение сервером определенных задач. В SQL Enterprise Manager существуют графические средства для настройки и управления процессом репликации, предусмотрены возможности выполнения и анализа запросов, архивации и восстановления баз данных, автоматической генерации сценариев SQL и т. д.
Однако тонкости настройки SQL выходят за рамки этой книги. В следующей главе мы рассмотрим некоторые проблемы, связанные с применением SQL Server.
Язык Transact-SQL
Microsoft SQL Server поддерживает язык Transact-SQL, который является надмножеством SQL (Structured Query Language) - стандартного языка для построения запросов к реляционным базам данных. Язык T-SQL имеет сертификат соответствия стандарту ANSI SQL-92, однако при адаптации фрагментов, использующих нестандартные расширения, конечно, возникнут проблемы. Код SQL, написанный для этой книги, по возможности соответствует стандарту ANSI.

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


Поле

Описание

idDepartment

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

chrDeptName

Название раздела, отображаемое в приложении

txtDeptDesc

Описание раздела, предназначенное для служебных целей или для внешнего вывода

chrDeptlmage

Ссылка на графическое изображение, представляющее данный раздел

Перейдем к определению товаров.
Товары
На первый взгляд задача кажется простой. Но поспешные попытки определить обобщенный товар в базе данных сталкиваются с неожиданными трудностями. Например, компьютер может обладать множеством атрибутов (тактовая частота процессора, объем жесткого диска, память и т. д.), а у простой скрепки может вообще не быть ни одного атрибута.
Структура таблиц базы данных
В нашем примере с Web-сайтом товары (футболки) обладают всего двумя атрибутами - размером и цветом. Впрочем, реляционная структура таблицы позволяет использовать большее количество типов атрибутов.
При определении товара используются четыре таблицы: Products - основная информация о товаре.
Attribute - все значения атрибутов (например, красный, зеленый, X, XL). ProductAttribute - связь между товаром и его атрибутами,
AttributeCategory - категория, к которой относится конкретный атрибут (например, размер, цвет или вес).
Следует выделить еще два важных отношения: классификация товаров по разделам и необходимость отражения связей между товарами. Например, красная юбка может быть связана с красной блузкой, которая к ней хорошо подходит. Связи такого типа называются горизонтальными. Возможны и другие, вертикальные связи. Они используются в ситуациях, когда вместо дешевого товара покупателю предлагается другой, более дорогой аналог. Любой товар может принадлежать нескольким разделам и быть связанным с несколькими товарами.
Каждый товар принадлежит по крайней мере к одному разделу. В нашем примере допускается, чтобы товар принадлежал к нескольким разделам. Следовательно, понадобится отдельная таблица DepartmentProducts, содержащая информацию о принадлежности товаров к разделам. В другой таблице, RelatedProducts, хранится информация о связях между товарами. Ниже будут приведены команды SQL, которые создают эти таблицы и образуют связи между ними. Поля таблицы Products описаны в табл. 3.2.
Таблица 3.2. Поля таблицы Products


Поле

Описание

ipProduct

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

ChrProductName

Название товара, отображаемое в приложении

txtDescription

Описание товара. Информация хранится в текстовом формате, но текст может содержать теги HTML, определяющие его внешний вид

ChrProductlmage

Графическое изображение. Обычно в этом поле содержится имя файла на Web-сервере, в котором находится изображение. Также поле может содержать гиперссылку на файл

intPrice

Цена товара. Чтобы не возникало проблем с округлением, цена хранится в виде целого числа с двумя разрядами в дробной части

dtSaleStart

Начальная дата распродажи товара

dtSaleEnd

Конечная дата распродажи товара

intSalePrice

Цена товара при распродаже

intActive

Признак активности товара

Таблица Attribute
От полей таблицы товаров можно перейти к описанию полей таблицы атрибутов, перечисленных в табл. 3.3.
Таблица 3.3. Поля таблицы Attribute


Поле

Описание

idAttribute

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

cnrAttributeName

Название атрибута, выводимое для покупателя

idAttributeCategory

Ссылка на категорию, к которой относится данный атрибут

Таблица ProductAttribute
Конкретный продукт связывается со списком атрибутов при помощи таблицы ProductAttribute. Поля этой таблицы описаны в табл. 3.4.
Таблица 3.4. Поля таблицы ProductAttribute


Поле

Описание

idProductAttribute

Автоматически увеличиваемый счетчик, содержащий уникальный идентификатор для каждой комбинации

idAttribute

Идентификатор атрибута

idProduct

Идентификатор товара, с которым связан данный атрибут

Как нетрудно убедиться, в таблице хранится простой перечень атрибутов, связанных с каждым товаром. Обратите внимание - на этом уровне значения атрибутов не связываются с категориями.
Таблица AttributeCategory
Наконец, каждому значению атрибутов в списке необходимо поставить в соответствие некоторую категорию. Поля таблицы AttributeCategory описаны в табл. 3.5.
Таблица 3.5. Поля таблицы AttributeCategory


Поле

Описание

idAttributeCategory

Автоматически увеличиваемый счетчик, содержащий уникальный идентификатор для каждой категории

chrCategoryName

Название категории

В таблице просто перечисляются категории атрибутов.
ПРИМЕЧАНИЕ
Чтобы узнать, какими категориями атрибутов обладает тот или иной товар, достаточно построить запрос, который возвращает список категорий на основании набора атрибутов, присвоенных объекту. Из результатов запроса следует исключить возможные повторения.

Классификация товаров по разделам
Как уже говорилось выше, каждый товар должен быть отнесен по крайней мере к одному разделу. Впрочем, структура базы данных должна быть достаточно гибкой, чтобы товары могли принадлежать сразу нескольким разделам. Например, хотя блузка и относится к категории "Блузки", она также может быть частью раздела "Весенняя коллекция". Компакт-диск может одновременно относиться к категориям "Джаз" и "Блюз". Поля таблицы DepartmentProducts описаны в табл. 3.6.
Таблица 3.6. Поля таблицы DepartmentProducts


Поле

Описание

IdDepartmentProduct

Автоматически увеличиваемый счетчик, содержащий уникальный идентификатор для каждой комбинации

IdDepartment

Идентификатор раздела

idProduct

Идентификатор товара, относящегося к данному разделу

Эта таблица, как и AttributeCategory, содержит простой список комбинаций "товар/раздел".
Связывание товаров
Наконец, мы должны предусмотреть способ установления логической связи между двумя товарами. С точки зрения покупателя это может выглядеть как список логически связанных товаров или как предложение перейти к другому товару. Например, это могут быть товары, представляющие интерес для данного покупателя, или те, существование которых необходимо учитывать при приобретении другого товара. Связи между товарами устанавливаются в таблице Related-Products. Поля этой таблицы описаны в табл. 3.7.
Таблица 3.7. Поля таблицы RelatedProducts


Поле

Описание

IdRelatedProduct

Автоматически увеличиваемый счетчик, содержащий уникальный идентификатор для каждой комбинации

IdProductA

Идентификатор товара

IdProductB

Идентификатор товара, связанного с предыдущим товаром

IdRelationType

Тип связи (горизонтальная или вертикальная)

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

Правильное определение товаров и их связей имеет решающее значение для хорошего представления ассортимента товаров покупателю. Структура, описанная выше, подходит лишь для очень простых товаров с минимальным набором атрибутов. В более сложных ситуациях структуры данных товаров также усложняются.
В частности, у большинства компаний уже имеются готовые базы данных товаров. Хотя структура этих баз данных не всегда позволяет использовать их непосредственно в коммерческом приложении, вы должны связать их с базой электронного магазина и обеспечить синхронизацию данных в этих двух базах.
Покупатели
От товаров мы переходим к покупателям. Мы, разумеется, должны хранить информацию о них - особенно о тех, кто что-нибудь купил. Возможны разные варианты, от хранения минимальных данных до построения полного профиля покупателя. При наличии полного профиля оформление заказов может производиться одним щелчком мыши (как на Amazon. com). В простейшем случае для каждого заказа сохраняются только платежные реквизиты, а также данные для отправки счетов и доставки товара.
ПРИМЕЧАНИЕ
Помните: электронные магазины бывают такими же разнообразными и сложными, как и люди. У всех людей есть общие элементы (голова, сердце, тело), но по характеру и природе мы сильно отличаемся друг от друга. Вы должны хорошо понимать, какие требования предъявляются к вашему магазину и как лучше применить шаблон, описанный в книге.

Если информация о покупателе накапливается на постоянной основе, у вас появляется много возможностей для персональной настройки Web-сайта. При наличии профильных данных некоторые товары можно предлагать в момент очередной регистрации пользователя на сайте. Кроме того, открываются дополнительные возможности для сообщения покупателю состояния заказа, номеров транспортных накладных и т. д.
В примерах этой книги мы ограничиваемся сохранением общих данных покупателя. Позднее покупатель может прочитать эту информацию, для этого он должен ввести свое имя и пароль или установить cookie.
Таблица с данными покупателя имеет простую структуру. Ее записи в основном состоят из реквизитов, используемых при доставке или выписке счета. Поскольку в каждом заказе присутствует идентификатор покупателя, вы можете установить соответствие между теми товарами, которые он заказывал ранее, и содержимым сайта (персональная настройка). Поля таблицы Shopper перечислены в табл. 3.8.
ВНИМАНИЕ
Из-за участившихся случаев похищения данных приходится учитывать проблемы безопасности, связанные с долгосрочным хранением данных кредитных карт. Возможно, некоторые покупатели не захотят, чтобы данные их карт хранились в вашей базе данных. Если вы будете настаивать на этом, то можете лишиться клиентов. Наш сценарий не предусматривает долгосрочного хранения платежных реквизитов в базе данных покупателей.

Таблица 3.8. Поля таблицы Shopper


Поле

Описание

idShopper

Автоматически увеличиваемый счетчик, содержащий уникальный идентификатор покупателя

chrFirstName

Имя покупателя

chrLastName

Фамилия покупателя

chrAddress

Адрес

chrCity

Город

chrState

Штат

chrProvince

Область

chrCountry

Страна

chrZipCode

Почтовый индекс

chrPhone

Телефон

chrFax

Факс

chrEmail

Адрес электронной почты

dtEntered

Дата ввода информации о покупателе

chrUserName

Имя пользователя. Вводится покупателем при обращениях к профилю, получении информации о состоянии заказов и т.д

chrPassword

Пароль. Вводится покупателем при обращениях к профилю и получении информации о состоянии заказов

intCookie

Флаг проверки имени пользователя и пароля при обращении к профилю. Отказ от проверки позволяет немедленно идентифицировать пользователя, когда он в очередной раз посетит сайт со своего компьютера

В нашем примере таблица Shopper содержит типичные реквизиты, используемые при выписке счетов. Покупатель может заказать доставку товара на другой адрес. Иногда в профиль включается постоянный адрес доставки, а реквизиты для выписки счетов изменяются. Впрочем, в большинстве случаев реквизиты для доставки и выписки счетов совпадают.
ПРИМЕЧАНИЕ
В нашем примере данные кредитных карт не заносятся в базу данных покупателей. Вместо этого они сохраняются в отдельной таблице для каждого конкретного заказа. В дополнение к ней можно было бы создать вторичную таблицу с типами кредитных карт, сроками действия и т. д. Учтите, что покупатель может выбрать другой способ оплаты. В операциях "бизнес/бизнес" следует предусмотреть такие способы, как наложенный платеж, накладная, заказ на приобретение и т. д.

Следует помнить, что для каждого заказа нередко создается новый профиль - даже при оформлении нескольких заказов одним покупателем. Покупатель может попросту отказаться от использования своего профиля. Любые попытки помешать ему в этом заметно снижают свободу выбора посетителей Web-сайта.
ПРИМЕЧАНИЕ
В данном примере не учтена возможность международных заказов, поэтому в записи отсутствуют соответствующие адресные поля.

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


Поле

Описание

idBasket

Автоматически увеличиваемый счетчик, содержащий уникальный идентификатор корзины

intQuantity

Общее количество позиций в корзине

dtCreated

Дата создания корзины

idShopper

Идентификатор покупателя, создавшего корзину

intOrderPlaced

Признак оформления заказа на корзину

intSubTotal

Промежуточный итог без учета налогов, стоимости доставки, обработки заказа и т.д.

intTotal

Общая стоимость заказа с учетом всех дополнительных сборов

intShipping

Стоимость доставки заказа. Вычисляется по действующим нормам

intTax

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

Обратите внимание - в корзине хранятся различные составляющие итоговой суммы. Они фиксируются в момент оформления заказа на случай изменения действующих норм в области дополнительных сборов, а также рекламных кампаний или особых условий для заказов данного типа.
В каждой корзине присутствует список входящих в нее товаров. Информация из этого списка используется при оформлении заказа. Поля таблицы Basket-Items описаны в табл. 3.10.
Таблица 3.10. Поля таблицы BasketItems


Поле

Описание

idBasketltem

Автоматически увеличиваемый счетчик, содержащий уникальный идентификатор каждой позиции в корзине

idProduct

Идентификатор товара, включенного в корзину

intPrice

Цена товара на момент включения в корзину. В случае распродажи может отличаться от основной цены

chrName

Название товара

intQuantity

Количество заказанных единиц товара

idBasket

Идентификатор корзины, к которой относится данная позиция

chrSize

Значение атрибута "размер"

chrColor

Значение атрибута "цвет"

СОВЕТ
Операции с таблицами, содержащими данные корзин и их отдельных позиций, являются одним из основных аспектов управления базами данных. На Web-сайтах с большим объемом сделок количество корзин может существенно превышать количество размещенных заказов. Необходимо предусмотреть возможность автоматической очистки корзин, существующих дольше заданного периода времени (например, 24 или 48 часов).

Операции с таблицами корзины осуществляются по мере просмотра Web-сайта покупателем. Ниже будут рассмотрены функциональные средства для добавления, обновления и удаления отдельных позиций корзины. А пока мы переходим к рассмотрению заказов, размещаемых на нашем сайте.
Заказы
Во всех сделках бизнесмены больше всего любят именно этот этап - получение денег. Информация о заказе сохраняется в тот момент, когда покупатель завершает отбор товаров в корзину и переходит к оформлению заказа. Необходимо сохранить основные параметры заказа, в том числе адреса для доставки и выписки счетов, платежные реквизиты, перечень заказанных товаров и т. д.
Информация заказа тесно связана с содержимым корзины, а точнее - с товарами, находящимися в этой корзине.
В нашем примере заказ связывается с корзиной и покупателем. Каждой корзине и каждому заказу обязательно должен соответствовать некоторый покупатель.
Платежные реквизиты хранятся в отдельной таблице. Это сделано для того, чтобы их можно было поскорее удалить, но при этом осталась основная контактная информация по заказу. Поля таблицы OrderData описаны в табл. 3.11.
Таблица 3.11. Поля таблицы OrderData


Поле

Описание

idOrder

Автоматически увеличиваемый счетчик, содержащий уникальный идентификатор заказа

idShopper

Идентификатор покупателя, разместившего заказ

chrShipFirstName

Имя лица, которому доставляется заказ

chrShipLastName

Фамилия лица, которому доставляется заказ

chrShipAddress

Адрес для доставки заказа

chrShipCity

Город, в который доставляется заказ

chrShipState

Штат, в который доставляется заказ. Может повлиять на величину налога и стоимости доставки

chrShipProvince

Область (для международных заказов)

chrShipCountry

Страна, в которую доставляется заказ

chrShipZipCode

Почтовый индекс для доставки заказа. В некоторых сложных ситуациях может использоваться при вычислении налога

chrShipPhone

Телефон для доставки заказа

chrShipFax

Факс для доставки заказа

chrShipEmail

Адрес электронной почты лица, которому доставляется заказ

chrBillFirstName

Имя лица, на которое выписывается счет

chrBillLastName

Фамилия лица, на которое выписывается счет

chrBillAddress

Адрес для выписки счета

chrBillCity

Город для выписки счета

chrBillState

Штат для выписки счета

chrBillProvince

Область для выписки счета

chrBillCountry

Страна для выписки счета

chrBillZipCode

Почтовый индекс для выписки счета

chrBillPhone

Телефон для выписки счета

chrBillFax

Факс для выписки счета

chrBillEmail

Адрес электронной почты лица, на которое выписывается счет

dtOrdered

Дата размещения заказа

ПРИМЕЧАНИЕ
Как правило, для выписки счета и доставки используется один и тот же адрес. Однако мы сохраняем эти данные дважды на случай их изменения. В интерфейсе приложения следует предусмотреть возможность однократного ввода информации в случае, если выписка счета и доставка осуществляются по одному адресу.

Затем мы должны определить таблицу для хранения платежных реквизитов покупателя. Чтобы упростить процедуру проверки, в таблице сохраняются три основных атрибута кредитной карты: тип карты, ее номер и срок действия. Поля таблицы PaymentData описаны в табл. 3.12.
Таблица 3.12. Поля таблицы PaymentData


Поле

Описание

idPayment

Автоматически увеличиваемый счетчик, содержащий уникальный идентификатор платежа

idOrder

Идентификатор заказа, к которому относится платеж

chrCardType

Тип кредитной карты (Visa, American Express и т.д.)

chrCardNumber

Номер кредитной карты

chrExpDate

Срок действия кредитной карты

chrCardName

Имя владельца кредитной карты

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

  • Заказ получен, но еще не исполнен.
  • Заказ находится в процессе исполнения.
  • Заказ находится на последней стадии - в процессе доставки. В информацию состояния включается номер транспортной накладной.

С каждым заказом связывается запись в таблице OrderStatus. Поля этой таблицы описаны в табл. 3.13.

Таблица 3.13. Поля таблицы OrderStatus


Поле

Описание

idOrderStatus

Автоматически увеличиваемый счетчик, содержащий уникальный идентификатор записи, описывающей состояние заказа

idOrder

Идентификатор заказа

idStage

Стадия обработки заказа

dtShipped

Дата отправки (стадия 3)

dtFulfilled

Дата исполнения заказа со склада (стадия 2)

dtProcessed

Дата начала обработки заказа, полученного из Web (стадия 1)

txtNotes

Произвольные заметки, относящиеся к состоянию заказа. Например, при возникновении каких-либо проблем информация о них заносится в это поле

chrShippingNum

Номер транспортной накладной

intProcessed

Признак обработки заказа для последующего исполнения

Таблица состояния заказа может иметь значительно более сложную структуру. В идеальном варианте информация о состоянии заказа принимается непосредственно от системы обработки заказов.
Стоимость доставки
Существует несколько вариантов вычисления стоимости доставки. Факторы, используемые в расчетах, перечислены в табл. 3.14.
Таблица 3.14. Факторы, используемые при вычислении стоимости доставки


Фактор

Описание

Количество единиц товара

Стоимость доставки вычисляется на основании количества единиц товара, включенных в заказ. Возможные варианты - фиксированная стоимость доставки для одной единицы товара или для интервала. Например, стоимость доставки от 1 до 5 единиц составляет $3, от 6 до 10 единиц - $6 и т. д.

Общая стоимость заказа

Вместо количества единиц товара стоимость доставки вычисляется на основании общей стоимости доставки. Например, при заказе на сумму от $0 до $5 стоимость доставки составляет $1, на сумму от $6 до $10 - $3 и т. д.

Вес

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

Расстояние доставки

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

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


Поле

Описание

idQuantityRange

Автоматически увеличиваемый счетчик, содержащий уникальный идентификатор для каждого интервала

idLowQuantity

Нижняя граница количества единиц товара в интервале

idHighQuantity

Верхняя граница количества единиц товара в интервале

intFee

Стоимость доставки в заданном интервале

При оформлении заказа количество единиц товара сравнивается с данными таблицы, в результате чего определяется стоимость доставки. Данные, используемые в наших примерах, приведены в табл. 3.16.
Таблица 3.16. Стоимость доставки


Нижняя граница

Верхняя граница

Стоимость

0

5

$5.00

6

10

$7.50

11

20

$10.00

21

99999

$15.00

Обычно при такой модели определяется максимальный интервал и максимальная стоимость доставки. В нашем примере максимальный интервал задан в границах от 21 до 99 999 единиц. В другой разновидности этого способа используется формула, в которой учитывается количество заказанных единиц свыше 20.
ПРИМЕЧАНИЕ
Часто существуют специальные условия оплаты для доставки в течение одного или двух дней. Например, если покупатель хочет, чтобы товар был доставлен в течение двух дней, стоимость доставки увеличивается на $5. Чтобы товар был доставлен на следующий день, к стоимости прибавляется $10.

Налог
Помимо расходов на доставку, в окончательную стоимость заказа необходимо включить налог. Задача вычисления налога может оказаться как довольно сложной, так и относительно простой. Все зависит от используемой системы налогов и местонахождения точек, с которых доставляются товары. В этой книге налог будет вычисляться по простейшей формуле - в виде фиксированного процента от суммы заказа. Предполагается, что доставка осуществляется только из двух или трех штатов.
Исходя из этих требований, в таблице Tax (см. табл. 3.17) просто устанавливается прямая связь между штатом и налоговой ставкой.
Таблица 3.17. Поля таблицы Tax


Поле

Описание

idState

Автоматически увеличиваемый счетчик, содержащий уникальный идентификатор для каждого штата

chrState

Сокращенное название штата

intTaxRate

Налоговая ставка для указанного штата

Данные, используемые в наших примерах, приведены в табл. 3.18.
Таблица 3.18. Примеры налоговых ставок


Штат

Налоговая ставка

TX

5% (0.05)

VA

10% (0.10)

DC

25%(0.25)

В процессе обработки заказа мы просто ищем в таблице Tax штат, указанный в адресе доставки, и вычисляем налог в зависимости от результатов поиска.
Окончательная структура базы данных
Итак, определение таблиц базы данных завершено.
т полная картина данных, необходимых для работы нашего электронного магазина. На следующем этапе мы рассмотрим сценарии SQL, в которых создаются описанные таблицы.
Сценарии SQL
Завершив проектирование баз данных, можно переходить к созданию сценариев SQL. Сценарии используются для создания таблиц базы данных, а также заполнения некоторых таблиц данными (ставки налогов, стоимость доставки).
ВНИМАНИЕ
Весь код SQL, приведенный в этой главе, тестировался в SQL Server 7 SP1. Хотя в SQL Server 6.5 он должен работать идентично, в других версиях могут возникнуть проблемы.

Обратите внимание - мы не создаем хранимые процедуры и другие объекты базы данных, необходимые для использования таблиц. Они будут созданы по мере изложения материала.
Первый сценарий создает таблицу Attribute. В качестве первичного ключа выбирается поле IdAttribute.
Листинг 3.1. Таблица Attribute
CREATE TABLE dbo.Attribute (
   idAttribute int IDENTITY (1, 1) NOT NULL ,
   chrAttributeName varchar (255) NULL ,
   idAttributeCategory int NULL ,
)
Листинг 3.2. Таблица AttributeCategory
CREATE TABLE dbo.AttributeCategory (
   idAttributeCategory int IDENTITY (1, 1) NOT NULL ,
   chrCategoryName varchar (255) NULL ,
)
В таблице BasketTable первичным ключом является поле idBasket. Поле dtCreated по умолчанию заполняется функцией Get Date (). Все поля, в которых должны храниться денежные величины, по умолчанию инициализируются нулями. Не забывайте инициализировать поля при создании собственной базы данных.
Листинг 3.3. Таблица Basket
CREATE TABLE dbo.Basket (
   idBasket int IDENTITY (1, 1) NOT NULL ,
   intQuantity int NULL CONSTRAINT DF_Basket_intQuantity_1__13
DEFAULT (0),
   idShopper int NULL ,
   intOrderPlaced int NULL CONSTRAINT DF_Basket_intOrderPlaced11__12
DEFAULT (0),
   intSubTotal int NULL CONSTRAINT DF_Basket_intSubTotal_13__12 DEFAULT (0),
   intTotal int NULL CONSTRAINT DF_Basket_intTotal_15__12 DEFAULT (0),
   intShipping int NULL CONSTRAINT DF_Basket_intShipping_12__12
DEFAULT (0),
   intTax int NULL CONSTRAINT DF_Basket_intTax_14__12 DEFAULT (0),
   dtCreated datetime NULL CONSTRAINT DF_Basket_dtCreated_1__12
DEFAULT (getdate()),
   intFreeShipping int NULL CONSTRAINT DF_Basket_intFreeShipping1__13
DEFAULT (0),
   CONSTRAINT PK___9__12 PRIMARY KEY CLUSTERED
   (
      idBasket
   )
)
В таблице Basketltem первичным ключом является поле idBasketltem. Полю intQuantityno умолчанию присваивается 0.
Листинг 3.4. Таблица Basketltem
 CREATE TABLE dbo.BasketItem (
   idBasketItem int IDENTITY (1, 1) NOT NULL ,
   idProduct int NULL ,
   intPrice int NULL ,
   chrName varchar (255) NULL ,
   intQuantity int NULL CONSTRAINT DF_BasketItem_intQuantity1__12 DEFAULT (0),
   idBasket int NULL ,
   chrSize varchar (50) NULL ,
   chrColor varchar (50) NULL ,
   CONSTRAINT PK___10__12 PRIMARY KEY CLUSTERED
   (
      idBasketItem
   )
)
Две следующие таблицы, Department и DepartmentProducts, определяют классификацию -оваров по разделам. В обоих случаях в качестве первичного ключа выбирается поле счетчика (identity column).
Листинг 3.5. Таблица Department
 CREATE TABLE dbo.Department (
   idDepartment int IDENTITY (1, 1) NOT NULL ,
   chrDeptName varchar (255) NULL ,
   txtDeptDesc text NULL ,
   chrDeptImage varchar (255) NULL ,
   CONSTRAINT PK___1__12 PRIMARY KEY CLUSTERED
   (
      idDepartment
   )
)
В таблице OrderStatus полю idStage присваивается 0 - признак того, что заказ еще не был обработан. Поле intPrecessed также инициализируется нулевым значением. Как только заказ будет получен и принят к исполнению, этому полю присваивается 1. Поле idOrderStatus является первичным ключом.
За таблицей OrderStatus следует таблица PaymentData. Первичным ключом в ней является поле idPayment.
Листинг 3.8. Таблица OrderStatus
 CREATE TABLE dbo.OrderStatus (
   idOrderStatus int IDENTITY (1, 1) NOT NULL ,
   idOrder int NULL ,
   idStage int NULL CONSTRAINT DF_OrderStatu_idStage_14__12 DEFAULT (0),
   dtShipped datetime NULL ,
   dtFulfilled datetime NULL ,
   dtProcessed datetime NULL ,
   txtNotes text NULL ,
   chrShippingNum varchar (30) NULL ,
   intProcessed int NULL CONSTRAINT DF_OrderStatu_intProcesse1__12 DEFAULT (0),
   CONSTRAINT PK___13__12 PRIMARY KEY CLUSTERED
   (
      idOrderStatus
   )
)
Листинг 3.9. Таблица PaymentData
 CREATE TABLE dbo.PaymentData (
   idPayment int IDENTITY (1, 1) NOT NULL ,
   idOrder int NULL ,
   chrCardType varchar (50) NULL ,
   chrCardNumber varchar (30) NULL ,
   chrExpDate varchar (25) NULL ,
   chrCardName varchar (150) NULL ,
   CONSTRAINT PK___12__12 PRIMARY KEY CLUSTERED
   (
      idPayment
   )
)
Таблица ProductAttribute связывает товары с их атрибутами. Первичным ключом этой таблицы является поле idProductAttribute.
Листинг 3.10. Таблица ProductAttribute
 CREATE TABLE dbo.ProductAttribute (
   idProductAttribute int IDENTITY (1, 1) NOT NULL ,
   idAttribute int NULL ,
   idProduct int NULL ,
   CONSTRAINT PK___4__12 PRIMARY KEY CLUSTERED
   (
      idProductAttribute
   )
)
Таблица Products содержит основную информацию о товарах. Первичным ключом этой таблицы является поле idProduct. Полям intPrice и intSalePrice по умолчанию присваивается 0. Поля dtSaleStart и dtSaleEnd инициализируются 1 января 1980 года, чтобы предотвратить случайное использование цены товара при распродаже.
Листинг 3.11. Таблица Products
 CREATE TABLE dbo.Products (
   idProduct int IDENTITY (1, 1) NOT NULL ,
   chrProductName varchar (255) NULL ,
   txtDescription text NULL ,
   chrProductImage varchar (255) NULL ,
   intPrice int NULL CONSTRAINT DF_Products_intPrice_3__12 DEFAULT (0),
   dtSaleStart datetime NULL CONSTRAINT DF_Products_dtSaleStart_2__12 DEFAULT ('1 / 1 / 80'),
   dtSaleEnd datetime NULL CONSTRAINT DF_Products_dtSaleEnd_1__12 DEFAULT ('1 / 1 / 80'),
   intSalePrice int NULL CONSTRAINT DF_Products_intSalePrice_4__12 DEFAULT (0),
   intActive int NULL CONSTRAINT DF_Products_intActive_3__12 DEFAULT (0),
   intFeatured tinyint NULL CONSTRAINT DF_Products_intFeatured_3__10 DEFAULT (0),
   dtFeatureStart datetime NULL CONSTRAINT DF_Products_dtFeatureStar2__10 DEFAULT ('1/1/80'),
   dtFeatureEnd datetime NULL CONSTRAINT DF_Products_dtFeatureEnd_1__10 DEFAULT ('1/1/80'),
   CONSTRAINT PK___2__12 PRIMARY KEY CLUSTERED
   (
      idProduct
   )
)
В таблице RelatedProducts определяются связи между товарами. Первичным ключом является поле idRelatedProduct, а все остальные поля инициализируются нулями.
Листинг 3.12. Таблица RelatedProducts
 CREATE TABLE dbo.RelatedProducts (
   idRelatedProduct int IDENTITY (1, 1) NOT NULL ,
   idProductA int NULL CONSTRAINT DF_RelatedPro_idProductA_1__12 DEFAULT (0),
   idProductB int NULL CONSTRAINT DF_RelatedPro_idProductB_2__12 DEFAULT (0),
   idRelationType int NULL CONSTRAINT DF_RelatedPro_idRelationT3__12 DEFAULT (0),
   CONSTRAINT PK___7__12 PRIMARY KEY CLUSTERED
   (
      idRelatedProduct
   )
)
В таблице Shipping хранятся данные о стоимости доставки. Первичным ключом является поле idQuantityRange, а все остальные поля инициализируются нулями.
Листинг 3.13. Таблица Shipping
 CREATE TABLE dbo.Shipping (
   idQuantityRange int IDENTITY (1, 1) NOT NULL ,
   intLowQuantity int NULL CONSTRAINT DF_Shipping_intLowQuantit3__12 DEFAULT (0),
   intHighQuantity int NULL CONSTRAINT DF_Shipping_intHighQuanti2__12 DEFAULT (0),
   intFee int NULL CONSTRAINT DF_Shipping_intFee_1__12 DEFAULT (0),
   CONSTRAINT PK___14__12 PRIMARY KEY CLUSTERED
   (
      idQuantityRange
   )
)
В таблице Shopper хранятся данные о покупателях. Поле dtEntered инициализируется функцией GetDate(), то есть датой создания записи о покупателе. Полю intCookie присваивается 0 - признак того, что идентификатор покупателя не должен сохраняться при помощи cookie. Поле idShopper является первичным ключом.
Листинг 3.14. Таблица Shopper
 CREATE TABLE dbo.Shopper (
   idShopper int IDENTITY (1, 1) NOT NULL ,
   chrFirstName varchar (50) NULL ,
   chrLastName varchar (50) NULL ,
   chrAddress varchar (150) NULL ,
   chrCity varchar (100) NULL ,
   chrState varchar (2) NULL ,
   chrZipCode varchar (15) NULL ,
   chrPhone varchar (30) NULL ,
   chrFax varchar (30) NULL ,
   chrEmail varchar (150) NULL ,
   chrUserName varchar (25) NULL ,
   chrPassword varchar (25) NULL ,
   intCookie tinyint NULL CONSTRAINT DF_Shopper_intCookie_1__12 DEFAULT (0),
   dtEntered datetime NULL CONSTRAINT DF_Shopper_dtEntered_1__12 DEFAULT (getdate()),
   chrProvince varchar (150) NULL ,
   chrCountry varchar (150) NULL ,
   CONSTRAINT PK___8__12 PRIMARY KEY CLUSTERED
   (
      idShopper
   )
)
Таблица Tax содержит информацию о ставке налога в различных штатах. Первичным ключом является поле idState, а полю intTaxRate по умолчанию присваивается 0.
Листинг 3.15. Таблица Tax
 CREATE TABLE dbo.Tax (
   idState int IDENTITY (1, 1) NOT NULL ,
   chrState varchar (50) NULL ,
   fltTaxRate float NULL CONSTRAINT DF_Tax_fltTaxRate_1__13 DEFAULT (0),
   CONSTRAINT PK___15__12 PRIMARY KEY CLUSTERED
   (
      idState
   )
)
Приведенные сценарии создают основные таблицы нашей базы данных, устанавливают первичные ключи и инициализируют некоторые поля. Следующим шагом станет наполнение таблиц информацией.
Итоги
Тщательное проектирование баз данных является одним из ключевых факторов успеха любого коммерческого Web-сайта. В этой главе мы определили основные таблицы, которые будут использоваться при построении сайта. В следующей главе рассматриваются системные средства, предназначенные для построения коммерческих решений. Затем мы перейдем непосредственно к программированию.

 

 
На главную | Содержание | < Назад....Вперёд >
С вопросами и предложениями можно обращаться по nicivas@bk.ru. 2013 г. Яндекс.Метрика