Студопедия

КАТЕГОРИИ:

АстрономияБиологияГеографияДругие языкиДругоеИнформатикаИсторияКультураЛитератураЛогикаМатематикаМедицинаМеханикаОбразованиеОхрана трудаПедагогикаПолитикаПравоПсихологияРиторикаСоциологияСпортСтроительствоТехнологияФизикаФилософияФинансыХимияЧерчениеЭкологияЭкономикаЭлектроника


Пример проктирования базы данных




Во многих системах нам приходится вести учёт движения денежных средств или материально-товарных ценностей. Естественно, к таковым относятся приложения типа "Бухгалтерия", "Склад" и "Зарплата". Одной из наиболее часто используемых в таких системах абстракций является план счетов.

Необходимо отметить, что мы предполагаем, что существует операция создания проводки, равно как и операция удаления проводки, но не существует операции её модификации. Мы также предполагаем, что счета не изменяют своего расположения относительно друг друга.

 

Построение концептуальной модели

План счетов представляет собой строго иерархическую структуру счетов.

Счёт может быть либо папкой, которая подразделяется на другие подсчета (назовём его счётом-папкой), либо конечным счётом (назовём его просто счётом). Мы также можем выделить ещё один дискриминатор –- подразделение на счета, являющиеся корнями дерева счетов (корневой счёт), и на счета, являющиеся составными частями других счетов (подсчёт). Подсчёта являются в определённом смысле частью счёта-папки, но их жизненный цикл не обязательно совпадает с жизненным циклом содержащего их счёта-папки, что выражается отношением агрегации.

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

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

На рисунке 4.33 приведена диаграмма классов UML, отображающая представленные абстракции.

Кроме того, счета подразделяются по разделу балансового учёта. Счёта подразделяются на активные, пассивные и активно-пассивные счета. Кроме того, счета подразделяются на балансовые и забалансовые счета. В частности, баланс по всему плану счетов можно определить как сумму балансов конечных балансовых счетов. Известно, что активные счета могут включать в себя только активные подсчета, а пассивные счета могут включать в себя только пассивные подсчета. Забалансовые счета обычно определяются как активно-пассивные, но жесткого соответствия нет.

 

Рисунок 4.33 – Иерархия счётов и проводок, разрез «План счетов

На рисунке 4.34 приведена диаграмма классов UML, отображающая представленные абстракции.

Рисунок 4.34 – Иерархия счётов и проводок, разрез «Разделы учёта»

Все проводки создаются в рамках хозяйственных операций. Каждая хозяйственная операция может содержать несколько проводок, относящихся к разным счетам, однако выполненных в рамках одного временного периода расчёта. Соотвественно, каждой хозяйственной операции соответствует атрибут «период, на который выполняется расчёт».

На рисунке 4.35 приведена диаграмма классов UML, отображающая представленные абстракции.

Рисунок 4.35 – Иерархия счётов и проводок, разрез «Хозяйственные операции и проводки»

Возможные расширения модели

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

Например, основные хозяйственные операции по начислению зарплаты являются экземплярами типа, чьим супертипом является тип «Хозяйственная операция». Естественно, они имеют дополнительные атрибуты, имеющие смысл только для хозяйственных операций по начислению зарплаты. Так, для таких операций необходимо различать дату начисления и месяц расчёта.

В частности, были выполнены такие расширения плана счетов, как иерархия хозяйственных операций (рисунок 4.36) и персонализированные счета (рисунок 4.37).

Рисунок 4.36 –.Расширение плана счетов, разрез «Иерархия хозяйственных операций»

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

Рисунок 4.37 – Пример взаимосвязи конкретного счёта с работником предприятия

Персонализированные счета также является весьма частым случаем для бухгалтерских систем. В частности, расчёт зарплаты производится на счета, ассоциированные с конкретными сотрудниками. Вот пример взаимосвязи счёта 70/ШТ/АНА с работником Алексейцевой Н. А.

Декларирование непосредственной связи между счётом и работником не только важно для уточнения спецификации, но также позволит избежать аномалий обновления, а также позволит реализовать для бухгалтера точки зрения «План счетов к Работникам» и «Работники и ассоциированные счета».

