Подскажите, какой тип данных использовать

[andrystepa]

Я в первый раз в жизни пишу настоящую БД. Пишу ее на MySQL, интерфейс на PHP. Самое сложное препятствие появилось уже в начале работы. Думал, в процессе все решится, но уже пришло время соединять интерфейс и базу - а базы то и нетути.
База представляет из себя классификатор конструкторской документации изделий и комплектующих предприятия. Проще говоря, в бумажном виде это выглядит как толстый талмуд с оглавлением и страницами с таблицами. Вроде бы проектировать ничего и не надо: есть оглавление - наименование группы изделий, страница таблицы(ссылка), десятичная характеристика изделий. Вот это последнее поле - десятичная характеристика, и вызвала главную трудность. Во-первых их может быть три вида - для комплектов и документов, для узлов и групп и для отдельных деталей. Во-вторых, эта характеристика имеет подобный вид: 1.647 или 6.164, а может быть даже диапазон: от 1.610 до 1.619.  Но эта самая характеристика нужна не только для того, чтобы правильно составить идентификатор изделия (это часть идентификатора, остальная добавляется из порядкового номера в соответствующей таблице и класса изделия), но и для поискового запроса. В общем, я не могу решить, какой тип данных выбрать для этой характеристики. И как отобразить в поле характеристики что она может быть выбрана из диапазона значений. Помогите пожалуйста!

[McZim]

andrystepa, вы наверное не пишите БД, просто наполняете базу + некий интерфейс к ней. Возьмите например тип данных char. А в книгах что пишут? или вы их не читали?

[Алексей++]

мне кажется, пути два:
1)все отдельные части  идентификатора хранить в разных полях
2) хранить в одном текстовом поле в определённом формате

[Sla]

например, так

CREATE TABLE group (
        id int,
   name varchar(32)
)
CREATE TABLE article (
        id int,
   name varchar(32),
        id_group int
)

Create View Article_CODE
AS
Select Str(article.id_group)+'.'+ Str(article.id) as code_article,
          article.Name as  name
From article, group
Where article.id_group=group.id

[andrystepa]

andrystepa, вы наверное не пишите БД, просто наполняете базу + некий интерфейс к ней. Возьмите например тип данных char. А в книгах что пишут? или вы их не читали?

Пока что наполнять нечего. База данных, или классификатор, сейчас, как я уже писал, в виде толстого бумажного талмуда. Мне поставили задачу сделать его в электронном виде, чтобы с ним могли работать конструкторы. Книги у меня есть две - Люк Веллинг "MySQL. Учебное пособие" и Э.Харрис "PHP\MySQL для начинающих". Но в них я к сожалению никакой подсказки не нашел. Более того, мне еще светит проблема: в базе надо будет позднее хранить файлы с чертежами - бинарные файлы. В MS Access есть такой тип поля - объект OLE - можно было бы им воспользоваться. Но у меня MySQL. А в списке типов, которые я нашел в книжке ничего похожего не нашел. Какое поле можно было бы использовать?

например, так

CREATE TABLE group (
        id int,
   name varchar(32)
)
CREATE TABLE article (
        id int,
   name varchar(32),
        id_group int
)

Create View Article_CODE
AS
Select Str(article.id_group)+'.'+ Str(article.id) as code_article,
          article.Name as  name
From article, group
Where article.id_group=group.id

Спасибо. Но все-же непонятно как тут можно сделать, чтобы использовать диапазоны значений?

[McZim]

andrystepa, в чем проблема воспользоваться гуглом???

http://www.google.ru/search?aq=f&complete=1&hl=ru&newwindow=1&q=MySQL+%D1%85%D1%80%D0%B0%D0%BD%D0%B5%D0%BD%D0%B8%D0%B5+%D1%84%D0%B0%D0%B9%D0%BB%D0%BE%D0%B2&btnG=%D0%9F%D0%BE%D0%B8%D1%81%D0%BA&lr=

