Добро пожаловать в форум, Guest  >>   Войти | Регистрация | Поиск | Правила | В избранное | Подписаться
Все форумы / Microsoft SQL Server Новый топик    Ответить
 Правильная структура таблицы  [new]
ferzmikk
Member

Откуда:
Сообщений: 2056
Здравствуйте!

Есть план на каждый год по месяцам. См. excel-ий файл. План состоит из иерархических статей. Конечные статьи, которая не имеет под статьи, можно корректировать. Не конечные статьи, которые имеют под статьи, пересчитываются автоматически.

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

Но нужно учесть следующую логику. Например, в 2017 году в справочнике была сформирована иерархия статей.
1. В 2018 году одна статья удалилась, но в 2017 году не должна удаляться.
2. В 2018 году появилась статья, но в 2017 не должна присутствовать.
3. В 2018 году статья перенаименовалась, но в 2017 году должна остаться как предыдущее наименование.
4. В 2018 году статья изменила своего родителя (то есть была перемещена), в 2017 году не должна перемещаться.
5. Есть статья с кодом строки 7 (см excel файл), В нее входят Статья 1.2.1 и Статья 1.2.2, но не входят Статья 1.2.3 и Статья 1.2.4.
6. Структура статей для всех городов и указанного года абсолютно одинаковая.
7. Определенная статья 2018 года соответствует статье 2017 года. То есть Статья 1.1.1 за 2018 год соответствует Статья 1.1.1 за 2017 год.

Для 1-4 пунктов изменение в 2018 г. может быть как и в начале года, как и в середине года, так и в конце года.

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

+Имеется SQL-запрос с исходными данными.
Declare @ТаблицаСправочник table(
	[Код статьи] integer,
	[Текстовый код статьи] varchar (20),
	[Наименование статьи] varchar(20),
	[Родитель] integer,
	[Присутствие подстатьи] bit
)
;
INSERT INTO
  @ТаблицаСправочник 
VALUES 
(1,'1','Статья 1',null,1),
(2,'1.1','Статья 1.1',1,1),
(3,'1.1.1','Статья 1.1.1',2,0),
(4,'1.1.2','Статья 1.1.2',2,0),
(5,'1.1.3','Статья 1.1.3',2,0),
(6,'1.2','Статья 1.2',1,1),
(7,'1.2.1','Статья 1.2.1',6,0),
(8,'1.2.2','Статья 1.2.2',6,0),
(9,'1.2.3','Статья 1.2.3',6,0),
(10,'1.2.4','Статья 1.2.4',6,0),
(11,'1.3','Статья 1.3',1,0),
(12,'2','Статья 2',null,0)
;

Declare @ТаблицаДокумент table(
	[Код] integer,
	[Город] Varchar(20),
	[Код статьи] integer,
	[Год] integer,
	[Январь] numeric (8,3),
	[Февраль] numeric (8,3),
	[Март] numeric (8,3),
	[Апрель]numeric (8,3),
	[Май]numeric (8,3),
	[Июнь]numeric (8,3),
	[Июль]numeric (8,3),
	[Август]numeric (8,3),
	[Сентябрь]numeric (8,3),
	[Октябрь]numeric (8,3),
	[Ноябрь]numeric (8,3),
	[Декабрь]numeric (8,3)
)
;

INSERT INTO
  @ТаблицаДокумент 
