Проектирование БД

    Для хранения информации в базе данных необходимо предварительно определить группы и параметры данных, свеcти эту информацию в реляционные таблицы и установить между ними связи. Кроме того, необходимо задать первичные ключи и индексы, нормализовать структуру. Конечным результатом проектирования будет схема БД и типовой сценарий SQL на ее основе.

Определение таблиц

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

Категории каталога

    Организация структуры и работы категорий может быть совершенно различной, но обычно используется рекурсивная схема, при которой одно из полей записи содержит ссылку на родительскую категорию (рис. 1.5). 
    Рекурсивная схема категорий характеризуется параметрами, описанными в табл. 1.4.

Таблица 1.4. Поля таблицы категорий (Categories)

    Для обеспечения возможности структурировать товар в категориях введено поле ParentCategory. Это поле -- не что иное, как рекурсивный внешний ключ, указывающий на уникальный идентификатор Id, категории уровнем выше. Уникальный идентификатор Id однозначно определяет запись в таблице и является первичным ключом.

Рис. 1.5. Использование вложенности категорий

Первичный ключ -- поле или набор полей таблицы, однозначно идентифицирующие каждую строку.

Рекурсивный внешний ключ -- внешний ключ, ссылающийся на запись в собственной реляционной таблице.

    Рекурсивный внешний ключ в нашем случае позволяет создавать вложенность категорий. Для указания корневой директории вводится логическое ограничение: корневая категория имеет идентификатор с первичным ключомId=0. 
    Тип данных для полей Id и ParentCategory выбран исходя из того, что категорий в несколько раз меньше, чем товаров, и для нашего небольшого магазина вполне достаточно зарезервировать 65535 категорий/подкатегорий; для обоих полей используется тип SMALLINT UNSIGNED. 
    Поле Name имеет максимальную длину 32 символа, но этого достаточно, потому что название категории должно описываться одним, максимум двумя-тремя словами.

Описание книг

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

• таблицей информации о товарах, в которой описаны основные параметры книг (Books);
• таблицей информации об авторах, в которой хранятся данные об авторах книг, представленных в Интернет-магазине (Authors);
• таблицей информации об издательствах (Publishers).

    Параметры таблицы товаров Books описаны в табл. 1.5.

Таблица 1.5. Поля таблицы книг (Books)

    Типы данных для полей определены в соответствии с особенностями реальных свойств товара и специфическими свойствами базы данных MySQL. 
    Так, для названия книги (поле Name) определена максимальная длина 255 символов, и используется тип VARCHAR, а не CHAR, поскольку число букв в названии книг может быть различным. Использование полей переменной длины позволяет избежать хранения ненужной информации.

Вообще говоря, вопрос не так однозначен, поскольку браузеры удаляют двойные пробелы в соответствии со спецификацией HTML, а таблицы с постоянной длиной записи обрабатываются быстрее таблиц с переменной длиной записи. Но в нашем случае тип переменной длины (VARCHAR, TEXT) используется и в других полях таблицы, поэтому использование типа CHAR не повлияет качественно на скорость обработки записей.

    Длина поля определяется максимально возможными или достаточными для большинства записей значениями. Так, для полей названия книги (поле Name) и краткого описания (поле Synopsis) определена длина 255 символов, чтобы гарантировать правильное представление наименования и описания товара. В то же время для поля ISBN 13 символов достаточно, чтобы точно описать уникальный ISBN- номер книги.