[Sla]

например, так
CREATE TABLE group (
        id int,
   name varchar(32)
min_limit int,
max_limit int
)

[Sla]

andrystepa, опиши пошаговый алгоритм работы с проектом
группа - номер - диапазон
изделие - номер - группа

[RXL]

andrystepa, или книги неважные, или читаешь невнимательно.
1. "Поле для хранения файлов" ты ни в какой книге не найдешь. Что есть файл? Блок бинарных данных. Соотв. - используем тип BLOB (большие бинарные объекты). Замечу, что BLOB (или LOB) есть практически в любой современной СУБД.
2. Хранение объемных данных в БД должно быть оправдано. Таблицы меньшего объема работают быстрее и база получается компактнее. Как вариант: файлы можно хранить в директории, а в БД хранить только относительный путь к файлу.

[andrystepa]

Кое-что разъяснил начальник КБ. Оказывается диапазон значений нужен потому, что характеристика выглядит примерно так:
5.164.199 - первое число это тип изделия, второе число - идентификатор подтипа изделия, третье число - порядковый номер данного изделия в таблице. Но изделий может быть больше 1000, поэтому для таких случаев и используется диапазон значений идентификатора подтипа изделия. То есть задача похоже упростилась. Как показал  Sla, надо просто в каждой записи оглавления указать лимиты для значений характеристики, а пользователю выводить лишь диапазон - это уже дело техники программирования.
Только вот такая ситуация получается. Я даже не знаю, стоит ли мне ее разруливать. Получается что таблица базы, представляющая из себя оглавление практически ничем не связана с остальными таблицами. То есть единственная связь - характеристика, по сути является идентификатором таблицы. И я толком не знаю, как связать ее с таблицей. То есть понятно, что название таблицы можно сделать таким же, как название идентификатора, и выбирая название идентификатора строить запрос к соответствующей таблице. Уфф, немного путанно. Короче, MySQL не будет знать о связи - это знает лишь программа. Хорошо ли это?

[RXL]

andrystepa, приведи структуру таблиц.

[andrystepa]

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

[andrystepa]

Кстати, меня несколько озадачил этот код:

например, так

CREATE TABLE group (
        id int,
   name varchar(32)
)
CREATE TABLE article (
        id int,
   name varchar(32),
        id_group int
)

Create View Article_CODE
AS
Select Str(article.id_group)+'.'+ Str(article.id) as code_article,
          article.Name as  name
From article, group
Where article.id_group=group.id

Что означает CREATE View ...?
Я знаю пока только CREATE DATABASE и CREATE TABLE
К тому же я даже толком не знаю как получить справку по командам MySQL!

[RXL]

andrystepa, VIEW - это объект базы данных. С точки зрения интерфейса - это таблица, а по сути - это просто запрос. В MySQL VIEW появилось в версии 5.0.

[RXL]

Мой вариант:

Код: (SQL)

CREATE TABLE articles (
        level TINYINT UNSIGNED NOT NULL,
        id SMALLINT UNSIGNED NOT NULL,
        parent_id SMALLINT UNSIGNED NOT NULL,
        name VARCHAR(32) NOT NULL,
        PRIMARY KEY (level, id, parent_id)
);

CREATE VIEW articles_codes AS
SELECT
        t1.id code1, t2.id code2, t3.id code3,
        t1.name name1, t2.name name2, t3.name name3,
        CONCAT(t1.id, '.', t2.id, '.', t3.id) code
FROM articles t1, articles t2, articles t3
WHERE t1.level = 0
        AND t2.level = 1
        AND t3.level = 2
        AND t1.id = t2.parent_id
        AND t2.id = t3.parent_id;

Тест:

Код: (SQL)

INSERT INTO articles VALUES
        (0, 1, 0, 'aaaa'),
        (1, 1, 1, 'bbb'),
        (2, 1, 1, 'cccccc');