VALUES 
(1,'Город1',1,2017,Null,Null,Null,Null,Null,Null,Null,Null,Null,Null,Null,Null),
(2,'Город1',2,2017,Null,Null,Null,Null,Null,Null,Null,Null,Null,Null,Null,Null),
(3,'Город1',3,2017,10,Null,Null,Null,Null,Null,Null,Null,Null,Null,Null,Null),
(4,'Город1',4,2017,20,Null,Null,Null,Null,Null,Null,Null,Null,Null,Null,Null),
(5,'Город1',5,2017,30,Null,Null,Null,Null,Null,Null,Null,Null,Null,Null,Null),
(6,'Город1',6,2017,Null,Null,Null,Null,Null,Null,Null,Null,Null,Null,Null,Null),
(7,'Город1',7,2017,Null,Null,Null,Null,Null,Null,Null,Null,Null,Null,Null,Null),
(8,'Город1',8,2017,40,Null,Null,Null,Null,Null,Null,Null,Null,Null,Null,Null),
(9,'Город1',9,2017,50,Null,Null,Null,Null,Null,Null,Null,Null,Null,Null,Null),
(10,'Город1',10,2017,60,Null,Null,Null,Null,Null,Null,Null,Null,Null,Null,Null),
(11,'Город1',11,2017,70,Null,Null,Null,Null,Null,Null,Null,Null,Null,Null,Null),
(12,'Город1',12,2017,80,Null,Null,Null,Null,Null,Null,Null,Null,Null,Null,Null),
(13,'Город1',13,2017,90,Null,Null,Null,Null,Null,Null,Null,Null,Null,Null,Null)

SELECT
	Таб1.[Код],
	Таб1.[Город],
	Таб1.[Код статьи],
	Таб2.[Текстовый код статьи],
	Таб2.[Наименование статьи],
	Таб2.[Родитель],
	Таб2.[Присутствие подстатьи],
	Таб1.[Год],
	Таб1.[Январь],
	Таб1.[Февраль],
	Таб1.[Март],
	Таб1.[Апрель],
	Таб1.[Май],
	Таб1.[Июнь],
	Таб1.[Июль],
	Таб1.[Август],
	Таб1.[Сентябрь],
	Таб1.[Октябрь],
	Таб1.[Ноябрь],
	Таб1.[Декабрь]	
FROM
	@ТаблицаДокумент Таб1
Left JOIN
	@ТаблицаСправочник Таб2
ON
	Таб1.[Код статьи] = Таб2.[Код статьи]
;

Возникают следующие вопросы:
1. Скажите, для данной задачи нужно отделять эти две таблицы или лучше в одну? Следует учесть, что если толкать в одну таблицу и если создавать новые строки для 2019 года, то важно чтобы для удобства справочные строки можно скопировать за 2018 г и скорректировать структуру статей для 2019 года. Возможно будет нужно создавать отдельную процедуру.
2. Для автоматического перерасчета родительских строк нужно использовать триггеры или функцию? Учитывая что нужно делать перебор строк. Если изменена родительская строка и это строка имеет своего родителя, то и родитель тоже корректируется.
3. По пятому пункту из описания логики как прописать родитетелей? То есть если к таким элементам как Статья 1.2.1, Статья 1.2.2, Статья 1.2.3 и Статья 1.2.4. указать родителя как Статья 1.2, то статьям Статья 1.2.1, Статья 1.2.2 как указать родителя Статья 1.2 ( Статья 1.2.1 + Статья 1.2.2).
4. Для данной задачи что нужно учесть еще в плане структуры? Нужно ли в справочной таблице поле Присутствие подстатьи или достаточно поля Родитель?

К сообщению приложен файл (Справочная таблица.xlsx - 9Kb) cкачать
12 фев 18, 12:18    [21184992]     Ответить | Цитировать Сообщить модератору
 Re: Правильная структура таблицы  [new]
Дедушка
Member

Откуда: Город трёх революций
Сообщений: 5111
- я бы делал две таблицы (дерево и его значения)
- если дерево локализовано в рамках одного года и все расчёты внутри одного года, то есть смысл подумать над разделением этих таблиц по годам (секционирование\физическое разделение)
- вам нужна версионность структуры дерева привязанная к году
- попробуйте реализацию дерева через hierarchyID
- чем реализовать перерасчёты и изменения структуры зависит от вашей системы (частота изменений, способ обработки ошибок... и тп.)
12 фев 18, 12:45    [21185118]     Ответить | Цитировать Сообщить модератору
Все форумы / Microsoft SQL Server Ответить