Пример базы данных

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

В проектировании базы данных первое дело, которое Вы должны сделать, это вычислить последовательность действий, необходимых Вам для решения поставленной задачи. В SQL это может выглядеть так:

# The Widget table.
#
# Detailed information on a widget.  Linked to by Purchase_Order_Item
# by way of the widget_id field.  Linked to Widget_Color by way of
# the widget_color field, and to the Widget_Size table by way of the
# widget_size field.
CREATE TABLE Widget_Table (
  widget_id MEDIUMINT(8) NOT NULL AUTO_INCREMENT,
  widget_name CHAR(60) NOT NULL,
  widget_color_id MEDIUMINT(8) NOT NULL,
  widget_size_id MEDIUMINT(8) NOT NULL,
  widgets_on_hand SMALLINT NOT NULL,
  widget_price FLOAT(8,2) NOT NULL,
  commission_percent FLOAT(2,2) NOT NULL,
  PRIMARY KEY (widget_id),
  KEY (widget_name),
  KEY (widget_color_id,widget_size_id)
);

Первая строка сообщает СУБД MySQL, что мы определяем таблицу по имени Widget_Table. Следующие шесть строк определяют поля, которые таблица содержит, тип данных, которые входят в них, и какие атрибуты эти поля имеют.

Прежде, чем Вы сможете создать эту таблицу, Вы должны создать пустую базу данных. В MySQL это выполнено посредством программы mysqladmin.

prompt> mysqladmin create Widget-DB

Одна из множества основных концепций в хорошем проекте реляционной базы данных это то, что Вы никогда не должны хранить избыточные данные. В случае Widget_Table это отражено в полях Widget_color_id и widget_size_id. Эти два поля могли бы быть строками. Взамен мы делаем их указателями на другие таблицы, которые будут содержать одну запись для каждого возможного значения, которое может содержать такое поле.

Это сделано по двум причинам:

• Непротиворечивость
• Централизация сложности

Widget_id поле - среда (3 байт) установленное по размеру целое число. Это имеет специальные атрибуты NOT NULL и AUTO_INCREMENT. NOT NULL является ANSI SQL стандартом и определяет что, когда кто-то вводит widget информацию в эту таблицу, они должны дать некоторое значение для этого поля. Если не дали, MySQL назначит полю значение по умолчанию. Конечно, если значение по умолчанию было определено, то будет использоваться оно, когда не задано никакого значения. Если же оно не определено, то поле получит значение, исходя из его типа.

AUTO_INCREMENT специфический атрибут MySQL. Если Вы вставляете ноль в это поле MySQL, автоматически назначит значение, которое на единицу выше, чем самое высокое предыдущее значение, назначенное к этому полю в этой таблице. Это простой метод для производства уникальных идентификаторов для нового widgets, поскольку они введены в таблицу.

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

Мы также создаем вторичные индексы использованием слова KEY. Индексирование значительно увеличивает быстродействие запросов и объединений. Индексы могут включать больше чем одно поле. Если Вы имеете индекс, который включает больше чем одно поле, Вы не нуждаетесь в создании другого индекса с первым полем в составном индексе.

Мы определили Widget_Table. Теперь надо определить путь слежения за заказами. Для этой цели мы определяем таблицу Purchase_Order.

# The Purchase Order table.
#
# customer_id links us to the Customer_Table
# Where we can get more information about the customer.
#
# To allow for N items on a Purchase order we have to
# have another table called Purchase_Order_Item that
# we link to be way of purchase_order.
CREATE TABLE Purchase_Order (
  purchase_order MEDIUMINT(8) NOT NULL AUTO_INCREMENT,
  customer_id SMALLINT NOT NULL,
  order_date DATE NOT NULL,
  due_date DATE NOT NULL,
  close_date DATE NOT NULL,
  status_code TINYINT(2) UNSIGNED NOT NULL,
  last_action_date TIMESTAMP,
  PRIMARY KEY (purchase_order),
  KEY (customer_id,order_date,status_code)
);