SELECT *
FROM articles_codes
WHERE code1 = 1 AND code2 = 1 AND code3 = 1; /* код 1.1.1 */

code1   code2   code3   name1           name2           name3           code
1       1       1       aaaa            bbb             cccccc          1.1.1

SELECT code
FROM articles_codes
WHERE code1 = 1 AND code2 = 1 AND code3 = 1;

code
1.1.1

Что непонятно?

[andrystepa]

Код:

[code]

RXL,
Поправьте меня, если я в чем-то неправ. Создаются три таблицы. articles, которые состоят каждая из части данных о изделии.
Запрос CREATE VIEW articles_codes AS ... объединяет эти три таблицы в одну и создает для новые колонки из данных таблиц, сводя все в новую таблицу articles_codes.
Я предполагал слегка по-другому.

Код:

СREATE TABLE catalog (
	id INT NOT NULL auto_increment PRIMARY KEY,
	name VARCHAR(25),
	id_group INT NOT NULL, // идентификатор группы изделий - первое число характеристики, указывает что это - сборка, узел, деталь или документ
	izdocnum INT,                   // номер для изделий(сборок) и документов - второе число характеристики
	uzgrnum INT,                    // номер для узлов и групп
	detnum INT                       // номер для деталей
	);
СREATE TABLE limits (
	id INT NOT NULL, PRIMARY KEY,
	max1 INT,
        min1 INT, 
	cur1 INT,                   // max, min и текущий номер для изделий(сборок) и документов
        max2 INT,
        min2 INT,
        cur2 INT,                    // max, min и текущий номер для узлов и групп
        max3 INT,
        min3 INT,
	cur3 INT                       // max, min и текущий номер для деталей
	);

Правда застрял на том, что не могу придумать, как связать эти таблицы. Да и вторая таблица корявая получилась .[/code]

[RXL]

andrystepa, ага - совершенно не верно

Где три таблицы? Все три компонента идентификатора полностью однотипны. Так зачем три таблицы создавать? Можно обойтись одной, добавив столбец, по которому они будут отличаться. Что я в примере и показал.

Запрос CREATE VIEW articles_codes AS ... объединяет эти три таблицы в одну и создает для новые колонки из данных таблиц, сводя все в новую таблицу articles_codes.

VIEW дано лишь для примера.

andrystepa, оставь SQL в покое - у тебя не хватает опыта для проектирования БД "на лету". Рекомендую сперва почитать теорию.

Рекомендую статьи:
Введение в Базы данных
Теоретические основы реляционных баз данных.
Нормализация реляционных баз данных
И прочие статьи в разделе Базы данных/Общее.

[andrystepa]

andrystepa, ага - совершенно не верно

Где три таблицы? Все три компонента идентификатора полностью однотипны. Так зачем три таблицы создавать? Можно обойтись одной, добавив столбец, по которому они будут отличаться. Что я в примере и показал.

Запрос CREATE VIEW articles_codes AS ... объединяет эти три таблицы в одну и создает для новые колонки из данных таблиц, сводя все в новую таблицу articles_codes.

VIEW дано лишь для примера.

andrystepa, оставь SQL в покое - у тебя не хватает опыта для проектирования БД "на лету". Рекомендую сперва почитать теорию.

Рекомендую статьи:
Введение в Базы данных
Теоретические основы реляционных баз данных.
Нормализация реляционных баз данных
И прочие статьи в разделе Базы данных/Общее.

В перечисленных статьях ничего нового не нашел, а вот статьи Введение в SQL, в частности Введение в SQL - 4
https://club.shelek.ru/viewart.php?id=230
помогли понять, что же такое t1, t2, t3 и для чего они понадобились. И хотя я еще не до конца понял логику работы, все равно путь понятен. Спасибо

[andrystepa]

Да, кстати, как я понял, VIEW это представление? Я прав? Подскажите, где бы про него подробнее прочитать - для чего надо, как использовать, и т.д. В тех статьях, что я прочитал об этом говорится вскользь. Если можно, с привязкой к MySQL