Номер в данном случае описывается в символьном виде, поскольку ISBN может иметь вид как "1-123-12345-1", так и "1-1234-1234-1".

    Файлы с изображениями разработчики MySQL рекомендуют хранить в виде внешних файлов на диске, а в базе данных MySQL указывать только пути к этим файлам -- это существенно увеличивает скорость работы базы данных и приложения в целом, позволяет кэшировать изображения на стороне HTTP-сервера. Максимальная длина пути файла в большинстве операционных систем ограничивается 255 символами. 
    Для описания числа страниц (поле PagesCount) достаточно предусмотреть тип SMALLINT, использующий 2 бита или диапазон от -32768 до +32767. Нам совсем не требуется так много, но следующий меньший тип TINYINTсоответствует диапазону -128 до +127, или максимально 255 (в случае беззнакового типа), а этого недостаточно. 
    Дата публикации (поле PublicationDate) описана как тип YEAR, поскольку интерес представляет именно год публикации. В то же время для времени поступления книги в магазин (поле AppearDate) выбран тип DATE, так как по этому полю будет производиться поиск наиболее новых книг (например, поступивших за последнюю неделю). 
    Цена книги хранится в поле Price с типом DECIMAL(6,2), для данного проекта этого достаточно. 
    Поля Author (информация об авторе) и Publisher (информация об издательстве, выпустившем книгу) описаны какSMALLINT UNSIGNED, они являются ссылками на записи в таблицах Authors и Publishers, то есть внешними ключами.

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

    Запись о книге однозначно идентифицируются с помощью уникального поля Id, которое является первичным ключом таблицы. 
    Основные выборки из таблицы Books будут производиться по категориям (поле Category), так как книги однозначно привязаны к категории, к которой они относятся, с учетом даты появления книги в магазине (полеAppearDate), поэтому следует добавить составной индекс по этим двум полям. 
    В соответствии с техническим заданием необходимо обеспечить поиск товара в названиях и описаниях товара (поля Name и Synopsis), для ускорения возможностей поиска необходимо определить индексы по этим полям.

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

    В Интернет-магазине предусматривается возможность выборки информации по имени автора или названию издательства, следовательно, необходимо добавить еще два индекса (поля Author и Publisher). 
    Об авторе достаточно знать имя и краткую биографическую справку. Список произведений, написанных определенным автором, формируется на основе данных таблицы Books. Параметры таблицы авторов Athors описаны в табл. 1.6.

Таблица 1.6. Поля таблицы авторов (Authors)

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

Таблица 1.7. Поля таблицы издательств (Publishers)

    Так же как и в таблице авторов, в этой таблице необходим только первичный ключ Id, однозначно определяющий запись в таблице.

Связи между таблицами Интернет-каталога

    Описанных выше четырех таблиц достаточно, чтобы создать структурированный каталог и представить полную (или почти полную) информацию о книгах, находящихся в нем. 
    Для того чтобы более точно проследить логику спроектированной базы данных и связи между таблицами, рисуется модель логической структуры данных. 
    Фактически на данном этапе закончено проектирование структуры Интернет-каталога, на рис. 1.6 представлена его окончательная модель.

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

Рис. 1.6. Модель логической структуры данных

Данные Интернет-магазина

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

Таблица 1.8. Поля таблицы пользователей (Users)

    Для полей Name и Surname (имя и фамилия покупателя соответственно) определена максимальная длина поля 127 символов, этого должно быть достаточно для большинства потенциальных покупателей, излишнее увеличение связано с тем, что иногда пользователи вводят и имя, и фамилию в одном поле. 
    Поле Email определено длиной 64 символа. Возможно, это излишне, так как большинство адресов не превышают 15-30 символов, но представим, что кто-то с очень длинным адресом захочет купить товар в этом магазине. В случае с информацией о покупателях лучше перестраховаться и предусмотреть такую возможность. 
    Поле Phone (номер телефона для подтверждения заказа) используется для хранения как номера телефона, так и кода города/страны (например, 7-(812)-312-00-00), если пользователь ввел эту информацию. 
    Для поддержания сессий пользователя идентификация выполняется по полям IP (текущий IP покупателя) иSessionKey (уникальный код для авторизации).

С помощью proxy-серверов несколько пользователей могут использовать один IP-адрес; чтобы исключить возможность подмены пользователя, в поле SessionKey генерируется уникальный для конкретного пользователя ключ сессии.

    Дополнительное поле LastVisit (время последнего посещения) поможет управляющему каталогом удалять "новых" пользователей, добавивших товары, но не завершивших покупки в течение длительного времени. 
    Первичным ключом в данном случае является Id, но кроме Id пользователь также характеризуется уникальным E-Mail-адресом. Основные выборки будут производиться по полям Id, IP и LastVisit, эти поля включаются в отдельный индекс. 
    В приложении будет использована упрощенная схема пользовательской корзинки. Информация о добавленном в корзинку товаре непосредственно помещается в таблицу. Для реализации упрощенной схемы пользовательской корзинки достаточно параметров, описанных в табл. 1.9.

Таблица 1.9. Поля таблицы пользовательской корзинки (Orders)

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

Связи между таблицами Интернет-магазина

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

