Всем привет!

Ситуация такая, мне по наследству досталась база и десятка два клиентских программ к этой базе.
В базе объекты хранятся в таблицах вида:
id int,
field0 varchar(100),
field1 float,
и т.д.
Объект это некая запись с набором полей, например, книга - поля id, автор, название и т.п.

Сейчас мне в базу нужно добавить следующее:
1. Распределение доступа(защита от несанкционированной порчи данных).
2. Протоколирование всех изменений полей объектов с автором, датой и т.п..
3. Некоторые функции, которые относятся ко всем объектам базы, например, напоминания и т.п.
4. Постоянное добавление новых типов объектов в базу.

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

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

1. Создается общая таблица с id всех объектов(objs)
-----
obj_id int,
obj_type_id int.
-----
obj_id - id объекта, obj_type_id - id типа объекта.
И при добавлении нового объекта будет во первых производится добавление в эту таблицу, получение obj_id объекта, а потом уже добавление данных в таблицы, содержащие поля.
2. Создается таблица с типами объектов:
-----
obj_type_id int,
obj_type_info varchar(100)
-----
obj_type_id - id типа объекта.
obj_type_id = 1 obj_type_info = "Книга"
obj_type_id = 2 obj_type_info = "Шкаф"
и т.п.

3. Создается таблица с типами полей.
-----
obj_param_id int,
obj_type_id int,
obj_param_name varchar(100),
obj_param_type int
-----
obj_param_id - id параметра объекта типа obj_type_id.
для obj_type_id = 1 obj_param_id = 1 obj_param_name="Автор" obj_param_id = 2 obj_param_name="Название"
для obj_type_id = 2 obj_param_id = 3 obj_param_name="Высота" obj_param_id = 4 obj_param_name="Ширина"
и т.п.
4.
Все отдельные поля объектов записываются в каждый в свою таблицу по типам, т.е. поля типа int будут лежать в таблице int_data, varchar(100) в таблице varchar100_data, float в таблице float_data и т.д Структура этих таблиц будет примерно такой:
------------
int_id int
obj_id int
obj_param_id int
val int
---
и т.д.
т.е. пусть имеется шкаф с obj_id=23, тогда,
obj_id=23 obj_param_id=3 val=56 //Высота
obj_id=23 obj_param_id=4 val=65 //Ширина

5.
Записывать данные в таблицы можно будет с помощью хранимой процедуры типа
update_field
@obj_id int,
@obj_param_name varchar(100)
@val sql_variant
AS
BEGIN
--Получение типа параметраobj_type_id
...............

--Обновление поля в зависимости от типа параметра
IF (@obj_type_id=1) //int
BEGIN
UPDATE int_data SET val=CONVERT(int, @val) WHERE obj_id=@obj_id AND obj_param_id=@obj_param_id
END
ELSE
IF (@obj_type_id=2) //float
BEGIN
...
END
END

Т.е. одной процедурой можно будет изменять все имеющиеся параметры всех объектов.

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

5.
Получать данные этих объектов можно будет с помощью представлений вида(для шкафа):
SELECT
obj_id,
tbl0.val AS heigh,
tbl1.val AS width
FROM objs
LEFT OUTER JOIN int_data AS tbl0 ON tbl0.obj_param_id=1 AND tbl0.obj_id = objs.obj_id
LEFT OUTER JOIN varchar100_data AS tbl1 ON tbl1.obj_param_id=2 AND tbl1.obj_id =

Т.е. для каждого объекта написать такое представления(view), в результате с ними можно будет работать как с обычными таблицами.

Связи между объектами я планирую сделать отдельными таблицами, что бы контролировать средствами сервера связи.


На данный момент я вижу несколько недостатков:
1. Уменьшается надёжность базы. (лишние связи, данные различной важности будут лежать в одной таблице, на одной таблице будет завязана вся база).
2. Уменьшается скорость работы.
3. Нет возможности использовать все встроенные в сервер средства контроля целостности данных.

Что скажете?

GreenSunriseНепонятно, зачем сущности совершенно разной природы сваливать по сути в одну плоскую таблицу (на самом деле в несколько - objs, objtypes, fieldstypes, и несколько таблиц по типам). То есть ВСЕ данные вы лишаете их семантики и храните единообразно. Зачем?


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

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

Проще назначать некие "закладки", или что-либо ещё, что будет относится к абсолютно любым объектам. Например, напоминание о том, что какой-либо объект куда-либо перемещен.