[McZim]

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

[Sla]

McZim, не торопись с изменениями т.е. вьюхи бывают readonly

вот как ты, например, сделаешь update вот такой вьюхи

Код:

Create View Article_CODE
AS
Select Str(article.id_group)+'.'+ Str(article.id) as code_article,
          article.Name as  name
From article, group
Where article.id_group=group.id

Представления - это таблицы, содержание которых  выбирается других таблиц. Работают в запросах и операторах DML точно также как и основные таблицы, но собственных данных не содержат. Представление (view) фактически представляет из себя  запрос, который выполняется всякий раз, когда представление вызывается. Вывод запроса при этом становится содержанием представления.

[andrystepa]

И все-же если можно, покажите ссылочку или укажите книгу, где можно об этом поподробнее. В документации MySQL все это описано, но достаточно бедно, с одним простеньким примером. А функциональность у представлений, как мне кажется, довольно большая.

[RXL]

andrystepa, в доке MySQL полностью описан синтаксис. Суть мы тебе уже рассказали: запрос представляется как таблица. Вьюхи бывают только для чтения, изменяемые и материализованные (физическая копия результата запроса).

Почитать можно стандарт SQL-92 - его поддерживают многие современные СУБД (конечно, каждый по своему). Есть ли современные хорошие книги по MySQL - не в курсе. Раньше были, но MySQL на месте не стоит и развивается.

[Алексей++]

по MySQL книга 2007 года , авторы
Максим Кузнецов
Игорь Симдянов

судя по вступлению, у них ещё парочка книг есть, можно этих авторов поискать

[andrystepa]

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

[Dimka]

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

[Sla]

andrystepa, опиши весь путь нумерования каталога.
а то получается какой-то бред


Есть группа изделий
Каждой группе соответствует свой код в указанном диапазоне
Каждое изделие привязывается к группе по номеру


По сути дела у тебя что группа, что изделие - одно и то же.
Поэтому предложенный RXL'ем шаблон таблицы имеет право на жизнь

Код:

CREATE TABLE articles (
	id SMALLINT UNSIGNED NOT NULL,
	parent_id SMALLINT UNSIGNED NOT NULL,
	min_level  SMALLINT UNSIGNED NOT NULL,
	max_level  SMALLINT UNSIGNED NOT NULL,
	name VARCHAR(32) NOT NULL,
	PRIMARY KEY (level, id, parent_id)
);

Т.е. для групп parrent_id=0
Причем min_level  и мах_level нужны только на этапе заполнения данных, или же на этапе интерактивной выборки пользователем.
т.е.
чтобы получить подгруппы (изделия) входящие в группу

Select * from articles
where parent_id=0




Чтобы выбрать изделие (таблицу) принадлежащую группе (вот здесь видимо нужно ввести еще одно поле идентифицирующее  именно изделие

Select a1.*
  from articles a, articles a1
where a.parent_id=0
  and a1.parent_id=a.id

Если нужно получить только подгруппы, то нужно добавить в запрос анализ наличия child'ов

Select a1.*
  from articles a, articles a1
where a.parent_id=0
  and a1.parent_id=a.id
  and EXISTS (SELECT * FROM articles a2
                    WHERE a2.parent_id=a1.id);



Но! Я так понял далее что наступает некая рекурсия по дереву...

Группа.подгруппа.поддгруппа.изделие
Группа.подгруппа.изделие

[Sla]

dimka,

Для таблиц в БД известны 5 уровней нормализации

А можешь сказать какие уровни?

[Dimka]

А можешь сказать какие уровни?

Конечно

1 нормальная форма
2 нормальная форма
3 нормальная форма + нормальная форма Бойса-Кодда
4 нормальная форма
5 нормальная форма

[RXL]

dimka, ты пальцем покажи (ну, ссылку или цитату).