Рис. 1.7. Модель логической структуры Интернет-магазина

Сценарий SQL

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

Если для создания схемы базы данных вы пользовались каким-либо CASE-средством, возможно, вы сможете сгенерировать сценарий автоматически, на основе составленной модели базы данных.

Листинг 1.1. Сценарий SQL для Интернет-магазина

DROP TABLE IF EXISTS Users;

DROP TABLE IF EXISTS Orders;

DROP TABLE IF EXISTS Books;

DROP TABLE IF EXISTS Authors;

DROP TABLE IF EXISTS Categories;

DROP TABLE IF EXISTS Publishers;

#==============================================================#

# Table : Publishers #

#==============================================================#

CREATE TABLE Publishers (

     Id SMALLINT NOT NULL AUTO_INCREMENT,

     Name VARCHAR(255) NOT NULL,

     Description TEXT NOT NULL,

     PRIMARY KEY (Id)

);

#==============================================================#

# Table : Authors #

#==============================================================#

CREATE TABLE Authors (

     Id SMALLINT UNSIGNED NOT NULL AUTO_INCREMENT,

     Name VARCHAR(255) NOT NULL,

     Biography TEXT NOT NULL,

     PRIMARY KEY (Id)

);

#==============================================================#

# Table : Categories #

#==============================================================#

CREATE TABLE Categories (

     Id SMALLINT NOT NULL AUTO_INCREMENT,

     ParentCategory SMALLINT NOT NULL,

     Name VARCHAR(32) NOT NULL,

     PRIMARY KEY (Id),

     INDEX (ParentCategory),

     CONSTRAINT FK_CATEGORI_REFERENCE_CATEGORI FOREIGN KEY (ParentCategory)

REFERENCES Categories (Id)

);

#==============================================================#

# Table : Books #

#==============================================================#

CREATE TABLE Books (

     Id MEDIUMINT NOT NULL AUTO_INCREMENT,

     Category SMALLINT NOT NULL,

     Name VARCHAR(255) NOT NULL,

     Author SMALLINT UNSIGNED NOT NULL,

     Publisher SMALLINT UNSIGNED NOT NULL,

     ISBN CHAR(13) NOT NULL,

     ImageHREF VARCHAR(255) NOT NULL,

     Synopsis TEXT NOT NULL,

     PagesCount SMALLINT NOT NULL,

     PublicationDate YEAR NOT NULL,

     AppearDate DATE NOT NULL,

     Price DECIMAL(6,2) NOT NULL,

     PRIMARY KEY (Id),

     INDEX (Category, AppearDate),

     INDEX (Author),

     INDEX (Publisher),

     FULLTEXT INDEX (Name),

     FULLTEXT INDEX (Synopsis),

     CONSTRAINT FK_Books_REFERENCE_Authors FOREIGN KEY (Author)

REFERENCES Authors (Id),

     CONSTRAINT FK_Books_REFERENCE_PUBLISHE FOREIGN KEY (Publisher)

REFERENCES PublisherS (Id),

     CONSTRAINT FK_Books_REFERENCE_CATEGORI FOREIGN KEY (Category)

REFERENCES Categories (Id)

);

#==============================================================#

# Table : Orders #

#==============================================================#

CREATE TABLE Orders (

     Id INT NOT NULL,

     Amount TINYINT NOT NULL,

     Book MEDIUMINT NOT NULL,

     INDEX (Id),

     INDEX (Book),

     CONSTRAINT FK_Orders_REFERENCE_Books FOREIGN KEY (Book)

REFERENCES Books (Id)

);

#==============================================================#

# Table : Users #

#==============================================================#

CREATE TABLE Users (

     Id MEDIUMINT UNSIGNED NOT NULL AUTO_INCREMENT,

     Name VARCHAR(127) NOT NULL,

     Surname VARCHAR(127) NOT NULL,

     Email VARCHAR(64) NOT NULL,

     Password VARCHAR(12) NOT NULL,

     Phone VARCHAR(20) NOT NULL,

     Address VARCHAR(255) NOT NULL,

     IP CHAR(14) NOT NULL,

     SessionKey INT UNSIGNED NOT NULL,

     LastVisit DATETIME NOT NULL,

     OrderID INT UNSIGNED NOT NULL,

     PRIMARY KEY (Id),

     INDEX (Id, IP, LastVisit),

     INDEX(Email, Password),

     CONSTRAINT FK_Users_REFERENCE_Orders FOREIGN KEY (OrderID)

REFERENCES Orders (Id)

);