Мы считаем, что заказчик собирается заказывать у нас товар более чем однажды. По этой причине вместо записей об адресе и имени заказчика, мы назначаем каждому заказчику уникальный номер идентификации, который связывает нас со входом в таблице, которая содержит эту информацию. Мы назовем эту таблицу Customer. О ней поговорим немного позже. Также обратите внимание, что мы не имеем никакого упоминания о widgets в этой таблице. Это потому, что порядок приобретения может быть разным. Мы могли бы только определить некоторое фиксированное число способов покупки, но это не будет очень гибко. Взамен этого мы определяем поле, названное purchase_order, которое будет содержать уникальный номер для каждого способа приобретения. Затем мы определяем другую таблицу, которая будет содержать один вход для каждого значения порядка покупки.

Еще одно интересное поле last_action_date. Это поле имеет тип TIMESTAMP. Поля этого типа автоматически модифицируются всякий раз, когда на них выполняется команда INSERT или UPDATE. Это показывает, когда запись была в последний раз изменена.

Таблица для Purchase_Order_Item:

# The Purchase_Order_Item table.
#
# Since there can be more than one item on a purchase order
# we need a table that contains a single item.  We link back to
# The main Purchase_Order table by use of the purchase_order field.
#
# We can also link back to the Widget_Table table by use of the
# widget_id field.
#
# Last of all we link to the Status table by way of the status_code field.
CREATE TABLE Purchase_Order_Item (
  purchase_order SMALLINT NOT NULL,
  order_sequence SMALLINT NOT NULL,
  widget_id MEDIUMINT(8) NOT NULL,
  quantity SMALLINT(4) UNSIGNED NOT NULL,
  status_code TINYINT(2) UNSIGNED NOT NULL,
  order_date DATE NOT NULL,
  item_due_date DATE NOT NULL,
  deliver_date DATE NOT NULL,
  last_action_date TIMESTAMP,
  PRIMARY KEY (purchase_order,order_sequence),
  KEY (widget_id),
  KEY (status_code,order_date)
);

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

Таблица Customer:

# The Customer table.
#
# We need to know where to send those widgets.
#
# Links back to Purchase_Order by way of customer_id.
CREATE TABLE Customer (
  customer_id SMALLINT NOT NULL AUTO_INCREMENT,
  customer_name VARCHAR(80) NOT NULL,
  customer_contact VARCHAR(80) NOT NULL,
  customer_address VARCHAR(80),
  customer_city VARCHAR(80),
  customer_zip VARCHAR(10),
  customer_phone VARCHAR(20),
  customer_fax VARCHAR(20),
  PRIMARY KEY (customer_id),
  KEY (customer_name)
);

Это и совсем просто. Здесь хранится информация о каждом заказчике: его имя, с кем связаться в случае проблем, адрес, телефон и факс.

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

# The Sales_droid table.
#
# Keep track of the people who sell the widgets.
#
CREATE TABLE Sales_Droid (
  sales_droid_id SMALLINT NOT NULL AUTO_INCREMENT,
  sales_droid_first_name VARCHAR(80) NOT NULL,
  sales_droid_last_name VARCHAR(80) NOT NULL,
  sales_droid_phone VARCHAR(20) NOT NULL,
  PRIMARY KEY (sales_droid_id)
);

Нужны еще три простые таблицы для служебной информации:

# The Status table.
#
# Table to contain all valid status codes.
#
# Links to to Purchase_Order_Item and Purchase_Item by way of status_code.
CREATE TABLE Status (
  status_code TINYINT NOT NULL AUTO_INCREMENT,
  status_text VARCHAR(80) NOT NULL,
  PRIMARY KEY(status_code)
);

Таблица Status очень простая. Нам нужен уникальный числовой ID, который связан с коротким текстовым полем, которое содержит текст кода состояния.

# The Widget_Color table.
#
# Table to contain all valid color codes.
CREATE TABLE Widget_Color (
  widget_color_id TINYINT NOT NULL AUTO_INCREMENT,
  color_text VARCHAR(80) NOT NULL,
  PRIMARY KEY(widget_color_id)
);

# The Widget_Size table.
#
# Table to contain all valid color codes.
CREATE TABLE Widget_Size (
  widget_size_id TINYINT NOT NULL AUTO_INCREMENT,
  size_text VARCHAR(80) NOT NULL,
  PRIMARY KEY(widget_size_id)
);

Таблицы Widget_Color и Widget_Size почти идентичен таблице Status. Только имена изменены.

Все! Можно вводить данные.

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

На главную страничку.