1

Topic: The correct structure of the table

Hello!
There is a plan on every year on months. Excel th file see. The plan consists of hierarchical articles. Finite articles which has no under articles, it is possible to adjust. Not finite articles which have under articles, are enumerated automatically.
There are two tables. The table as the reference manual with brought all articles where for each article it is specified Parent. And also the Table as the document where on years articles and appropriate values on months are fixed.
But it is necessary to consider following logic . For example, in 2017 in the reference manual the hierarchy of articles has been generated.
1. In 2018 one article was deleted, but in 2017 should not be deleted.
2. In 2018 there was article, but in 2017 should not be present.
3. In 2018 article , but in 2017 should remain as the previous name.
4. In 2018 article changed the parent (that is has been moved), in 2017 should not be moved.
5. There is article with the code of a line 7 (sm excel a file), are included Into it Article 1.2.1 and Article 1.2.2 , but do not enter Article 1.2.3 and Article 1.2.4 .
6. Structure of articles for all cities and the specified year absolutely identical.
7. Certain article 2018 corresponds to article 2017. That is article 1.1.1 for 2018 corresponds Article 1.1.1 for 2017.
For 1-4 points change in 2018 can be as well as in the beginning of year, as well as in the middle of the year, and in the end of the year.
Proceeding from the described logic it is necessary to register structure so that at change of finite article, the parent were enumerated.
[spoiler There is a SQL query with the initial data.] [code]
Declare @ table (
[Article code] integer;
[The text code of article] varchar (20);
[Article name] varchar (20);
[Parent] integer;
[Subentry presence] bit
)
;
INSERT INTO
@
VALUES
(1, ' 1 ', ' Article 1 ', null, 1);
(2, ' 1.1 ', ' Article 1.1 ', 1,1);
(3, ' 1.1.1 ', ' Article 1.1.1 ', 2,0);
(4, ' 1.1.2 ', ' Article 1.1.2 ', 2,0);
(5, ' 1.1.3 ', ' Article 1.1.3 ', 2,0);
(6, ' 1.2 ', ' Article 1.2 ', 1,1);
(7, ' 1.2.1 ', ' Article 1.2.1 ', 6,0);
(8, ' 1.2.2 ', ' Article 1.2.2 ', 6,0);
(9, ' 1.2.3 ', ' Article 1.2.3 ', 6,0);
(10, ' 1.2.4 ', ' Article 1.2.4 ', 6,0);
(11, ' 1.3 ', ' Article 1.3 ', 1,0);
(12, ' 2 ', ' Article 2 ', null, 0)
;
Declare @ table (
[code] integer;
[City] Varchar (20);
[Article code] integer;
[Year] integer;
[January] numeric (8,3);
[February] numeric (8,3);
[March] numeric (8,3);
[April] numeric (8,3);
[May] numeric (8,3);
[June] numeric (8,3);
[July] numeric (8,3);
[August] numeric (8,3);
[September] numeric (8,3);
[October] numeric (8,3);
[November] numeric (8,3);
[December] numeric (8,3)
)
;
INSERT INTO
@
VALUES
(1, ' Gorod1',1,2017,Null,Null,Null,Null,Null,Null,Null,Null,Null,Null,Null,Null);
(2, ' Gorod1',2,2017,Null,Null,Null,Null,Null,Null,Null,Null,Null,Null,Null,Null);
(3, ' Gorod1',3,2017,10,Null,Null,Null,Null,Null,Null,Null,Null,Null,Null,Null);
(4, ' Gorod1',4,2017,20,Null,Null,Null,Null,Null,Null,Null,Null,Null,Null,Null);
(5, ' Gorod1',5,2017,30,Null,Null,Null,Null,Null,Null,Null,Null,Null,Null,Null);
(6, ' Gorod1',6,2017,Null,Null,Null,Null,Null,Null,Null,Null,Null,Null,Null,Null);
(7, ' Gorod1',7,2017,Null,Null,Null,Null,Null,Null,Null,Null,Null,Null,Null,Null);
(8, ' Gorod1',8,2017,40,Null,Null,Null,Null,Null,Null,Null,Null,Null,Null,Null);
(9, ' Gorod1',9,2017,50,Null,Null,Null,Null,Null,Null,Null,Null,Null,Null,Null);
(10, ' Gorod1',10,2017,60,Null,Null,Null,Null,Null,Null,Null,Null,Null,Null,Null);
(11, ' Gorod1',11,2017,70,Null,Null,Null,Null,Null,Null,Null,Null,Null,Null,Null);
(12, ' Gorod1',12,2017,80,Null,Null,Null,Null,Null,Null,Null,Null,Null,Null,Null);
(13, ' Gorod1',13,2017,90,Null,Null,Null,Null,Null,Null,Null,Null,Null,Null,Null)
SELECT
1. [code];
1. [City];
1. [Article code];
2. [The text code of article];
2. [Article name];
2. [Parent];
2. [Subentry presence];
1. [Year];
1. [January];
1. [February];
1. [March];
1. [April];
1. [May];
1. [June];
1. [July];
1. [August];
1. [September];
1. [October];
1. [November];
1. [December]
FROM
@TablitsaDokument Tab1
Left JOIN
@TablitsaSpravochnik Tab2
ON
1. [Article code] = Tab2. [Article Code]
;
[/code] [/spoiler]
There are following questions:
1. Tell, for the given task it is necessary to separate these two tables or it is better in one? It is necessary to consider that if to push in one table and if to create new lines for 2019 it is important that for convenience reference lines can be copied for 2018  and to correct structure of articles for 2019. Probably it will be necessary to create separate procedure.
2. For automatic recomputation of parent lines it is necessary to use triggers or function? Considering that it is necessary to do search of lines. If the parent line is changed and it is a line has the parent also the parent too is adjusted.
3. On the fifth point from the logic description how to register ? That is if to such elements as Article 1.2.1 , Article 1.2.2 , Article 1.2.3 and Article 1.2.4. to specify the parent as Article 1.2 , to articles Article 1.2.1 , Article 1.2.2 how to specify the parent Article 1.2 (Article 1.2.1 + Article 1.2.2) .
4. For the given task that it is necessary to consider in respect of structure? Whether the field subentry Presence or enough a field the Parent is necessary in the reference table?

2

Re: The correct structure of the table

- I would do two tables (a tree and its values)
- If the tree is localized within the limits of one year and all calculations in one year, that is the sense to think over sharing of these tables on years (sectioning / physical sharing)
- To you it is necessary  tree structures anchored to a year
- Try tree implementation through hierarchyID
- Than to implement recomputations and structure changes depends on your system (frequency of changes, an error handling method... And .)