Для многих бухгалтерских систем каждая проводка может содержать один или несколько различных признаков аналитического учёта. Хозяйственные операции могут выполняться в рамках исполнения бухгалтерских документов (рисунок 4.38).

Рисунок 4.38 – Расширение плана счетов, разрез «Персонализированные счета»

Уточнение логической модели

На этом этапе мы уточняем набор атрибутов типов (с точностью до наименования) и набор ограничений.

Каждый счёт в нашей модели имеет код и дисплейную метку. Полный код уникален. Конечные счета имеют значения дебетового и кредитового остатков на момент «начало времён». Проводки в общем случае имеют атрибуты Количество единиц и Стоимость одной единицы, а также производный атрибут /Сумма проводки. Кроме того, мы определяем атрибут Дата создания проводки (рисунки 4.397, 4.40).

Далее мы переходим от диаграмм классов UML (точка зрения спецификации) к классическим ER-диаграммам. Отношения наследования преобразуем в отношения подкатегории, ассоциации и агрегации преобразуем в неидентифицирующие отношения.

Всем сущностям, перенесённым из логической модели в физическую, назначаются суррогатные ключи (смотрите статью "Естественные ключи против искусственных ключей" Анатолия Тенцера) типа AnIdentifier.

Рисунок 4.39 – Иерархия счётов и проводок, разрез «План счетов»

Рисунок 4.40 – Иерархия счётов и проводок, разрез «Хозяйственные операции и проводки»

Упрощение модели

Хотелось бы сразу отметить два способа упрощения модели. Не рекомендуется прибегать к ним на этапе предварительного проектирования БД. Обычно упрощение модели производится в последней трети процесса отображения логической модели на физическую модель.

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

Таким образом, мы можем преобразовать по данному правилу в физической модели типы Активный счёт, Пассивный счёт и Активно-пассивный счёт, равно как и типы Балансовый счёт и Забалансовый счёт. Соответствующие этим типам сущности в нотации ER будут помечены как существующие только в логической модели.

В типе Произвольный счёт мы разместим логические атрибуты-дискриминанты ЭтоАктивныйСчёт, ЭтоПассивныйСчёт, ЭтоБалансовыйСчёт.

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

В частности, подобному сокращению в вышеприведённых диаграммах можно прибегнуть в случае с типом Корневой счёт.

Результирующая логическая модель

Сводная ER-диаграмма для иерархии счётов и проводок приведена на рисунке 4.41.

Рисунок 4.41 – Иерархия счётов и проводок, сводная ER-диаграмма, логическая модель данных

 

 

Построение физической модели

Все типы данных определяются как поименованные домены, все типы обозначаются как NOT NULL. Для всех символьных типов устанавливаем пустую строку как значение по умолчанию. Логический тип определяем как INTEGER NOT NULL CHECK(VALUE BETWEEN 0 AND 1) DEFAULT 0;