Если нужно добавить новый объект, то мне нужно просто добавить новые записи в таблицы настроек и этот объект появится в базе автоматически.


GreenSunrise
Недостатки:
1. Поскольку ваша структура не является отражением предметной области, то гораздо труднее писать бизнес-логику, то есть те вещи, которые семантически отражают действия в вашей предметной области.
2. Блокировки. Ужасы одновременного доступа. Фактически ЛЮБОЕ действие в системе приводит к обращению к одному и тому же крайне маленькому набору таблиц. Тут уже ничего не спасет - только в морг. Когда у вас начнут работать несколько пользователей, вы поймете, что сотворили :-)
3. Сделать внятные триггеры, процедуры, констрейнты, функции, вьюхи, внешние ключи - практически невозможно, поскольку см. п.1
4. Поддержка этого клубка макарон - проще повеситься сразу.
5. Вы пытаетесь активную директорию проимитировать? ;-)

Нет, я пытаюсь упростить себе дальнейшую работу. Но знаний мало, и поэтому я решил узнать, что думают по этому поводу профессионалы.

А вообще, если оставить все объекты в отдельных таблицах, то как мои задачи решать правильно?
Правильно все же оставить единый id объекта и под каждый тип объекта писать свою процедуру на обновление, для того, что бы сохранить "отображение предметной области"?
Или как?
Как вообще можно несколько унифицировать структуру базы для моего случая?

mirochik
Как вообще можно несколько унифицировать структуру базы для моего случая?

имелось ввиду унифицировать доступ и работу со структурой.

Да, я собирался делать именно так.
А как правильно?
Как в моем случае правильно решить поставленные задачи?

zayac mirochikПроще назначать некие "закладки", или что-либо ещё, что будет относится к абсолютно любым объектам. Например, напоминание о том, что какой-либо объект куда-либо перемещен.

Если нужно добавить новый объект, то мне нужно просто добавить новые записи в таблицы настроек и этот объект появится в базе автоматически.
Операции по модификации структуры таблиц необходимо сводить к минимуму. То что я создал - это от безысходности. Не я устанавливал правила. А создание... ну продумать структуру и создавать create table.

mirochikЯ хочу автоматизировать протоколирование изменения полей таблиц, т.е. что бы изменения вносились автоматически и что бы это можно было бы сделать в одном месте, например, в процедуре update_field, можно добавить строчку, заносящую в протокол информацию об измененных полях.
Как по мне, так аудит - это задача к которой необходимо подходить очень осторожно. Как правило аудита требуют далеко не все таблицы, а в тех что требуют - далеко не все поля. Продумайте на какие таблицы необходимо повесить тригера и готовтесь к неприятному сюрпризу - размер таблиц в которые будут идти результаты аудита будет увеличиваться очень быстро. А протоколировать изменения всего-всего-всего... вы потом не разберете где меняли "ЧП Иваноф" на "ЧП Иванов", а где остатки на счете этого ЧП.


Т.е. структура базы должна быть с минимальными наворотами и максимально возможно отражать подобие физических объектов? Т.е. если есть какой-либо объект, обладающей 10тью полями, то все этим поля по возможности нужно сложить в одну запись отдельной (только для этого типа объектов) таблицы? Если нужна связь, то сделать таблицу связи только для этого объекта. Если нужен новый тип объекта, то нужно создать новую таблицу для него. Писать под каждый объект хранимые процедуры, отвечающие только за этот объект и т.п.? Все серьёзные базы сделаны именно так?

Что касается аудита(в моем случае я бы назвал это просто историей изменений), то все данные вводятся вручную, если я введу единый номер объекта, и будут записывать информацию о том, кто когда что и на что изменил поле этого объекта, то сделать выборку по этому объекту(именно объекту, а не полю) большого труда не составит. Если все изменения в базе будут производиться через хранимые процедуры, то это реализовать сохранение истории не сложно. Триггеры, как я рассчитывал, для этого потребоваться не должны, хотя… я уже не знаю:)
А единый номер объекта тоже нежелательно использовать? Если, допустим, реализовывать все объекты в отдельных таблицах, но ключевое поле объекта все же держать в отдельной таблице, что бы получить возможность сохранять и просматривать историю через единый интерфейс, а также, создать единый механизм для контроля перемещений объектов и т.п.
Или это тоже не правильно?

Я также думал, что при описанной мной выше структуре, в конечном счете можно будет реализовать связывание и "развязывание" объектов с помощью опять же одной подпрограммы. Что значительно упростило бы процесс написания программ, ускорило и т.п.

Вот.