Руководство для начинающих по написанию схем баз данных mySQL

Создайте свою собственную базу данных mySQL, используя только текстовый редактор и эту базовую структуру, или ‘схему’

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

На удивление проще, чем кажется, давайте познакомимся с различными аспектами, используемыми для написания хорошо продуманной схемы базы данных

Синтаксис CREATE TABLE

Для начала откройте ваш любимый текстовый редактор. Для создания схемы базы данных не требуется ничего, кроме обычного текстового файла. База данных состоит из нескольких таблиц, каждая из которых состоит из столбцов, а для создания одной таблицы используется синтаксис CREATE TABLE. Вот базовый пример:

CREATETABLEusers
  id INT NOT NULL,
  is_active TINY INT NOT NULL,
  full_name VAR CHAR(100) NOT NULL,
  email VARCHAR(100) NOT NULL
);

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

Используйте правильные типы столбцов

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

COL_NAME TYPE  OPTIONS 

Имя колонки, затем тип колонки, затем все необязательные параметры. Дополнительные параметры мы рассмотрим позже, а пока сосредоточимся на типе столбца. Ниже перечислены наиболее часто используемые типы столбцов:

ТипОписание

По большому счету, вышеперечисленные типы столбцов – это все, что вам нужно для написания хорошо построенных схем баз данных mySQL

Определите параметры столбцов

При определении столбцов также можно указать различные параметры. Ниже приведен еще один пример оператора CREATE TABLE :

CREATETABLEusers
  id INT NOT NULL PRIMARY KEY AUTO_INCREMENT,
  username VARCHAR(100) NOT NULL UNIQUE,
  status ENUM('active','inactive') NOT NULL DEFAULT 'active',
  balance DECIMAL(8,2) NOT NULL DEFAULT 0,
  date_of_birth DATETIME,
  created_at TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP
);

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

• Вы всегда должны использовать NOT NULL для всех возможных столбцов, чтобы помочь в скорости и производительности таблицы. Это просто указывает, что столбец не может быть пустым / нулевым при вставке строки.
• Всегда старайтесь сохранить размер столбца настолько маленьким, насколько это реально возможно, так как это помогает улучшить скорость и производительность.
• Столбец id представляет собой целое число, также является первичным ключом таблицы, что означает его уникальность, и будет увеличиваться на единицу при каждой вставке записи. Обычно его следует использовать во всех создаваемых таблицах, чтобы можно было легко ссылаться на любую отдельную строку в таблице.
• Столбец status является ENUM и должен иметь значение либо ‘активный’, либо ‘неактивный’. Если значение не указано, новый ряд будет начинаться со статуса ‘активный’.
• Столбец баланс начинается с 0 для каждой новой строки и представляет собой сумму, отформатированную с двумя десятичными точками.
• Столбец date_of_birth – это просто DATE, но также допускает нулевое значение, поскольку дата рождения может быть неизвестна при создании.
• И, наконец, столбец created_at является TIMESTAMP и по умолчанию соответствует текущему времени, когда была вставлена строка.

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

Связывайте таблицы вместе с помощью ограничений внешнего ключа

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

Вот пример:

CREATETABLEusers
  id INT NOT NULL PRIMARY KEY AUTO_INCREMENT,
  username VARCHAR(100) NOT NULL UNIQUE,
  full_name VARCHAR(100) NOT NULL,
  created_at TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP
) engine=InnoDB;
 
CREATETABLEorders (
  id INT NOT NULL PRIMARY KEY AUTO_INCREMENT,
  userid INT NOT NULL,
  amount DECIMAL(8,2) NOT NULL,
  product_name VARCHAR(200) NOT NULL,
  FOREIGN KEY (userid) REFERENCES users (id) ONDELETECASCADE
) engine=InnoDB;

В последней строке вы заметите предложение FOREIGN KEY. В этой строке просто говорится, что эта таблица содержит дочерние строки, которые связаны по столбцу userid со своей родительской строкой, которая является столбцом id таблицы users. Это означает, что при удалении строки из таблицы users mySQL автоматически удалит все соответствующие строки из таблицы orders , обеспечивая структурную целостность вашей базы данных

Также обратите внимание на engine=InnoDB в конце приведенного выше утверждения. Хотя InnoDB сейчас является типом таблиц mySQL по умолчанию, это было не всегда, поэтому его следует добавить на всякий случай, поскольку каскадирование работает только с таблицами InnoDB

Проектируйте с уверенностью

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

После создания схемы убедитесь, что вы знаете, как использовать ее с помощью этих важных команд SQL