Проектирование банерной системы

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

• фиксированная плата за оговоренный период;
• оплата по числу показов;
• оплата по числу нажатий на банер.

    Предоставление рекламного места на страницах сайта за фиксированную плату целесообразно на сайтах с устоявшейся аудиторией. Рекламодатель заинтересован в посетителях своего сайта, поэтому для него удобнее использовать учет по числу нажатий на банер, то есть по числу пользователей, перешедших к нему на сайт по гиперссылке банера. Однако большинство банерных сетей используют систему учета на основе числа показов, потому что CTR (click/through ratio) банера сильно зависит от субъективных параметров, таких как оформление и содержание.

CTR, click/through ratio -- отношение числа нажатий на банер к числу его показов. Одна из важнейших характеристик рекламных свойств банера, качественно показывает его отдачу как рекламного средства.

    Для проектируемой банерной сети банеры будут показываться случайным образом, но при этом будет учитываться максимальное число показов, то есть банеры могут быть показаны только заданное число раз. При жестком определении числа показов может возникнуть ситуация, когда банерных показов не останется, и банеры перестанут показываться. Чтобы исключить такие ситуации, необходимо предусмотреть банеры с неограниченным числом показов. 
    Для организации небольшой банерной сети достаточно параметров, перечисленных в табл. 1.10. 
    Поле Id однозначно характеризует учетную запись банера -- это первичный ключ таблицы Banners. 
    Графический банер характеризуется, в первую очередь, размерами, они описаны в полях Height и Width. Эти параметры позволят использовать графические банеры нескольких форматов.

Таблица 1.10. Параметры таблицы банерной системы сайта (Banners)

    URL банера хранит информацию о графическом файле, который должен быть отображен в браузере пользователя. Банерная система должна работать как для показа локально расположенных банеров, так и внешних банеров систем обмена. Банеры внешних систем будут определяться исходя из URL банера по наличию в строке "http://", то есть при указании абсолютного пути к внешнему источнику. Путь к банерам, расположенным локально, начинается с "/", то есть указывается относительно корневой директории файловой системы Интернет-сервера. Разграничение между внутренними и внешними банерами необходимо для определения способа показа банера. 
    Ссылка, по которой будет переходить пользователь после нажатия на банер, хранится в поле Link. 
    Информация о числе банеров, показанных пользователям, находится в поле ShowCount, а максимальное число показов банера, после превышения которого он не будет показываться, -- в поле ShowMax. 
    Выборка банера для показа производится по размеру банера, с учетом числа показов; банеры, выработавшие максимальное число показов, больше не должны показываться. В некоторых случаях, например при подключении внешних банерных сетей, не нужно ограничивать количество показов банеров. Максимальное число показов указывается в поле ShowMax. Нулевое значение в этом поле указывает на неограниченный ресурс банера.

Универсальные приложения Интернет-торговли

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

Рис. 1.8. Пример связи таблиц базы данных при использовании унифицированных параметров

    Например, в таблице товаров описаны минимально необходимые параметры, а в таблице описаний товаров хранятся данные, характеризующие товар (рис. 1.8). При подготовке товара для отображения на экране пользователя происходит выборка значений из таблицы описаний товара c Id, идентичным Id товара. После этого в шаблон товара в определенном порядке добавляются соответствующие свойства. 
    Подобным образом обрабатываются не только параметры, описывающие визуальную часть интерфейса, но и специфические параметры, не заданные в типовой конфигурации или принципиально перемещенные в таблицы параметров. Например, для универсальных приложений таким же образом производится разделение ценообразования, рассчитываются скидки и налоги. 
    Помимо более глубокого расщепления информации, связанной с товарами, практикуется создание групп покупателей с различными ценовыми делениями и списками доступных товаров. 
    Как и все универсальное, этот способ построения коммерческих приложений имеет свои недостатки. Наиболее очевидный из них -- это скорость. На выборки и анализ результатов выборок тратится достаточно много времени, а для Интернет-проектов этот параметр является критическим, особенно для больших проектов с базами данных, размер которых превышает 1-2 Гбайт.

Резюме

    В этой главе мы рассмотрели первый этап проектирования Интернет-приложения:

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

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