Как указано в названии, при разработке баз данных, какой предпочтительный способ обрабатывать таблицы, которые имеют несколько столбцов, которые только сохраняют значения true/false как одно или одно или значение (например, "Y/N: или" 0/1 ")? Также существуют некоторые проблемы, которые могут возникнуть между различными базами данных (например, Oracle и SQL Server), которые могут повлиять на обработку столбцов?
При проектировании баз данных, каков предпочтительный способ хранения нескольких значений true/false?
Ответ 1
В SQL Server
существует тип данных BIT
. Вы можете сохранить 0 или 1, сравнить значения, но не запускать MIN
или MAX
.
В Oracle
вы просто используете NUMBER
или CHAR(1)
.
В MySQL
и PostgreSQL
любой тип данных неявно конвертируется в BOOLEAN
.
Обе системы поддерживают BOOLEAN
тип данных, который вы можете использовать как есть, без операторов, в предложениях WHERE
или ON
:
SELECT *
FROM mytable
WHERE col1
что невозможно в SQL Server
и Oracle
(у вас должен быть какой-то вид или предикат).
В MySQL
, BOOLEAN
является синонимом TINYINT(1)
.
В PostgreSQL
тоже (с точки зрения хранения), но логически, он неявно не конвертируется в какой-либо другой тип.
Ответ 2
Из собственного опыта я предпочитаю char (1) для 'Y' или 'N'. Использование 0 и 1 может быть немного запутанным в зависимости от того, сколько пива я уже пил, а функция С++ main() возвращает 0 при успехе. Типы ENUM и BIT больше проблем, чем они того стоят.
Интересно отметить, что MySQL information_schema
использует VARCHAR (3) для "YES" или "NO".
Пример:
information_schema.USER_PRIVILEGES (
...
IS_GRANTABLE VARCHAR(3) NOT NULL DEFAULT ''
)
Ответ 3
Вместо булевых типов данных вы можете захотеть рассмотреть другую модель данных для хранения логических значений, что может быть особенно уместно в следующих случаях:
- когда у вас будет много столбцов да/нет.
- когда вам, вероятно, потребуется добавить в будущем дополнительные столбцы да/нет.
- когда значения yes/no не изменяются очень часто.
Определение прав пользователя может быть типичным примером вышеизложенного. Рассмотрим следующие таблицы:
Table "Users": (user_id, name, surname, country)
Table "Permissions": (permission_id, permission_text)
Table "Users_Permissions": (user_id, permission_id)
В таблице Permissions
вы должны указать все возможные разрешения, которые могут быть применимы к пользователям. Вам нужно добавить строку в таблицу Permissions
для каждого атрибута yes/no. Как вы могли заметить, это упрощает добавление новых разрешений в будущем без изменения схемы базы данных.
В приведенной выше модели вы должны указать значение TRUE, назначив user_id
с permission_id
в таблице Users_Permissions
. В противном случае по умолчанию это будет FALSE.
Например:
Table "Permissions"
permission_id text
-----------------------------------
1 "Read Questions"
2 "Answer Questions"
3 "Edit Questions"
4 "Close Questions"
Table "Users_Permissions"
user_id permission_id
-----------------------------------
1 1
1 2
1 3
2 1
2 3
<сильные > Преимущества
- Индексирование: вы можете легко использовать индекс для запроса конкретных фактов.
- Пробел. Стандартное соглашение сохраняет пространство, когда у вас много ложных значений.
- Нормализованный. Факты определяются в их собственных таблицах (в таблицах
Permissions
иUsers_Permissions
.) Вы можете легко хранить дополнительную информацию по каждому факту.
Недостатки
- Запросы. Для простых запросов потребуются JOIN.
- Настройка для False. Чтобы установить значение false, вам нужно будет удалить строку (из таблицы
Users_Permissions
.) В противном случае вы можете использовать флаг "deleted" вUsers_Permissions
, что также позволит вам хранить информацию для аудиторских маршрутов, например, когда было изменено разрешение и кем. Если вы удалите строку, вы не сможете сохранить эту информацию.
Ответ 4
Используйте все, что имеет смысл для конкретного используемого механизма базы данных. Это интерфейс к базе данных, которая должна обрабатывать его. Если интерфейс с кодовым интерфейсом к базе данных достаточно модульный, то это будет не что иное, как простое однострочное изменение для обработки другого булевого типа в базовой базе данных.
Ответ 5
Если ваша СУБД поддерживает булевский тип данных, например MySQL, используйте его. Если это не так, как Oracle, я обычно использую char (1) со значениями Y или N. В последнем случае неплохо написать пару функций для преобразования Java или С++ или любого другого логического типа от Y/N, чтобы избежать избыточного кода для этого. Это довольно тривиальная функция, но ей придется иметь дело с такими случаями, как нули или значения, отличные от Y или N, и вы хотите сделать это последовательно.
Я бы определенно НЕ упаковывал флаги в одну переменную с битовыми операциями. Да, это сэкономит место на диске, но цена намного сложнее и возможности для ошибки. Если ваша СУБД не поддерживает бит-операции - и поскольку у меня никогда не было желания делать что-то подобное, я не знаю, с какой стороны, если это возможно, тогда у вас будет реальное затруднение выбора или сортировки на основе такого флага. Конечно, вы можете получить все записи, соответствующие другим критериям, а затем вызывать код вызывающего кода с соответствующими значениями флага. Но что, если только небольшой процент записей имеет желаемое значение флага, и у вас есть запрос, который объединяет многие другие записи? Например, "выберите employee.name, sum (pay.amount) от оплаты труда сотрудника, используя (employee_id), где employee.executive = true и pay.bonus = true". С предложением where вы, вероятно, извлекаете очень скромное количество записей. Без него вы получите всю базу данных.
В наши дни дисковое пространство дешево, поэтому экономия на диске может быть неважной. Если у вас действительно есть огромное количество флагов - например, сотни или тысячи из них на запись - я полагаю, что может быть случай их упаковки. Но это будет вниз по моему списку вариантов дизайна.
Изменить: Позвольте мне подробнее написать класс, чтобы преобразовать ваш "SQL-логический" в "Java boolean". То же самое относится к любому языку, но я буду использовать Java в качестве примера.
Если ваша СУБД имеет встроенный логический тип, то с помощью Java вы можете прочитать это непосредственно в логической переменной с помощью ResultSet.getBoolean().
Но если вам нужно сохранить его как, скажем, char "Y" или "N", вы должны прочитать его в строке. Поэтому мне разумно объявить класс следующим образом:
class MyBoolean
{
boolean value;
final static MyBoolean TRUE=new MyBoolean(true), FALSE=new MyBoolean(false);
public MyBoolean(boolean b)
{
value=b;
}
public MyBoolean(String s)
{
if (s==null)
return null;
else if (s.equals("Y"))
return MyBoolean.TRUE;
else
return MyBoolean.FALSE;
}
public static String toString(MyBoolean b)
{
if (b==null)
return null;
else if (b.value)
return "Y";
else
reutrn "N";
}
public String toString()
{
return toString(this);
}
}
Затем вы можете легко получить булевы из базы данных с помощью "MyBoolean flag = new MyBoolean (rs.getString(" флаг ")); и напишите в базу данных с помощью" rs.setString( "flag", flag.toString());"
И, конечно, вы можете добавить любую другую логику, которая вам нужна, если у вас есть другие логические вещи, которые вам нужно сделать. Если для некоторых целей вы хотите отображать логические значения в виде T/F или Yes/No или On/Off или что-то еще, вы можете просто добавить альтернативные варианты toString - toTFString или toString (значение, truetext, falsetext) или что-то еще - вместо записи аналогичный код снова и снова.
Ответ 6
Я думаю, что значения "Y/N" более значимы, чем "1/0". С Oracle я бы сделал следующее, чтобы данные были проверены как можно больше с помощью механизма базы данных:
- определяют столбцы как char (1)
- добавить контрольное ограничение, которое возможно значения ограничены "in" ( "Y", 'Н')
- если это соответствует бизнес-правилам, сделать их недействительными - это может предотвращать проблемы, когда вы неявно предположим, что все, что не является "Y", имеет значение "N" в вашем SQL
Ответ 7
Вместо добавления столбца я предлагаю создать другую таблицу. Услышьте меня...
Предположим, что у вас есть таблица с именем Customer
:
CREATE TABLE Customer
(
CustomerID NUMBER,
Name VARCHAR(100)
)
Теперь предположим, что вы хотите указать, разрешено ли пользователю показывать результаты поиска. Одним из вариантов является добавление некоторого столбца, представляющего одно из двух возможных состояний:
CREATE TABLE Customer
(
CustomerID NUMBER,
Name VARCHAR(100),
Searchable BOOLEAN /* or CHAR(1) or BIT... */
)
Ваш поисковый запрос будет выглядеть примерно так:
SELECT CustomerID, Name
FROM Customer
WHERE Name LIKE '%TheCustomerNameIAmLookingFor%'
AND Searchable = TRUE /* or 'Y' or 0... */
Это хорошо и просто. Многие люди в этой теме дают хороший совет по выбору типа данных, который должен иметь этот столбец, чтобы заставить синтаксис хорошо воспроизводиться в разных базах данных.
Альтернатива: создание отдельной таблицы
Вместо добавления другого столбца в Customer
, я создам отдельную таблицу, в которой хранится CustomerID
для каждого пользователя с возможностью поиска.
CREATE TABLE Customer
(
CustomerID NUMBER,
Name VARCHAR(100)
)
CREATE TABLE SearchableCustomer
(
CustomerID NUMBER
)
В этом случае клиент считается доступным для поиска, если их CustomerID
существует в таблице SearchableCustomer
. Теперь запрос на поиск клиентов будет выглядеть следующим образом:
SELECT CustomerID, Name
FROM Customer
WHERE Name LIKE '%TheCustomerNameIAmLookingFor%'
AND CustomerID IN (SELECT CustomerID FROM SearchableCustomer)
Вы увидите, что эта стратегия очень переносима для РСУБД:
- Поиск поисковых клиентов использует предложение
IN
илиJOIN
- При создании поиска с помощью клиента используется оператор
INSERT
- При создании клиента, не используемого для поиска, используется оператор
DELETE
Преимущества сюрприза
Вы можете сделать определение поискового клиента таким же сложным, как вы хотите, если вместо таблицы выбрать SearchableCustomer
представление:
CREATE VIEW SearchableCustomer AS
SELECT CustomerID
FROM Customer
WHERE Name LIKE 'S%' /* For some reason, management only cares about customers whose name starts with 'S' */
Ваш поисковый запрос не меняется вообще!: D По моему опыту, это привело к огромной гибкости.
Ответ 8
Бит-столбцы обычно используются для представления значений типа T/F или Y/N, по крайней мере, в SQL Server. Хотя пурист базы данных может сказать вам, что столбцы бит не имеют места в базах данных, потому что они "слишком близки к оборудованию" - Джо Целько.
Ответ 9
"SELECT * Из таблицы WHERE col1
что невозможно в SQL Server и Oracle (у вас должен быть какой-то вид или предикат).
Это только показывает, какое смехотворное и смехотворное отвращение Oracle и SQL-сервер действительно есть.
Если col1 объявлен как тип BOOLEAN, выражение "col1" является предикатом.
Если семантика предложения WHERE требует, чтобы его выражение просто оценивало значение истины, и если какой-либо столбец объявлен типом "значение истинности", тогда "WHERE that-column" должен быть как допустимым, так и поддерживаемым, Период. Любая система, которая не просто раскрывает своих авторов за некомпетентные посредственные шутки, которые они есть.
Ответ 10
Обычно я делал это без значений BIT/BOOLEAN. Вместо этого у меня было бы три таблицы. Скажем, у нас есть система управления проектами, в которой есть проекты, и эти проекты имеют целую кучу атрибутов.
Затем мы имеем таблицы:
Project - Project_ID (INT), - Name (VARCHAR) Attribute - Attribute_ID (INT), - Name (VARCHAR) ProjectAttribute_Rel - Project_ID (INT), - Attribute_ID (INT)
Является ли атрибут проекта истинным или ложным, зависит от наличия в нем строки в ProjectAttribute_Rel.
Как правило, вы должны иметь дело с Attribute_ID в своем коде, поэтому, когда вы читаете атрибуты проекта (где вы, вероятно, имеете Project_ID), вы просто делаете (PHP произвольно используется в качестве примера):
$arrAttributes = array();
$oQuery = mysql_query('
SELECT Attribute_ID
FROM ProjectAttribute_Rel
WHERE Project_ID = '.addslashes($iProjectId).'
');
while ($rowAttribute = mysql_fetch_assoc($oQuery)) {
$arrAttributes[] = $rowAttribute['Attribute_ID'];
}
На этом этапе вы можете проверить, является ли атрибут проекта истинным, проверяя, существует ли он вообще в $arrAttributes. В PHP это будет:
if (in_array($arrAttributes, $iAttributeId)) {
// Project attribute is true!
}
Этот подход также позволяет вам делать всевозможные трюки, чтобы избежать перечисления множества атрибутов при обновлении, снова при выборе (потому что SELECT * плохо в коде), когда вы вставляете и т.д. Это связано с тем, что вы всегда можете прокручивать атрибут таблицы, чтобы найти доступные атрибуты, поэтому, если вы добавите его, и вы делаете так, добавление/редактирование/удаление атрибутов тривиально. Скорее всего, ваш SQL даже не нужно будет изменять, потому что сами атрибуты определены в базе данных, а не в коде.
Надеюсь, что это поможет.