Bravenec
Informační systémy
Datový návrh v databázi PostgreSQL - vytvoření datového návrhu.
I když jsem v úvodním článku psal, že podvojné účetnictví je jednoduchá záležitost, jeho spolehlivá a bezpečná implementace v počítači vyžaduje od relačního databázového systému některé základní vlastnosti - referenční integritu a transakce. Oblíbená databáze MySQL z hlediska těchto požadavků pravděpodobně nevyhovuje (nevím, nepracuji s ní). Jistěže lze podvojné účetnictví implementovat i nad jednodušší databází, ale většina rozhodování se tak přesune do implementační vrstvy, což je velmi pracné a vede k velkým redundancím v kódu, a ošetření některých chybových stavů bez transakcí si nedovedu představit vůbec.
Přenechejme tedy nejrychlejší Linuxovou SQL databázi webovým designérům, kteří nepočítají peníze ale přístupy na stránky, a sáhněme po open source databázi PostgreSQL.
Základ podvojného účetnictví lze rozdělit do několika tabulek. Základem pro nás budiž osnova:
create table ei_osnova (
ucet int4,
anal int4,
popis varchar(128),
aktivni float8 not null check (aktivni=1.0 or aktivni=-1.0),
primary key (ucet, anal)
);
Komentář zasluhuje položka "aktivni", která může obsahovat hodnotu 1 nebo -1 a slouží k optickému obrácení znaménka u pasivních účtů. Použití nalezne později při vytváření sestav z účetnictví.
Hlavní kniha už není samostatná tabulka, ale je závislá na osnově. V hlavní knize se nesmí objevit žádný účet neexistující v osnově a zároveň z osnovy nesmí zmizet žádný účet, k němuž existuje v hlavní knize záznam. To nám zajišuje mechanismus referenční integrity:
create table ei_hlkniha (
ucet int4 not null,
anal int4 not null,
poc_zustatek_md float8 not null default 0,
poc_zustatek_dal float8 not null default 0,
zustatek_md float8 not null default 0,
zustatek_dal float8 not null default 0,
foreign key (ucet, anal) references ei_osnova(ucet, anal)
on update no action on delete no action
);
Položky zustatek_md a zustatek_dal se mění při každém zápisu do účetnictví. Počáteční zůstatky se nastavují v účetnictví pouze jednou, při otevírání nového účetního období. Zapisují se sem zůstatky z hlavní knihy po účetní uzávěrce. Při běžném účtování se počáteční zůstatky nemění.
Účetní deník v digitální implementaci nebudeme rozdělovat do jednotlivých účtů, ale použijeme jedinou tabulku. Protože do deníku se zapisují vždy nejméně dva záznamy na jednu účetní transakci, zapisovaly by se některé údaje dvakrát. Je tedy lépe deník rozdělit do dvou tabulek - ei_popis a ei_denik. Samozřejmě mezi nimi musí být vzájemná vazba:
create table ei_popis (
cislo int4 primary key,
popis varchar(128) not null
);
create table ei_denik (
cislo int4 not null references ei_popis(cislo)
on update no action on delete no action,
datum date not null default now(),
mdd char(1) check (mdd='M' or mdd='D'),
ucet int4,
anal int4,
doklad varchar(64),
castka float8 not null default 0,
foreign key (ucet, anal) references ei_osnova(ucet, anal)
on update no action on delete no action
);
create rule ei_popis_nodelete
as on delete to ei_popis
do instead nothing;
create rule ei_denik_nodelete
as on delete to ei_denik
do instead nothing;
Při každé účetní operaci se budou do tabulky ei_denik zapisovat vždy nejméně dva záznamy. Jeden pro stranu má dáti, druhý pro stranu dal. Strana účtu je uvedena v položce mdd - zde se může objevit písmeno M nebo D. Databáze sama hlídá, aby se nedalo zapsat nic jiného. Číslo záznamu je svázáno s tabulkou ei_popis - před zápisem do deníku je tedy potřeba zapsat záznam do tabulky ei_popis. Integritní omezení zároveň hlídá, aby nebylo možné zapsat do deníku záznam o neexistujícím účtu, případně aby se nedal smazat nebo změnit údaj v osnově, k němuž v deníku existují nějaké záznamy. Pravidlem v databázi je zajištěno, že jednou zapsané údaje nelze mazat (to je nutnost daná zákonem o účetnictví).
Podvojné účetnictví vyžaduje, aby všechny účetní operace byly číslované nepřerušovanou vzestupnou řadou. Pro uschování čísla dokladu vytvoříme tabulku ei_poradi. V tabulce bude vždy jen jediná věta. Aby se nedaly do tabulky zapsat další věty, je zápis a mazání odmítnuto pravidlem vytvořeným nad tabulkou:
create table ei_poradi (
cislo int4 not null
);
insert into ei_poradi values (1);
create rule ei_poradi_noinsert
as on insert to ei_poradi
do instead nothing;
create rule ei_poradi_nodelete
as on delete to ei_poradi
do instead nothing;
Jako základ pro počítání peněz tato datová struktura stačí. Aniž bychom napsali jediný výkonný příkaz, zajistili jsme si definicí tabulek i základní kontroly. Už v takto navržené databázi se nemůže stát, že by se podařilo zapsat do databáze vyložené nesmysly. Můžeme zkusit založit několik účtů a vyzkoušet účtování:
-- úvodní příprava databáze
insert into ei_osnova values (221,100,'Komerční banka, běžný účet', 1);
insert into ei_osnova values (600,100,'Výplata',-1);
insert into ei_hlkniha (ucet, anal) values (221,100);
insert into ei_hlkniha (ucet, anal) values (600,100);
-- začátek účetní transakce
begin;
lock ei_poradi;
select cislo from ei_poradi;
insert into ei_popis (cislo, popis)
values ((select cislo from ei_poradi), 'Zkouška');
insert into ei_denik (cislo, mdd, ucet, anal, castka)
values ((select cislo from ei_poradi),
'M', 221, 100, 24000);
insert into ei_denik (cislo, mdd, ucet, anal, castka);
values ((select cislo from ei_poradi),
'D', 600, 100, 24000);
update ei_hlkniha set zustatek_md=zustatek_md+24000
where ucet=221 and anal=100;
update ei_hlkniha set zustatek_dal=zustatek_dal+24000
where ucet=600 and anal=100;
update ei_poradi set cislo=cislo+1;
commit;
Číslo účetní operace se dozvíte až po zahájení transakce a po uzamčení tabulky ei_poradi. Sáhneme-li do tabulky dříve, před příkazem begin a uzamčením tabulky, nelze ve víceuživatelském prostředí zajitit, že dostanete právě takové číslo operace, jaké jste z tabulky před chvílí přečetli.
Velkým ulehčením práce je uzavření celé operace do jedné transakce. Nemusíte tak hlídat například existenci účtů. Pokud se vám stane, že se budete snažit zadat neexistující účet do tabulky ei_denik, vše, co bylo dosud v rámci transakce zapsané do databáze, bude zrušeno. Databáze bude bez programátorovy zásluhy ve stejném stavu jako před transakcí. Transakce spolu s referenční integritou pomáhá udržet databázi v konzistenci. Přesto lze náš datový model ošetřit mnohem lépe pomocí triggerů a funkcí.