В частности, коды и дисплейные метки счетов мы определяем как VARCHAR, стоимость единицы проводки как DOUBLE PRECISION (, количество единиц проводки как INTEGER

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

В качестве целевого сервера здесь мы рассматриваем InterBase SQL Server корпорации Interbase. Для построения модели данных мы воспользуемся CASE-инструментарием Platinum ERwin корпорации Platinum.

Полученная физическая модель может быть принята как базис для проработки физических моделей для других СУБД. В частности, эта модель была успешно перенесена на Microsoft SQL Server 7.0.

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

Существующие ограничения мы дополним такими ограничениями:

CREATE EXCEPTION AK_ACCOUNTCANNOTBEAFOLDER "Cannot make this account as folder because it's leaf"; SET TERM !! ;CREATE TRIGGER TI_AnAccountFolder_Restrict1 FOR AnAccountFolderAFTER INSERTAS DECLARE VARIABLE NewId INTEGER;BEGIN NewId = NEW.Id; IF( EXISTS( SELECT * FROM AnAccount WHERE Id = :NewId ) ) THEN BEGIN EXCEPTION AK_ACCOUNTCANNOTBEAFOLDER; ENDEND !!SET TERM ; !! CREATE EXCEPTION AK_ACCOUNTCANNOTBEALEAF "Cannot make this account as leaf because it's folder"; SET TERM !! ;CREATE TRIGGER TI_AnAccount_Restrict1 FOR AnAccountAFTER INSERTAS DECLARE VARIABLE NewId INTEGER;BEGIN NewId = NEW.Id; IF( EXISTS( SELECT * FROM AnAccountFolder WHERE Id = :NewId ) ) THEN BEGIN EXCEPTION AK_ACCOUNTCANNOTBEALEAF; ENDEND !!SET TERM ; !! COMMIT;

Расширение модели

Поскольку мы используем иерархическую структуру счетов не только для показа иерархии, но и для финансовых расчётов согласно иерархии счетов, нам потребуется оптимизировать обработку иерархии счетов с помощью дополнительной функциональности. Воспользуемся шаблоном "Реализация древовидных структур данных" Анатолия Тенцера.

Нам потребуется в физической модели ввести новую таблицу "Наследование счёта", которая связана идентифицирующими отношениями с сущностями "Счёт" и "Счёт-папка". Для каждого счёта в этой таблице будет перечислены все его владельцы (прямой владелец счёта, владелец владельца и так далее). Немного подумав о получающихся запросах, мы можем придти к выводу, что помимо владельцев данного счёта, мы можем хранить в ней ещё и сам счёт. Тогда, правда, нам придётся несколько изменить определение таблицы, которая теперь будет дважды связана идентифицирующими отношениями с таблицей "Произвольный счёт".

 

 

Тогда, например, операция расчёта дебетового остатка конкретного счёта будет записана так:

 

CREATE PROCEDURE GetDebitBalanceOn(AccountId INTEGER, ReqDate DATE) RETURNS(DebitBalance DOUBLE PRECISION)AS DECLARE VARIABLE TheInitialValue DOUBLE PRECISION; DECLARE VARIABLE TheSum DOUBLE PRECISION;BEGIN SELECT SUM(A.InitialDebitValue) FROM AnAccount A INNER JOIN AccountInheritance AI ON (A.Id = AI.SubAccountId) WHERE (AI.AccountFolderId = :AccountId) INTO :TheInitialValue; IF( TheInitialValue IS NULL ) THEN EXIT; SELECT SUM(AMovement.Quantity * AMovement.UnitCost) FROM AMovement INNER JOIN AnEconomicOperation EOp ON (AMovement.EconomicOperationId = EOp.Id) WHERE (AMovement.DebitAccountId IN (SELECT SubAccountId FROM AccountInheritance WHERE (AccountFolderId = :AccountId))) AND (EOp.BeginsWith < :ReqDate) INTO :TheSum; IF ( TheSum IS NULL ) THEN TheSum = 0; DebitBalance = TheInitialValue + TheSum;END

Бывает, что соединения таблиц выполняются SQL-серверами более эффективно, чем вложенные запросы. Соответственно, вложенный запрос в этой хранимой процедуре можно переписать как соединение таблиц, однако, во-первых, оптимизаторы многих SQL-серверов в таком случае автоматически производят реинтерпретацию, во-вторых, подобные ручные оптимизации стоит проводить, только если производительность сервера оказывается недостаточной на рабочих объёмах данных.

Для поддержки актуальности таблицы "Наследование счёта" необходимо описать соответсвующие триггеры:

SET TERM !! ;CREATE PROCEDURE GetAccountParents(ASubAccountId INTEGER) RETURNS(AnAccountFolderId INTEGER)ASBEGIN /* Selects thread of parents of account from bottom to top */ WHILE( EXISTS(SELECT * FROM ASubAccount WHERE (Id = :ASubAccountId)) ) DO BEGIN SELECT AccountFolderId FROM ASubAccount WHERE (Id = :ASubAccountId) INTO :AnAccountFolderId; SUSPEND; ASubAccountId = AnAccountFolderId; ENDEND !!SET TERM ; !!SET TERM !! ;CREATE TRIGGER TI_AnAccount_Inherit FOR AnAccountAFTER INSERTAS DECLARE VARIABLE NewId INTEGER; DECLARE VARIABLE ParentId INTEGER;BEGIN NewId = NEW.Id; INSERT INTO AccountInheritance(AccountFolderId, SubAccountId) VALUES(:NewId, :NewId); FOR SELECT AnAccountFolderId FROM GetAccountParents(:NewId) INTO :ParentId DO BEGIN INSERT INTO AccountInheritance(AccountFolderId, SubAccountId) VALUES(:ParentId, :NewId); ENDEND !!SET TERM ; !! SET TERM !! ;CREATE TRIGGER TD_AnAccount_Inherit FOR AnAccountAFTER DELETEAS DECLARE VARIABLE OldId INTEGER;BEGIN OldId = OLD.Id; DELETE FROM AccountInheritance WHERE (SubAccountId = :OldId);END !!SET TERM ; !! COMMIT;

Кроме того, определим несколько представлений для удобства восприятия БД:

CREATE VIEW AnAccountTreeView (Id, AccountFolderId, Code, DisplayLabel) AS SELECT ACustomAccount.Id, ASubAccount.AccountFolderId, ACustomAccount.Code, ACustomAccount.DisplayLabel FROM ACustomAccount LEFT OUTER JOIN ASubAccount ON ( ACustomAccount.Id = ASubAccount.Id)

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

Специализация шаблонов физических объектов

CASE-инструментарий предоставляет возможность описывать шаблоны таких объектов БД, как хранимые процедуры и триггеры. Кроме того, можно описывать шаблоны скриптов.

Для независимых сущностей мы определяем генераторы для суррогатных ключей и хранимые процедуры для создания записей.

Шаблон CreateGenerator (PreScript For Table)

CREATE GENERATOR %TableNameGenerator%DBMSDelimSET GENERATOR %TableNameGenerator TO 0%DBMSDelim

 

Шаблон CreateRec (Stored Procedure For Table)

CREATE PROCEDURE Create%TableNameRec(%ForEachAtt() {%If( %Not( %AttIsPK) ) { %AttFieldName %AttPhysDatatype, } } Dummy INTEGER) RETURNS(Id INTEGER)ASBEGIN Id = GEN_ID(%TableNameGenerator, 1)%DBMSDelim INSERT INTO %TableName(%ForEachAtt() {%If( %Not( %AttIsPK) ) { %AttFieldName, } } Id) VALUES ( %ForEachAtt() {%If( %Not( %AttIsPK) ) { :%AttFieldName, } } :Id )%DBMSDelimEND

Создание деловых правил

В числе прочих мы можем создать, например, такие правила:

 

SET TERM !! ;CREATE PROCEDURE CreateAnAccountRec(Code VARCHAR(20), DisplayLabel VARCHAR(80), IsActive INTEGER, IsPassive INTEGER, InBalance INTEGER, ParentCode VARCHAR(20), Dummy INTEGER) RETURNS(Id INTEGER)AS DECLARE VARIABLE ParentAccountId INTEGER; DECLARE VARIABLE ParentInBalance INTEGER; DECLARE VARIABLE ParentIsActive INTEGER; DECLARE VARIABLE ParentIsPassive INTEGER;BEGIN IF( (IsActive = 0) AND (IsPassive = 0) ) THEN BEGIN IsActive = 1; IsPassive = 1; END SELECT Id, InBalance, IsActive, IsPassive FROM ACustomAccount WHERE Code = :ParentCode INTO :ParentAccountId, :ParentInBalance, :ParentIsActive, :ParentIsPassive; IF( ParentAccountId IS NULL ) THEN BEGIN ParentInBalance = InBalance; ParentIsActive = IsActive; ParentIsPassive = IsPassive; END EXECUTE PROCEDURE CreateACustomAccountRec(Code, DisplayLabel, ParentIsActive, ParentIsPassive, ParentInBalance, Dummy) RETURNING_VALUES(Id); /* check for parent account */ IF( (Id > 0) AND (ParentAccountId IS NOT NULL) ) THEN BEGIN /* check parent account for folder */ IF( NOT EXISTS( SELECT * FROM AnAccountFolder WHERE Id = :ParentAccountId ) ) THEN BEGIN INSERT INTO AnAccountFolder(Id) VALUES(:ParentAccountId); END INSERT INTO ASubAccount(Id, AccountFolderId) VALUES(:Id, :ParentAccountId); ENDEND !!SET TERM ; !! SET TERM !! ;CREATE PROCEDURE DoMovement(DebitAccountId INTEGER, CreditAccountId INTEGER, EcOpId INTEGER, Quantity INTEGER, UnitCost DOUBLE PRECISION) RETURNS(Id INTEGER)ASBEGIN IF( NOT EXISTS( SELECT * FROM AnAccount WHERE Id = :DebitAccountId ) ) THEN BEGIN INSERT INTO AnAccount(Id, InitialDebitValue, InitialCreditValue) VALUES(:DebitAccountId, 0.0, 0.0); END IF( NOT EXISTS( SELECT * FROM AnAccount WHERE Id = :CreditAccountId ) ) THEN BEGIN INSERT INTO AnAccount(Id, InitialDebitValue, InitialCreditValue) VALUES(:CreditAccountId, 0.0, 0.0); END EXECUTE PROCEDURE CreateAMovementRec(EcOpId, CreditAccountId, DebitAccountId, 'NOW', Quantity, UnitCost, 0) RETURNING_VALUES(ID);END !!SET TERM ; !! SET TERM !! ;CREATE PROCEDURE DoMovementByCode(DebitAccountCode VARCHAR(20), CreditAccountCode VARCHAR(20), EcOpId INTEGER, Quantity INTEGER, UnitCost DOUBLE PRECISION) RETURNS(Id INTEGER)AS DECLARE VARIABLE DebitAccountId INTEGER; DECLARE VARIABLE CreditAccountId INTEGER;BEGIN SELECT Id FROM ACustomAccount WHERE Code = :DebitAccountCode INTO :DebitAccountId; SELECT Id FROM ACustomAccount WHERE Code = :CreditAccountCode INTO :CreditAccountId; EXECUTE PROCEDURE DoMovement(DebitAccountId, CreditAccountId, EcOpId, Quantity, UnitCost) RETURNING_VALUES(ID);END !!SET TERM ; !! SET TERM !! ;CREATE PROCEDURE GetDebitBalanceAllOn(ReqDate DATE) RETURNS(DebitBalance DOUBLE PRECISION)AS DECLARE VARIABLE TheInitialValue DOUBLE PRECISION; DECLARE VARIABLE TheSum DOUBLE PRECISION;BEGIN SELECT SUM(A.InitialDebitValue) FROM AnAccount A INNER JOIN ACustomAccount CA ON (A.Id = CA.Id) WHERE (CA.InBalance = 1) INTO :TheInitialValue; IF( TheInitialValue IS NULL ) THEN EXIT; SELECT SUM(AMovement.Quantity * AMovement.UnitCost) FROM AMovement INNER JOIN AnEconomicOperation EOp ON (AMovement.EconomicOperationId = EOp.Id) INNER JOIN ACustomAccount CA ON (AMovement.DebitAccountId = CA.Id) WHERE (EOp.BeginsWith < :ReqDate) AND (CA.InBalance = 1) INTO :TheSum; IF ( TheSum IS NULL ) THEN TheSum = 0; DebitBalance = TheInitialValue + TheSum;END !!SET TERM ; !! COMMIT;

Результирующая физическая модель

На рисунке 4.42 представлена результирующая физическая модель.

Рисунок 4.42 – Иерархия счётов и проводок, физическая модель данных

Замечания

Так как в данной абстракции понятие "Проводка.сумма" является часто более существенным, чем понятие "Проводка.цена единицы", то для избежания ошибок округления полезно хранить сумму, а цену единицы вычислять, как (Проводка.сумма / Проводка.количество).

Кроме того, можно избавиться от понятий "Кредитовое начальное значение на счёте" и "Дебетовое начальное значение на счёте", вместо это проведя до начала отрабатываемого планом счетов периода соответствующие проводки со служебного счёта (созданного именно для этой цели).

Бухгалтерский учёт также подразумевает запрет на обороты между балансовыми и забалансовыми счетами, что можно реализовать соответствующими ограничениями (на уровне триггеров).

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

 

 



Поделиться:

Дата добавления: 2015-08-05; просмотров: 109; Мы поможем в написании вашей работы!; Нарушение авторских прав





lektsii.com - Лекции.Ком - 2014-2024 год. (0.005 сек.) Все материалы представленные на сайте исключительно с целью ознакомления читателями и не преследуют коммерческих целей или нарушение авторских прав
Главная страница Случайная страница Контакты