mimic-iii数据库_财务会计应用程序数据库III：完成基础结构
mimic-iii數(shù)據(jù)庫(kù)
介紹 (Introduction)
In the previous article , we discussed and implemented core accounting infrastructure design: general ledger, chart of accounts, (source) documents and financial statements structure.
在上一篇文章中 ，我們討論并實(shí)現(xiàn)了核心會(huì)計(jì)基礎(chǔ)結(jié)構(gòu)設(shè)計(jì)：總帳，會(huì)計(jì)科目表，(原始)單據(jù)和財(cái)務(wù)報(bào)表結(jié)構(gòu)。
This article will be dedicated to the finalization of accounting infrastructure design: company profile, person profiles, costs centres and all the other entities that are used across all of the specialized accounting documents.
本文將致力于會(huì)計(jì)基礎(chǔ)結(jié)構(gòu)設(shè)計(jì)的最終確定：公司資料，人員資料，成本中心以及所有專用會(huì)計(jì)憑證中使用的所有其他實(shí)體。
會(huì)計(jì)應(yīng)用本地化 (Localization in Accounting Application)
Localization is usually defined as customization of an application for specific cultures or regions. Localization consists primarily of translating the user interface. However, in the financial accounting domain, we handle not only numbers and codes but also some amount of textual information. Fortunately, the amount of textual information is very limited. In addition, not nearly all of the textual information requires localization. E.g., there is no point in localizing data that is only meant for internal use like descriptions of documents, fixed assets, internal comments etc. Only the information that is (potentially) visible externally needs to be localized.
本地化通常定義為針對(duì)特定文化或地區(qū)的應(yīng)用程序定制。 本地化主要包括翻譯用戶界面。 但是，在財(cái)務(wù)會(huì)計(jì)領(lǐng)域，我們不僅處理數(shù)字和代碼，還處理一些文本信息。 幸運(yùn)的是，文本信息的數(shù)量非常有限。 此外，并非幾乎所有文本信息都需要本地化。 例如，對(duì)僅用于內(nèi)部使用的數(shù)據(jù)進(jìn)行本地化是沒有意義的，例如文檔說明，固定資產(chǎn)，內(nèi)部注釋等。僅需要對(duì)(可能)在外部可見的信息進(jìn)行本地化。
Notably, there is a legal requirement for financial accounting that all of the accounting records and documents shall be kept in national language. (I believe it’s true for pretty much all of the jurisdictions) The common exceptions?to the rule are the (source) documents issued by persons in other countries, e.g., you don’t need to translate an invoice issued by some German company. Which brings us to the requirement that all of the textual data should always exist in the national language of the company’s registration country. The localization of textual data in the financial accounting domain means providing extra textual data translated into the native/understandable language of the person that the data is meant for, i.e., you need to keep such data both in the national language and in the language required by the person involved.
值得注意的是，對(duì)于財(cái)務(wù)會(huì)計(jì)有一項(xiàng)法律要求，即所有會(huì)計(jì)記錄和文件均應(yīng)以本國(guó)語言保存。 (我相信幾乎所有司法管轄區(qū)都是如此)。該規(guī)則的常見例外是其他國(guó)家/地區(qū)的人簽發(fā)的(原始)文件，例如，您無需翻譯某些德國(guó)公司簽發(fā)的發(fā)票。 這使我們要求所有文本數(shù)據(jù)應(yīng)始終以公司注冊(cè)國(guó)的本國(guó)語言存在。 文本數(shù)據(jù)在財(cái)務(wù)會(huì)計(jì)領(lǐng)域中的本地化意味著提供額外的文本數(shù)據(jù)，將其翻譯成該數(shù)據(jù)所針對(duì)的人的母語/可理解的語言，即，您需要以本國(guó)語言和要求的語言來保留此類數(shù)據(jù)由有關(guān)人員。
Having in mind the scope of the application we develop, localization will be required for the following documents:
考慮到我們開發(fā)的應(yīng)用程序的范圍，以下文件將需要本地化：
- Invoices made 發(fā)票
- Pro forma invoice made 備考發(fā)票
- Cash receipt 現(xiàn)金收據(jù)
The document specific textual data and its regionalization will be handled by the documents themselves, i.e., within the tables that extend document table for specific document type. However, there is one common data piece for all of the documents – company’s data. Therefore, the company’s profile should be localizable, i.e., there should be:
文檔特定的文本數(shù)據(jù)及其區(qū)域化將由文檔本身處理，即在擴(kuò)展文檔表以用于特定文檔類型的表中。 但是，所有文檔只有一個(gè)通用數(shù)據(jù)-公司數(shù)據(jù)。 因此，公司的資料應(yīng)該是可本地化的，即應(yīng)該有：
- A common company’s profile that contains language agnostic data (company’s official name, various codes, id’s, emails, defaults, policies, etc.) 包含語言不可知數(shù)據(jù)(公司的正式名稱，各種代碼，ID，電子郵件，默認(rèn)值，策略等)的常見公司資料。
- A localized company’s profile for each language that the company uses (address, contact info, CEO title, etc.) 公司使用的每種語言的本地化公司資料(地址，聯(lián)系信息，首席執(zhí)行官職位等)
Another common data piece for all of the localizable documents is person (profile). However, as you will soon see, there?are no textual data within person profile that could reasonable require localization. Official names cannot be translated. Hardly anybody would require translating an original address in a foreign country. The rest of the person data is about various codes, ids and defaults that are not subject to translation. Therefore, there is no need to implement localization for a person profile.
所有可本地化文檔的另一個(gè)通用數(shù)據(jù)是個(gè)人(個(gè)人資料)。 但是，正如您將很快看到的那樣，個(gè)人資料中沒有可以合理要求本地化的文本數(shù)據(jù)。 官方名稱無法翻譯。 幾乎沒有人需要在國(guó)外翻譯原始地址。 其余人員數(shù)據(jù)涉及無需翻譯的各種代碼，ID和默認(rèn)值。 因此，無需對(duì)人員資料進(jìn)行本地化。
Actually, most of the companies use some billing or CRM systems to issue invoices as well as cash receipts. The data from such systems is then automatically transferred to the accounting system, typically via REST service. In such cases, localization of an accounting system is not required at all trivially because the international data is handled by other applications and an accountant only require textual data in the national language. However, we are also targeting a small business that at least to some extent uses?accounting system for billing. Therefore – localization.
實(shí)際上，大多數(shù)公司都使用某些計(jì)費(fèi)或CRM系統(tǒng)來開具發(fā)票和現(xiàn)金收據(jù)。 然后，通常通過REST服務(wù)將來自此類系統(tǒng)的數(shù)據(jù)自動(dòng)傳輸?shù)接泿は到y(tǒng)。 在這種情況下，完全不需要瑣碎的會(huì)計(jì)系統(tǒng)本地化，因?yàn)閲?guó)際數(shù)據(jù)是由其他應(yīng)用程序處理的，而會(huì)計(jì)只需要使用本國(guó)語言的文本數(shù)據(jù)即可。 但是，我們還針對(duì)至少在某種程度上使用會(huì)計(jì)系統(tǒng)進(jìn)行計(jì)費(fèi)的小型企業(yè)。 因此-本地化。
會(huì)計(jì)應(yīng)用程序中的版本控制 (Versioning in Accounting Application)
In theory, every time a (source) document is received (or is issued) by an accountant, he shall record it and never change or delete it. In real life, typos happen all the time and accountants are not willing to do corrections in the right way by issuing adjustment documents like a credit note. This is especially true for small and medium business. So, the UD part of CRUD is technically illegal, but is a must for the target user group. For the very same reason, there is no such thing as document version in the accounting domain. If a particular company wants to use detailed audit trail, it can use extra solutions for it as discussed in the first article in this series (SQL server binary log, custom log with serialized documents, etc.). Most of the companies that we target will not want or need such a functionality. Notably, law rarely requires detailed audit trail. As we definitely do not target banks, insurance companies and other highly specific sectors, the solution with full CRUD and no (extensive) audit trail is perfectly acceptable for the target group (small to medium companies and their accountants).
從理論上講，每當(dāng)會(huì)計(jì)師收到(或發(fā)出)(原始)文件時(shí)，他應(yīng)記錄該文件，并且決不對(duì)其進(jìn)行更改或刪除。 在現(xiàn)實(shí)生活中，打字錯(cuò)誤經(jīng)常發(fā)生，會(huì)計(jì)人員不愿意通過發(fā)行調(diào)整文件(如貸方通知單)來正確地進(jìn)行更正。 對(duì)于中小型企業(yè)尤其如此。 因此，CRUD的UD部分在技術(shù)上是非法的，但對(duì)于目標(biāo)用戶組是必須的。 出于同樣的原因，在會(huì)計(jì)域中沒有諸如文檔版本之類的東西。 如果特定公司希望使用詳細(xì)的審計(jì)跟蹤，則可以使用本系列第一篇文章中討論的其他解決方案(SQL Server二進(jìn)制日志，帶有序列化文檔的自定義日志等)。 我們所針對(duì)的大多數(shù)公司都將不需要或不需要這樣的功能。 值得注意的是，法律很少要求詳細(xì)的審計(jì)線索。 由于我們絕對(duì)不針對(duì)銀行，保險(xiǎn)公司和其他高度特定的行業(yè)，因此具有完整CRUD功能且沒有(廣泛的)審計(jì)追蹤的解決方案對(duì)于目標(biāo)群體(中小型公司及其會(huì)計(jì)師)是完全可以接受的。
On the other hand, accounting domain?does require?some versioning let alone not exactly traditional. The reason is the relational model we use. Even though the documents data are self-sufficient, provides full details for a particular document. They do reference common model objects – persons’ and company’s profile. E.g., an invoice document model does not store full company’s profile data and persons (buyers) profile data, only reference the data in the common infrastructure tables. However, any document when printed or otherwise exported shall include the data that was relevant as of the date of the document. It could be achieved in two ways:
另一方面，會(huì)計(jì)域確實(shí)需要某些版本控制，更不用說不是完全傳統(tǒng)的了。 原因是我們使用的關(guān)系模型。 即使文檔數(shù)據(jù)是自給自足的，也可以提供特定文檔的完整詳細(xì)信息。 他們確實(shí)引用了常見的模型對(duì)象-人員和公司的個(gè)人資料。 例如，發(fā)票單據(jù)模型不存儲(chǔ)完整的公司資料數(shù)據(jù)和人員(買方)資料數(shù)據(jù)，而僅引用公共基礎(chǔ)結(jié)構(gòu)表中的數(shù)據(jù)。 但是，任何文檔在打印或以其他方式導(dǎo)出時(shí)，都應(yīng)包含截至文檔日期的相關(guān)數(shù)據(jù)。 它可以通過兩種方式實(shí)現(xiàn)：
- By storing the common state in some serialized way within the document itself (snapshot)?or 通過以某種序列化的方式將公共狀態(tài)存儲(chǔ)在文檔本身(快照)中，或者
- By providing different common state versions for all the periods 通過為所有期間提供不同的通用狀態(tài)版本
The first method has problems with the real world use cases. E.g., an accountant issued an invoice for some company and later got to know that the company’s name was already different/changed at the invoice date. If he changes the relevant person data in the common model, the changes will not be reflected in the document of the interest as it holds old common state snapshot. Therefore, we will need to provide some method to reset the snapshot or to require an accountant to delete the document and then enter it anew. In both cases, the implementation is going to be non-intuitive or/and inconvenient, which is not good.
第一種方法在實(shí)際使用案例中存在問題。 例如，一位會(huì)計(jì)師為某家公司開具了發(fā)票，后來又知道該公司的名稱在發(fā)票日期已經(jīng)不同/已更改。 如果他更改了通用模型中的相關(guān)人員數(shù)據(jù)，則該更改將不會(huì)反映在感興趣的文檔中，因?yàn)樗f的通用狀態(tài)快照。 因此，我們將需要提供一些方法來重置快照或要求會(huì)計(jì)師刪除文檔然后重新輸入。 在兩種情況下，實(shí)現(xiàn)都將是非直覺的和/或不便的，這是不好的。
The second method perfectly meets the expectations of an accountant. If some data for some period is changed, an accountant can be sure that all the documents for the relevant period will catch up. Therefore, we’re going to use it. (For further discussion, see Best way to design a database and table to keep records of changes).
第二種方法完全可以滿足會(huì)計(jì)師的期望。 如果某個(gè)時(shí)期的某些數(shù)據(jù)發(fā)生更改，會(huì)計(jì)可以確保相關(guān)時(shí)期的所有單據(jù)都將趕上。 因此，我們將使用它。 (有關(guān)進(jìn)一步的討論，請(qǐng)參閱設(shè)計(jì)數(shù)據(jù)庫(kù)和表以保留更改記錄的最佳方法 )。
The versioning method required is non-traditional in a way that any of the versions can be modified by an accountant whenever required (when an accountant gets to know about some changes). Conceptual winforms GUI could look something like that (for visualization only, fields are not actually well distributed as explained further!!!):
所需的版本控制方法是非傳統(tǒng)的，因此會(huì)計(jì)可以在需要時(shí)修改任何版本(會(huì)計(jì)需要了解某些更改時(shí))。 概念上的winforms GUI可能看起來像這樣( 僅用于可視化，字段實(shí)際上分布不佳，如進(jìn)一步說明！！！ )：
Actual version data will only be needed when a document is either printed or otherwise exported. So the business object implementing a document that requires versioning will require a method PrepareForPrint or similar to fetch (update) the versioned data. The SQL queries for that purpose will be as trivial as:
僅在打印或以其他方式導(dǎo)出文檔時(shí)才需要實(shí)際版本數(shù)據(jù)。 因此，實(shí)現(xiàn)需要版本控制的文檔的業(yè)務(wù)對(duì)象將需要使用PrepareForPrint或類似方法來獲取(更新)版本控制的數(shù)據(jù)。 用于此目的SQL查詢將很簡(jiǎn)單：
SELECT * FROM company_profile_versions v WHERE v. effective_date <= [document date] ORDER BY v.effective_date DESC LIMIT 1.Company profile version data could be cached, as the data amount will be small. Person profile version data is more problematic. Expected amount of persons could be as large as several dozen thousands. If you add three versions on average per person that could add up to quite a large memory footprint. Though, those implementation details are not particularly relevant at this development stage. For now, it’s enough to define that each company or person profile shall have a list of versioned data with effective dates specified.
公司簡(jiǎn)介版本數(shù)據(jù)可能會(huì)被緩存，因?yàn)閿?shù)據(jù)量很小。 人員配置文件版本數(shù)據(jù)存在更多問題。 預(yù)期人數(shù)可能多達(dá)數(shù)十萬人。 如果您平均每人添加三個(gè)版本，這可能會(huì)占用相當(dāng)大的內(nèi)存。 但是，這些實(shí)現(xiàn)細(xì)節(jié)在此開發(fā)階段并不特別相關(guān)。 到目前為止，只需定義每個(gè)公司或個(gè)人資料都應(yīng)具有已指定生效日期的版本控制數(shù)據(jù)列表即可。
地址 (Addresses)
There are two issues about addresses that should be discussed:
有關(guān)地址的兩個(gè)問題應(yīng)加以討論：
我們?nèi)绾螛?gòu)造地址字段？ (How Do We Structure Address Fields?)
You can find various address structurizations on web. Most of them are country specific, e.g., US postal office defines those address fields: Street Number, Predirectional, Street Name, Street Suffix, Postdirectional, Secondary Unit Indicator, Secondary Number, PO Box Number, City, State, ZIP Code, ZIP+4 Code. Using a country specific address structurization for an accounting application is not a particularly good idea, as a significant part of the companies have vendors and clients in foreign countries, which have their own addressing schema. There are no international standards for address structure. In such cases, it’s not a bad idea to rely on the guys who have the largest dataset to test the ideas on, i.e., Google. Google for Android uses those address fields: label, street, PO Box, neighbourhood, city, region, postcode and country. Definitely, it looks better than trying to fit US address schema for Lithuania.
您可以在網(wǎng)上找到各種地址結(jié)構(gòu)。 它們中的大多數(shù)是特定于國(guó)家/地區(qū)的，例如， 美國(guó)郵政局定義了以下地址字段：街道編號(hào)，預(yù)定向，街道名稱，街道后綴，定向，二級(jí)單位指示符，二級(jí)編號(hào)，郵政信箱編號(hào)，城市，州，郵政編碼，ZIP + 4個(gè)代碼。 在會(huì)計(jì)應(yīng)用程序中使用特定于國(guó)家/地區(qū)的地址結(jié)構(gòu)并不是一個(gè)特別好的主意，因?yàn)檫@些公司中有很大一部分在國(guó)外擁有自己的地址架構(gòu)的供應(yīng)商和客戶。 地址結(jié)構(gòu)沒有國(guó)際標(biāo)準(zhǔn)。 在這種情況下，依靠擁有最大數(shù)據(jù)集的人(例如Google)來測(cè)試想法并不是一個(gè)壞主意。 Google Android版會(huì)使用以下地址字段：標(biāo)簽，街道，郵政信箱，鄰里，城市，地區(qū)，郵政編碼和國(guó)家/地區(qū)。 無疑，它看起來比嘗試適合立陶宛的美國(guó)地址方案更好。
However, our data hunger shall not outshine the expectations of our users – accountants. An accountant will be unable and unwilling to structure addresses in foreign countries (try for yourself to structure address like this: “ 65 moo.7 T. Ao-nang, Muang, Krabi (Phi Phi Don), 81210 Phi Phi Don, Thailand ”). Therefore, we cannot impose a burden of address structuring on an accountant. It could only exist as an option for addresses within the application home country. To sum it all up, we shall provide only optional address structurization and only specific to the home country of the application. In my case – Lithuania. Which brings us to the requirement to have a field for unstructured address, a country and a few optional country specific fields (in my case – for Lithuania): municipality, city, street and house number. One more thing, that could enchase analytics these days, is geo coordinates. It’s easy to plug into gmaps and let an accountant validate unstructured address as well as pick the address from a map. Therefore, why not add two decimal fields for longitude and latitude.
但是，我們的數(shù)據(jù)需求絕不會(huì)超出用戶(會(huì)計(jì)師)的期望。 會(huì)計(jì)師將無法也不愿意在國(guó)外構(gòu)造地址(嘗試自己構(gòu)造地址，例如：“ 65 moo.7 T. Ao-nang，Muang，Krabi(Phi Phi Don)，81210 Phi Phi Don，Thailand ” )。 因此，我們不能給會(huì)計(jì)師增加地址結(jié)構(gòu)的負(fù)擔(dān)。 它只能作為應(yīng)用程序所在國(guó)家/地區(qū)內(nèi)地址的選項(xiàng)存在。 綜上所述，我們將僅提供可選的地址結(jié)構(gòu)化，并且僅針對(duì)應(yīng)用程序的母國(guó)。 就我而言–立陶宛。 這使我們要求擁有一個(gè)非結(jié)構(gòu)化地址字段，一個(gè)國(guó)家和一些可選的國(guó)家/地區(qū)專用字段(在我的情況下–立陶宛)：市政，城市，街道和門牌號(hào)碼。 如今，可以進(jìn)行分析的另一件事是地理坐標(biāo)。 插入gmap并讓會(huì)計(jì)師驗(yàn)證非結(jié)構(gòu)化地址以及從地圖中選擇地址很容易。 因此，為什么不為經(jīng)度和緯度添加兩個(gè)十進(jìn)制字段。
Keep in mind that we are doing the address structurization not out of having nothing else to do. The value of structured addresses is analytics. We definitely won’t do any analytics with the company’s self-addresses. Therefore, fields for unstructured addresses will be sufficient for the company’s profile.
請(qǐng)記住，我們正在做地址結(jié)構(gòu)化的工作并非沒有其他事情要做。 結(jié)構(gòu)化地址的價(jià)值在于分析。 我們絕對(duì)不會(huì)對(duì)公司的自我地址進(jìn)行任何分析。 因此，非結(jié)構(gòu)化地址的字段足以滿足公司的要求。
每人需要多少個(gè)地址？ (How Many Addresses per Person Are Required?)
There are two types of addresses of an accountant’s interest – billing address and shipping address. Actually, the latter is often handled by CRM, warehouse or billing applications and not so often of interest for an accounting.
會(huì)計(jì)師感興趣的地址有兩種類型：帳單地址和送貨地址。 實(shí)際上，后者通常是由CRM，倉(cāng)庫(kù)或開票應(yīng)用程序處理的，而對(duì)于會(huì)計(jì)而言卻不是那么重要。
The billing address is the one that is displayed on an invoice next to the client’s name. It can change, but it happens rarely. As we discussed in the section about versioning, it can be conveniently handled by the versions of a person profile. If the billing address changes at some moment, it shall and will affect the invoices, which dates are later than the change. E.g., if an accountant issued an invoice and then noticed that the billing address has changed, he can update person’s profile version and that will also update the invoice billing address, which is exactly what an accountant expects. The billing address is a legal field and might have some tax related implications. However, nothing really happens at the billing address. Therefore, its change in case of a mistake will still be reconcilable with the real world facts.
帳單地址是發(fā)票上顯示在客戶名稱旁邊的地址。 它可以改變，但很少發(fā)生。 正如我們?cè)谟嘘P(guān)版本控制的部分中討論的那樣，人員個(gè)人檔案的版本可以方便地處理它。 如果帳單地址在某一時(shí)刻發(fā)生更改，它將并且將影響發(fā)票，該日期晚于更改。 例如，如果會(huì)計(jì)師發(fā)出發(fā)票，然后發(fā)現(xiàn)帳單地址已更改，則他可以更新人員的個(gè)人資料版本，這也將更新發(fā)票帳單地址，這正是會(huì)計(jì)師期望的。 帳單郵寄地址是一個(gè)法律字段，可能會(huì)涉及一些與稅收相關(guān)的問題。 但是，帳單地址上并沒有真正發(fā)生任何事情。 因此，在發(fā)生錯(cuò)誤的情況下進(jìn)行更改仍然可以與現(xiàn)實(shí)世界相吻合。
The shipping address is the one where some goods or equipment?are actually shipped. It only has meaning if something is physically shipped. It also describes a real world fact, i.e., it’s not just a legal field like the billing address. Therefore, it is always a part of an invoice factual description and consequently is stored within the invoice schema (for further discussion, see Storing a Shipping Address Best Practice ). Of course, it is convenient for an accountant to be able to pick a shipping address from a lookup instead of entering it manually for each invoice. However, the lookup values (addresses) in this case are only defaults. Once an invoice is saved, the value is copied to the invoice because it becomes a real world fact (while you cannot change real world facts by changing a profile). Therefore, shipping addresses do not require versioning.
送貨地址是實(shí)際運(yùn)送某些貨物或設(shè)備的地址 。 它只有在實(shí)際運(yùn)輸某些東西時(shí)才有意義。 它還描述了一個(gè)現(xiàn)實(shí)世界的事實(shí)，即，它不僅是諸如帳單地址之類的合法字段。 因此，它始終是發(fā)票事實(shí)描述的一部分，因此存儲(chǔ)在發(fā)票架構(gòu)中(有關(guān)更多討論，請(qǐng)參見存儲(chǔ)送貨地址最佳實(shí)踐 )。 當(dāng)然，對(duì)于會(huì)計(jì)人員來說，能夠從查找中選擇送貨地址，而不是為每個(gè)發(fā)票手動(dòng)輸入送貨地址，是很方便的。 但是，在這種情況下，查找值(地址)僅為默認(rèn)值。 保存發(fā)票后，該值將被復(fù)制到發(fā)票，因?yàn)樗蔀檎鎸?shí)世界的事實(shí)(而您無法通過更改配置文件來更改真實(shí)世界的事實(shí))。 因此，收貨地址不需要版本控制。
A company by itself (as a legal entity) always has only one billing address. As changing fact that is required to be presented in multiple languages, it belongs to a version of company’s regional profile.
一家公司本身(作為法人實(shí)體)始終只有一個(gè)帳單地址。 由于需要用多種語言顯示的不斷變化的事實(shí)，它屬于公司區(qū)域概況的一個(gè)版本。
A company by itself could have multiple shipping addresses, i.e., it can ship goods from various locations. However, this concept is closely related to the warehouse concept for inventory operations. Therefore, we will deal with it in some later article about inventory operations.
一個(gè)公司本身可以有多個(gè)送貨地址，即它可以從各個(gè)位置運(yùn)送貨物。 但是，此概念與用于庫(kù)存操作的倉(cāng)庫(kù)概念緊密相關(guān)。 因此，我們將在以后的有關(guān)庫(kù)存操作的文章中進(jìn)行處理。
公司簡(jiǎn)介 (Company Profile)
To summarize the requirements for localization and versioning, we will need a total of four tables (entities) for company and person profiles:
總結(jié)本地化和版本控制的要求，我們將需要總共四個(gè)表(實(shí)體)用于公司和個(gè)人資料：
company_regional_profiles – to store localized company data that does not require versioning, e.g., default values that are only used to prefill document fields with default values. The table will contain one row per language (at least one row for the base language).
company_regional_profiles –用于存儲(chǔ)不需要版本控制的本地化公司數(shù)據(jù)，例如，僅用于使用默認(rèn)值預(yù)填充文檔字段的默認(rèn)值。 該表將包含每種語言的一行(基本語言至少一行)。
company_regional_profile_versions – to store localized company data that does require versioning, e.g. company address
company_regional_profile_versions –存儲(chǔ)需要版本控制的本地化公司數(shù)據(jù)，例如公司地址
company_profile – to store company data that does not require either localization or versioning, e.g., default values that are only used to prefill document fields with default values. The table will always contain only one row. (P.S. national ID as non-versioned field was a bad example, it can change although very rarely)
company_profile –存儲(chǔ)不需要本地化或版本控制的公司數(shù)據(jù)，例如，僅用于使用默認(rèn)值預(yù)填充文檔字段的默認(rèn)值。 該表將始終僅包含一行。 (PS國(guó)家ID作為非版本字段是一個(gè)不好的例子，盡管很少更改，但可以更改)
company_profile_versions – to store company data that does not require localization but does require versioning, e.g., company name, email, phone no, CEO name, etc.
company_profile_versions –存儲(chǔ)不需要本地化但需要版本控制的公司數(shù)據(jù)，例如公司名稱，電子郵件，電話號(hào)碼，CEO名稱等。
We will also need two more helper entities (tables) that logically belong?to the company profile:
我們還將需要另外兩個(gè)邏輯上屬于公司資料的幫助實(shí)體(表)：
default_accounts – As discussed in the previous article, some business functionality, e.g., balance sheet presentation?requires knowledge about some (default) company accounts that the company uses for specific purposes, e.g., retained revenues account. The list of such default accounts depends heavily on the business layer implementation and is likely to change. Therefore, it’s not a good idea to provide a separate field for each default account type. Besides, we also have to support the application extensions, which are likely to require their own default accounts. For these reasons, the default account types are defined as an enumeration with an extension point like we previously did for (source) document types. As I didn’t foresee the requirement for default account type extensibility in the first article in the series, we will implement it now.
default_accounts –如上一篇文章所述 ，某些業(yè)務(wù)功能(例如資產(chǎn)負(fù)債表表示)需要了解公司用于特定目的的一些(默認(rèn))公司帳戶，例如保留收入帳戶。 此類默認(rèn)帳戶的列表在很大程度上取決于業(yè)務(wù)層的實(shí)現(xiàn)，并且可能會(huì)發(fā)生變化。 因此，為每種默認(rèn)帳戶類型提供單獨(dú)的字段不是一個(gè)好主意。 此外，我們還必須支持應(yīng)用程序擴(kuò)展，這些擴(kuò)展可能需要其自己的默認(rèn)帳戶。 由于這些原因，默認(rèn)帳戶類型被定義為帶有擴(kuò)展點(diǎn)的枚舉，就像我們之前對(duì)(源)文檔類型所做的那樣。 由于我沒有預(yù)見該系列第一篇文章中對(duì)默認(rèn)帳戶類型可擴(kuò)展性的要求，因此我們現(xiàn)在將其實(shí)現(xiàn)。
company_regional_templates – one of the localization objects is a template that the company uses for its “external” documents, e.g., an invoice template. Specific template format depends on the reporting engine the application uses. However, it is not of importance from the database point of view, as a template is always a file, i.e., BLOB. The extra table requirement is due to the data type and for extensibility. The BLOB fields for templates could be added to the company_regional_profile_versions table, but such approach would complicate the database. First of all, templates do not change as frequently as other localizable data. Whenever we add a new version of localizable data, we duplicate the fields that do not actually change. It’s ok for small fields taking into account that there won’t be many entries in company_regional_profile_versions table. But the templates can be as large as 100 – 500 kB each. We definitely do not want to duplicate that much data when the company’s address got changed. Another reason to use a separate table is extensibility. It’s likely that some application extension could have its own documents that are meant for external users, e.g., a specific kind of invoice, and therefore require localization. As I didn’t foresee the requirement for template type extensibility in the first article in the series, we will also implement it now.
company_regional_templates –本地化對(duì)象之一是公司用于其“外部”文檔的模板，例如發(fā)票模板。 特定的模板格式取決于應(yīng)用程序使用的報(bào)告引擎。 但是，從數(shù)據(jù)庫(kù)的角度來看，它并不重要，因?yàn)槟０迨冀K是文件，即BLOB。 額外的表要求是由于數(shù)據(jù)類型和可擴(kuò)展性。 模板的BLOB字段可以添加到company_regional_profile_versions表中，但是這種方法會(huì)使數(shù)據(jù)庫(kù)復(fù)雜化。 首先，模板不像其他本地化數(shù)據(jù)那樣頻繁地更改。 每當(dāng)我們添加可本地化數(shù)據(jù)的新版本時(shí)，我們都會(huì)復(fù)制實(shí)際上未更改的字段。 考慮到在company_regional_profile_versions表中不會(huì)有太多條目的小字段也可以。 但是每個(gè)模板的大小可以高達(dá)100 – 500 kB。 我們絕對(duì)不希望在公司地址更改后重復(fù)那么多數(shù)據(jù)。 使用單獨(dú)的表的另一個(gè)原因是可擴(kuò)展性。 某些應(yīng)用程序擴(kuò)展可能具有自己的文檔(例如，特定類型的發(fā)票)供外部用戶使用，因此需要本地化。 由于我在本系列的第一篇文章中沒有預(yù)見到模板類型可擴(kuò)展性的要求，因此我們現(xiàn)在也將實(shí)現(xiàn)它。
Now we can summarize all of the requirements and my personal experience about the commonly used profile fields with the following company profile schemas (relevant portions per profile type):
現(xiàn)在，我們可以使用以下公司配置文件模式(每種配置文件類型的相關(guān)部分)來總結(jié)關(guān)于常用配置文件字段的所有要求和我的個(gè)人經(jīng)驗(yàn)：
表company_profile詳細(xì)信息 (Table company_profile Details)
| base_country | An ISO 3166 code of the country that the company is a resident for. Should not be changed after the company is created because companies cannot move to another jurisdiction by themselves. It’s a foreign key for the country_codes table and restricts updates and deletes of this country code. Actually, the restriction is the main purpose of this field, as it’s not very feasible that an accounting application could support many jurisdictions without major impact on usability. If a particular RDBMS does not support tables without primary keys, the field could also be used as a primary key. |
| base_currency | An ISO 4217 code of the base currency that is used for accounting. Should not be changed after the company is created because this kind of change brings us to the situation when there are two base currencies within a single database. Which is extremely difficult to calculate and support. The recommended method on such a rare occasions (e.g., introducing EUR) is to create a new company database and only move converted balances. While the old database is left for historic reference. It’s a foreign key for the currency_codes table and restricts updates and deletes of this currency code. |
| base_language | An ISO 639-2 code for the base language that is used in the accounting. Should not be changed after the company is created because that wouldn’t translate the old textual data by itself. The recommended method on such a rare occasions (I cannot even imagine such) is to create a new company database and only move balances. While the old database is left for historic reference. |
| establishment_date | A date that the company was established on. Obviously, cannot change (unless a typo). |
| default_description_invoice_made, default_description_invoice_received | A default descriptions of an invoice. The descriptions are only used to autofill the description field (as defaults), which is only for internal use. Therefore, its change has no impact for any old invoices. The reason why they are used instead of a separate table with default descriptions per document type is that in real life use cases, they are the only ones that do not require customized description (at least in my experience supporting an accounting application). |
| default_measure_unit_invoice_received | A default measure unit in an invoice received. It is only used to autofill the measure unit field (as a default), which is only for internal use. Therefore, its change has no impact for any old invoices. |
| default_sales_vat_schema_id, default_purchases_vat_schema_id | Default VAT schemas applied for new invoices. They are only applied as defaults. Therefore, their change has no impact for any old invoices. The VAT schemas will be discussed in the future articles in the series. For now, those are just reference fields for future use. |
| inserted_at, inserted_by, updated_at, updated_by | Standard audit trail fields as described in the first article in this series . Obviously, the company profile is a root entity. |
| base_country | 公司所在國(guó)家/地區(qū)的ISO 3166代碼。 公司成立后不應(yīng)更改，因?yàn)楣緹o法自行轉(zhuǎn)移到另一個(gè)司法管轄區(qū)。 它是country_codes表的外鍵，并限制此國(guó)家/地區(qū)代碼的更新和刪除。 實(shí)際上，限制是該領(lǐng)域的主要目的，因?yàn)闀?huì)計(jì)應(yīng)用程序可以在不對(duì)可用性造成重大影響的情況下支持許多司法管轄區(qū)并不可行。 如果特定的RDBMS不支持沒有主鍵的表，則該字段也可以用作主鍵。 |
| base_currency | 用于記帳的基礎(chǔ)貨幣的ISO 4217代碼。 創(chuàng)建公司后不應(yīng)更改，因?yàn)檫@種更改會(huì)使我們陷入一個(gè)數(shù)據(jù)庫(kù)中有兩種基本貨幣的情況。 這是很難計(jì)算和支持的。 在這種罕見的情況下(例如引入歐元)，推薦的方法是創(chuàng)建一個(gè)新的公司數(shù)據(jù)庫(kù)，并且僅移動(dòng)轉(zhuǎn)換后的余額。 舊的數(shù)據(jù)庫(kù)留作歷史參考。 這是currency_codes表的外鍵，并限制此貨幣代碼的更新和刪除。 |
| base_language | 記帳中使用的基本語言的ISO 639-2代碼。 公司成立后不應(yīng)更改，因?yàn)槟菢泳筒粫?huì)自動(dòng)轉(zhuǎn)換舊的文本數(shù)據(jù)。 在這種罕見的情況下(我什至無法想象的)，推薦的方法是創(chuàng)建一個(gè)新的公司數(shù)據(jù)庫(kù)，僅移動(dòng)余額。 舊的數(shù)據(jù)庫(kù)留作歷史參考。 |
| establishment_date | 公司成立的日期。 顯然，不能更改(除非輸入錯(cuò)誤)。 |
| default_description_invoice_made, default_description_invoice_received | 發(fā)票的默認(rèn)描述。 說明僅用于自動(dòng)填充說明字段(默認(rèn)情況下)，僅供內(nèi)部使用。 因此，它的更改對(duì)任何舊發(fā)票都沒有影響。 之所以使用它們，而不是使用每個(gè)文檔類型具有默認(rèn)描述的單獨(dú)表，是因?yàn)樵诂F(xiàn)實(shí)生活中的用例中，它們是唯一不需要自定義描述的(至少以我支持會(huì)計(jì)應(yīng)用程序的經(jīng)驗(yàn)而言)。 |
| default_measure_unit_invoice_received | 收到的發(fā)票中的默認(rèn)度量單位。 它僅用于自動(dòng)填充度量單位字段(默認(rèn)情況下)，僅供內(nèi)部使用。 因此，它的更改對(duì)任何舊發(fā)票都沒有影響。 |
| default_sales_vat_schema_id, default_purchases_vat_schema_id | 適用于新發(fā)票的默認(rèn)增值稅方案。 它們僅作為默認(rèn)值應(yīng)用。 因此，它們的更改對(duì)任何舊發(fā)票都沒有影響。 增值稅方案將在本系列的后續(xù)文章中進(jìn)行討論。 目前，這些只是供將來使用的參考字段。 |
| inserted_at, inserted_by, updated_at, updated_by | 標(biāo)準(zhǔn)審計(jì)跟蹤字段，如本系列第一篇文章中所述。 顯然，公司簡(jiǎn)介是一個(gè)根實(shí)體。 |
表company_profile_versions詳細(xì)信息 (Table company_profile_versions Details)
| Fields | Description |
| id | A synthetic key. I prefer to use it for consistency. Though, in this case, field effective_date is also a great candidate as primary key. |
| effective_date | A date that the profile data is effective from. |
| national_id_no, social_security_no, vat_no | Codes that identify the company within state registries. Could be jurisdiction specific, but in most jurisdiction should be pretty much the same. If you think that they cannot change, think again. National registry reforms happen. Not to mention that a company could be (de)registered as VAT payer multiple times. Obviously, the codes are not localizable. |
| company_name | An official name of the company. Obviously, can change. Not localizable. |
| ceo_name, accountants_name | Names of the CEO and accountant. Obviously, can change. Not localizable. |
| vat_deduction_percentage | Applicable VAT deduction percent. Will be discussed in detail in the future articles. For now, just a field reserved for future use. |
| ceo_signature_facsimile, accountants_signature_facsimile | In some jurisdictions, invoices could be “signed” with a CEO’s signature facsimile (scanned signature). Not applicable for Lithuania anymore and, as far as I’m concerned, the rest of the EU. If it’s applicable for your jurisdiction, it’s a good place for the fields. Even though they are of BLOB type, their size is small by definition (1-5 kB). Therefore, no significant impact for duplication. |
領(lǐng)域 | 描述 |
| id | 合成密鑰。 我更喜歡使用它來保持一致性。 但是，在這種情況下，字段effective_date也是主鍵的最佳選擇。 |
| effective_date | 配置文件數(shù)據(jù)生效的日期。 |
| national_id_no, social_security_no, vat_no | 在州注冊(cè)表中標(biāo)識(shí)公司的代碼。 可能是特定于管轄區(qū)的，但在大多數(shù)管轄區(qū)中應(yīng)該幾乎相同。 如果您認(rèn)為它們無法更改，請(qǐng)?jiān)倏紤]一下。 國(guó)家注冊(cè)管理機(jī)構(gòu)改革正在進(jìn)行。 更不用說一家公司可以多次(注銷)注冊(cè)為增值稅支付方。 顯然，代碼不可本地化。 |
| company_name | 公司的正式名稱。 顯然，可以改變。 不可本地化。 |
| ceo_name, accountants_name | 首席執(zhí)行官和會(huì)計(jì)師的姓名。 顯然，可以改變。 不可本地化。 |
| vat_deduction_percentage | 適用的增值稅抵扣百分比。 在以后的文章中將詳細(xì)討論。 目前，僅保留一個(gè)字段供將來使用。 |
| ceo_signature_facsimile, accountants_signature_facsimile | 在某些轄區(qū)，可以用首席執(zhí)行官的簽名傳真(掃描簽名)對(duì)發(fā)票進(jìn)行“簽名”。 不再適用于立陶宛，就我而言，不適用于歐盟其他地區(qū)。 如果它適用于您所在的管轄區(qū)，那么它是適合這些領(lǐng)域的好地方。 即使它們是BLOB類型，從定義上看它們的大小也很小(1-5 kB)。 因此，對(duì)復(fù)制沒有重大影響。 |
Table extended_default_account_types is implemented using the same method as the other type extensions (see the first article of the series for details). The only substantial difference is the absence of fallback type. In this case, the extended default account type has no meaning without the extension itself. Therefore, if the extension is uninstalled, the default account types owned by the extension can be safely dropped. Table fields:
表extended_default_account_types是使用與其他類型擴(kuò)展相同的方法實(shí)現(xiàn)的(有關(guān)詳細(xì)信息，請(qǐng)參閱本系列的第一篇文章 )。 唯一的實(shí)質(zhì)區(qū)別是沒有后備類型。 在這種情況下，擴(kuò)展名默認(rèn)帳戶類型沒有擴(kuò)展名本身就沒有任何意義。 因此，如果卸載了該擴(kuò)展程序，則可以安全地刪除該擴(kuò)展程序擁有的默認(rèn)帳戶類型。 表格欄位：
| Fields | Description |
| id | A GUID of the extended type that is assigned (generated) by the extension developer. |
| extension_id | An ID of the extension that the extended operation type belongs to (foreign key to table extensions) |
| type_name | A name (short description) of the extended default account type – for the (obvious) data debugging purposes. |
領(lǐng)域 | 描述 |
| id | 由擴(kuò)展開發(fā)人員分配(生成)的擴(kuò)展類型的GUID。 |
| extension_id | 擴(kuò)展操作類型所屬的擴(kuò)展的ID(表擴(kuò)展的外鍵) |
| type_name | 擴(kuò)展默認(rèn)帳戶類型的名稱(簡(jiǎn)短描述)–用于(顯而易見的)數(shù)據(jù)調(diào)試目的。 |
表default_accounts詳細(xì)信息 (Table default_accounts Details)
| Fields | Description |
| id | A standard synthetic key. A combination of default_account_type and extended_default_account_type_id could also be used as a clustered primary key. However, the extended_default_account_type_id field could be null (if it’s not defined by an extension). Which might cause problems in some RDBMS. Therefore, I opt for standard synthetic key. |
| default_account_type | An application defined enumeration of the default account types that a handled by the base application functionality. It might change. Therefore, ENUM type cannot be used. For the data integrity purpose (to prevent illegal/non-existent types), a technical lookup table might be used. But I am undecided about it yet. |
| extended_default_account_type_id | A default account type defined by an extension of the application (if the default account type belongs to an application extension). It’s a foreign key for the extended_default_account_types table with ON DELETE CASCADE option. So that the entry is automatically deleted if the extension is uninstalled. |
| account_id | An account that is set as the default of the particular type. It’s a foreign key for the accounts table with ON DELETE CASCADE option. So that the entry is automatically deleted if the account is deleted. |
領(lǐng)域 | 描述 |
| id | 標(biāo)準(zhǔn)合成密鑰。 default_account_type和extended_default_account_type_id組合也可以用作群集主鍵。 但是， extended_default_account_type_id字段可以為null (如果擴(kuò)展名未定義)。 這可能會(huì)在某些RDBMS中引起問題。 因此，我選擇標(biāo)準(zhǔn)的合成密鑰。 |
| default_account_type | 應(yīng)用程序定義了基本應(yīng)用程序功能處理的默認(rèn)帳戶類型的枚舉。 它可能會(huì)改變。 因此，不能使用ENUM類型。 為了數(shù)據(jù)完整性(防止非法/不存在的類型)，可以使用技術(shù)查詢表。 但是我還沒有決定。 |
| extended_default_account_type_id | 由應(yīng)用程序擴(kuò)展名定義的默認(rèn)帳戶類型(如果默認(rèn)帳戶類型屬于應(yīng)用程序擴(kuò)展名)。 它是帶有ON DELETE CASCADE選項(xiàng)的extended_default_account_types表的外鍵。 因此，如果擴(kuò)展名被卸載，該條目將被自動(dòng)刪除。 |
| account_id | 設(shè)置為特定類型默認(rèn)值的帳戶。 它是帶有ON DELETE CASCADE選項(xiàng)的帳戶表的外鍵。 這樣，如果刪除帳戶，該條目將自動(dòng)刪除。 |
Now we can summarize all of the requirements and my personal experience about the commonly used localizable profile fields with the following company regional profile schemas (relevant portion):
現(xiàn)在，我們可以使用以下公司區(qū)域配置文件架構(gòu)(相關(guān)部分)來總結(jié)關(guān)于常用的可本地化配置文件字段的所有要求和我的個(gè)人經(jīng)驗(yàn)：
表company_regional_profiles詳細(xì)信息 (Table company_regional_profiles Details)
| Fields | Description |
| id | An ISO 639-2 code for the language that the regional profile is for. There should be at least one regional profile for the base language of the company. |
| default_extra_info_in_invoice | A default region specific text that is added to invoices. The text is only used to autofill a localized document/invoice comments field (as a default value). Therefore, its change has no impact for any old invoices. |
| default_invoice_measure_units | A default measure unit in an invoice made. It is only used to autofill the localized measure unit field as a default value. Therefore, its change has no impact for any old invoices. |
| inserted_at, inserted_by, updated_at, updated_by | Standard audit trail fields as described in the first article in this series. Obviously, the company regional profile for a particular language is a root entity. |
領(lǐng)域 | 描述 |
| id | 區(qū)域概要文件所用語言的ISO 639-2代碼。 公司的基本語言至少應(yīng)有一個(gè)區(qū)域性資料。 |
| default_extra_info_in_invoice | 添加到發(fā)票的默認(rèn)區(qū)域特定文本。 文本僅用于自動(dòng)填充本地化的文檔/發(fā)票注釋字段(作為默認(rèn)值)。 因此，它的更改對(duì)任何舊發(fā)票都沒有影響。 |
| default_invoice_measure_units | 發(fā)票中的默認(rèn)度量單位。 它僅用于自動(dòng)填充本地化的度量單位字段作為默認(rèn)值。 因此，它的更改對(duì)任何舊發(fā)票都沒有影響。 |
| inserted_at, inserted_by, updated_at, updated_by | 標(biāo)準(zhǔn)審計(jì)跟蹤字段，如本系列第一篇文章中所述。 顯然，特定語言的公司區(qū)域資料是一個(gè)根實(shí)體。 |
表company_regional_profile_versions詳細(xì)信息 (Table company_regional_profile_versions Details)
| Fields | Description |
| id | A standard synthetic key. I prefer to use it for consistency. Though, in this case, fields effective_date and regional_profile_id could also be used as a clustered primary key. |
| regional_profile_id | An id of the regional profile that the version entry belongs to. |
| effective_date | A date that the profile data is effective from. |
| registered_address, office_address | A registered and actual address of the company. The registered address is an official address within the relevant companies’ registry. It is common that actual address is different from the official. |
| phone_no, email, contact_details | Contact details that are used in the documents for a specific language. Even though phone number and email are not localizable by nature (cannot be translated), it’s common to display different contact details for different languages. There are employees behind those contacts and not every employee may speak the relevant language. |
| ceo_title | A title of the CEO, e.g., director, president, chairman, CEO, etc. |
領(lǐng)域 | 描述 |
| id | 標(biāo)準(zhǔn)合成密鑰。 我更喜歡使用它來保持一致性。 不過，在這種情況下，字段effective_date和regional_profile_id ID也可以用作群集主鍵。 |
| regional_profile_id | 版本條目所屬的區(qū)域配置文件的ID。 |
| effective_date | 配置文件數(shù)據(jù)生效的日期。 |
| registered_address, office_address | 公司的注冊(cè)地址和實(shí)際地址。 注冊(cè)地址是相關(guān)公司注冊(cè)表中的正式地址。 實(shí)際地址與官方地址不同是很常見的。 |
| phone_no, email, contact_details | 文檔中使用的特定語言的聯(lián)系方式。 即使電話號(hào)碼和電子郵件本質(zhì)上無法本地化(無法翻譯)，但通常會(huì)針對(duì)不同的語言顯示不同的聯(lián)系方式。 這些聯(lián)系人背后有員工，并不是每個(gè)員工都可以說相關(guān)的語言。 |
| ceo_title | CEO的頭銜，例如董事，總裁，董事長(zhǎng)，CEO等。 |
Table extended_regional_template_types is implemented using the same method as the other type extensions (see the first article of the series for details). The only substantial difference is the absence of fallback type. In this case, the extended template type has no meaning without the extension itself. Therefore, if the extension is uninstalled, its templates can be safely dropped. Table fields:
表extended_regional_template_types是使用與其他類型擴(kuò)展相同的方法實(shí)現(xiàn)的(有關(guān)詳細(xì)信息，請(qǐng)參閱本系列的第一篇文章 )。 唯一的實(shí)質(zhì)區(qū)別是沒有后備類型。 在這種情況下，擴(kuò)展模板類型沒有擴(kuò)展本身就沒有任何意義。 因此，如果擴(kuò)展名已卸載，則可以安全地刪除其模板。 表格欄位：
| Fields | Description |
| id | A GUID of the extended type that is assigned (generated) by the extension developer. |
| extension_id | An ID of the extension that the extended template type belongs to (foreign key to table extensions) |
| type_name | A name (short description) of the extended template type – for the (obvious) data debugging purposes. |
領(lǐng)域 | 描述 |
| id | 由擴(kuò)展開發(fā)人員分配(生成)的擴(kuò)展類型的GUID。 |
| extension_id | 擴(kuò)展模板類型所屬的擴(kuò)展的ID(表擴(kuò)展的外鍵) |
| type_name | 擴(kuò)展模板類型的名稱(簡(jiǎn)短描述)–用于(顯而易見的)數(shù)據(jù)調(diào)試目的。 |
表company_regional_templates詳細(xì)信息 (Table company_regional_templates Details)
| Fields | Description |
| id | A standard synthetic key. A combination of regional_profile_id, effective_date, regional_template_type and extended_regional_template_type_id could also be used as a clustered primary key. However, such a large clustered index doesn’t look very appealing. Therefore, I opt for standard synthetic key. |
| regional_profile_id | An id of the regional profile that the template version entry belongs to. |
| effective_date | A date that the template is effective from. |
| regional_template_type | An application defined enumeration of the template types that a handled by the base application functionality. It might change. Therefore, ENUM type cannot be used. For the data integrity purpose (to prevent illegal/non-existent types), a technical lookup table might be used. But I am undecided about it yet. |
| extended_regional_template_type_id | A template type defined by an extension of the application. (if the template type belongs to an application extension) It’s a foreign key for the extended_regional_template_types table with ON DELETE CASCADE option. So that the entry is automatically deleted if the extension is uninstalled. |
| template_body | A template file. |
領(lǐng)域 | 描述 |
| id | 標(biāo)準(zhǔn)合成密鑰。 也可以將regional_profile_id ， effective_date ， regional_template_type和extended_regional_template_type_id的組合用作集群主鍵。 但是，這么大的聚集索引看起來并不吸引人。 因此，我選擇標(biāo)準(zhǔn)的合成密鑰。 |
| regional_profile_id | 模板版本條目所屬的區(qū)域概要文件的ID。 |
| effective_date | 模板生效的日期。 |
| regional_template_type | 應(yīng)用程序定義了由基本應(yīng)用程序功能處理的模板類型的枚舉。 它可能會(huì)改變。 因此，不能使用ENUM類型。 為了數(shù)據(jù)完整性(防止非法/不存在的類型)，可以使用技術(shù)查詢表。 但是我還沒有決定。 |
| extended_regional_template_type_id | 由應(yīng)用程序擴(kuò)展定義的模板類型。 (如果模板類型屬于應(yīng)用程序擴(kuò)展)，它是帶有ON DELETE CASCADE選項(xiàng)的extended_regional_template_types表的外鍵。 因此，如果擴(kuò)展名被卸載，該條目將被自動(dòng)刪除。 |
| template_body | 模板文件。 |
Now that we have full company profile schema in place, we can define a sequence of actions required to initialize a new database for a company:
現(xiàn)在我們已經(jīng)有了完整的公司資料架構(gòu)，我們可以定義一系列初始化公司新數(shù)據(jù)庫(kù)所需的動(dòng)作：
Add entries for the relevant extensions as described in the first article in the series
如本系列的第一篇文章所述，為相關(guān)擴(kuò)展添加條目
Add entry to the table country_codes for the base state of the company
將條目添加到表country_codes以獲取公司的基本狀態(tài)
Add entry to the table currency_codes for the base currency of the company
在表currency_codes添加公司基本貨幣的條目
Add entry to the table company_profile for the non-localized company profile
將條目添加到未本地化公司資料的表company_profile中
Add entry to the table company_profile_versions for the non-localized company profile initial data version
將條目添加到表company_profile_versions以獲取未本地化的公司資料的初始數(shù)據(jù)版本
Add entry to the table company_regional_profiles for the localized company profile in base language
以基本語言將條目添加到表company_regional_profiles ，以獲取本地化的公司資料
Add entry to the table company_regional_profile_versions for the localized company profile in base language initial data version
在基本語言初始數(shù)據(jù)版本中，將條目添加到表company_regional_profile_versions中以本地化的公司資料
That’s a minimal required initialization. Of course, you should also consider using extra wizard for some typical financial statements and chart of accounts setup.
這是最小的必需初始化。 當(dāng)然，您還應(yīng)該考慮對(duì)某些典型的財(cái)務(wù)報(bào)表和會(huì)計(jì)科目表設(shè)置使用額外的向?qū)А?
個(gè)人資料 (Person Profiles)
To summarize the requirements for localization, versioning and addresses we will need a total of three tables (entities) for person profiles:
為了總結(jié)本地化，版本控制和地址的要求，我們將總共需要三個(gè)表(實(shí)體)用于個(gè)人資料：
person_profiles – to store person data that does not require versioning, e.g., default values that are only used to prefill document fields
person_profiles –存儲(chǔ)不需要版本控制的人員數(shù)據(jù)，例如，僅用于person_profiles文檔字段的默認(rèn)值
person_profile_versions – to store person data that does require versioning, e.g., person name, address, etc.
person_profile_versions –存儲(chǔ)確實(shí)需要版本控制的人員數(shù)據(jù)，例如，人員姓名，地址等。
shipping_addresses - to store shipping addreses for a person
shipping_addresses存儲(chǔ)一個(gè)人的運(yùn)送地址
We will also need two more helper entities (tables) that logically belongs to the person profile: person_groups and person_group_assignments. The requirement is determined by typical use cases – sometimes accountants need to group persons by some common business specific criteria. Person groups are trivial entities – just a name (to display in a lookup controls) and a description. One person could be assigned to multiple groups and vice versa. Hence, we also need a standard many-to-many relationship table – person_group_assignments. As those tables are trivial, no further details are needed.
我們還將需要另外兩個(gè)邏輯上屬于人員資料的幫助者實(shí)體(表)： person_groups和person_group_assignments 。 需求由典型的用例確定-有時(shí)會(huì)計(jì)師需要按照一些特定于業(yè)務(wù)的通用標(biāo)準(zhǔn)對(duì)人員進(jìn)行分組。 人員組是瑣碎的實(shí)體–只是名稱(顯示在查找控件中)和描述。 一個(gè)人可以被分配到多個(gè)小組， 反之亦然 。 因此，我們還需要一個(gè)標(biāo)準(zhǔn)的多對(duì)多關(guān)系表– person_group_assignments 。 由于這些表很瑣碎，因此不需要更多詳細(xì)信息。
Now we can summarize all of the requirements and my personal experience about the commonly used localizable profile fields with the following company regional profile schemas (relevant portion):
現(xiàn)在，我們可以使用以下公司區(qū)域配置文件架構(gòu)(相關(guān)部分)來總結(jié)關(guān)于常用的可本地化配置文件字段的所有要求和我的個(gè)人經(jīng)驗(yàn)：
表person_profiles詳細(xì)信息 (Table person_profiles Details)
| Fields | Description |
| id | A standard synthetic primary key. A person doesn’t have any field or even a combination of fields that could be used as a natural key. |
| default_currency_id | An ISO 4217 code of the currency to use as a default when invoicing the person. As a default value, it could be changed without any effect for old invoices. It’s a nullable foreign key for table currency_codes with option ON DELETE SET NULL, because the null value can be safely treated as company’s base currency while there is no reason to block currency delete only because it’s assigned as a default for a person. (as long as the currency was not used in a document) |
| default_regional_profile_id | An id of the company’s regional profile (language) to use as a default when creating some localizable documents for person (e.g., invoice). As a default value, it could be changed without any effect for the old documents. It’s a nullable foreign key for table company_regional_profiles with option ON DELETE SET NULL, because the null value can be safely treated as company’s base language profile while there is no reason to block regional profile delete only because it’s assigned as a default for a person (as long as the regional profile was not used in a document). |
| default_accounts_receivable_id, default_accounts_payable_id | Default accounts to use in documents for the person. As a default value, it could be changed without any effect for the old documents. It’s a nullable foreign key for table accounts with option ON DELETE SET NULL, because the values are optional while there are no reasons to block account delete only because it’s assigned as a default for a person (as long as the account was not used in a document). |
| date_of_birth | A date of birth of a natural person (if the person is a natural person and chose to use date of birth instead of other ids). Obviously, cannot change (unless a typo). |
| contact_details, comments | The fields are only for internal use by an accountant. No benefits in keeping their versions. |
| is_client, is_natural_person, is_employee, is_supplier | Most common person categories. Typically used to filter lookup lists. |
| is_archived | A standard field for marking a person as no longer in active use as described in the first article in this series . |
| inserted_at, inserted_by, updated_at, updated_by | Standard audit trail fields as described in the first article in this series. Obviously, the person profile is a root entity. |
領(lǐng)域 | 描述 |
| id | 標(biāo)準(zhǔn)的合成主鍵。 一個(gè)人沒有可以用作自然鍵的任何字段，甚至沒有字段的組合。 |
| default_currency_id | 開具發(fā)票給人的默認(rèn)ISO 4217貨幣代碼。 作為默認(rèn)值，可以更改它，而對(duì)舊發(fā)票不起作用。 它是表currency_codes的可空外鍵，帶有選項(xiàng)ON DELETE SET NULL ，因?yàn)榭梢詫ull值安全地視為公司的基礎(chǔ)貨幣，而沒有理由僅因?yàn)閷⑵浞峙錇槿说哪J(rèn)值而阻止貨幣刪除。 (只要未在文檔中使用該貨幣) |
| default_regional_profile_id | 創(chuàng)建個(gè)人的一些可本地化文檔(例如發(fā)票)時(shí)，默認(rèn)使用的公司區(qū)域資料的ID(語言)。 作為默認(rèn)值，可以更改它而對(duì)舊文檔沒有任何影響。 它是帶有選項(xiàng)ON DELETE SET NULL表company_regional_profiles的可為空的外鍵，因?yàn)榭梢詫ull值安全地視為公司的基本語言配置文件，而沒有理由阻止區(qū)域配置文件刪除僅是因?yàn)樗恢付橐粋€(gè)人的默認(rèn)值(例如(只要文檔中未使用區(qū)域配置文件)。 |
| default_accounts_receivable_id, default_accounts_payable_id | 該人在文檔中使用的默認(rèn)帳戶。 作為默認(rèn)值，可以更改它而對(duì)舊文檔沒有任何影響。 對(duì)于帶有選項(xiàng)ON DELETE SET NULL表帳戶，它是可為空的外鍵，因?yàn)檫@些值是可選的，而沒有理由阻止帳戶刪除，僅是因?yàn)樗恢付閭€(gè)人的默認(rèn)值(只要未在帳戶中使用該帳戶)文件)。 |
| date_of_birth | 自然人的出生日期(如果該人是自然人，并且選擇使用出生日期代替其他身份證)。 顯然，不能更改(除非輸入錯(cuò)誤)。 |
| contact_details, comments | 這些字段僅供會(huì)計(jì)師內(nèi)部使用。 保留其版本沒有任何好處。 |
| is_client, is_natural_person, is_employee, is_supplier | 最普通的人類別。 通常用于過濾查找列表。 |
| is_archived | 本系列的第一篇文章中描述的用于標(biāo)記某人不再活躍的標(biāo)準(zhǔn)字段。 |
| inserted_at, inserted_by, updated_at, updated_by | 標(biāo)準(zhǔn)審計(jì)跟蹤字段，如本系列第一篇文章中所述。 顯然，個(gè)人檔案是根實(shí)體。 |
表person_profile_versions詳細(xì)信息 (Table person_profile_versions?Details)
| Fields | Description |
| id | A synthetic key. I prefer to use it for consistency. Though, in this case, fields effective_date and person_id could also be used as a clustered primary key. |
| person_id | An id of the person (profile) that the data version is for |
| effective_date | A date that the profile data is effective from |
| person_name | A name of the person. Obviously, can change. |
| address, municipality, city, street, house_number, longitude, latitude | An address of the person (unstructured and, optionally, structured). Obviously, can change. |
| country_id | A state of the person residence. Not so obvious, but still can change. E.g., if a company becomes a state resident for tax purposes it is considered a state resident even if it has no subsidiaries in that country. |
| national_id_no, vat_no, social_security_no, business_certificate_no, individual_activity_certificate_no | Codes that identify the person (company) within state registries. Could be jurisdiction specific, but in most jurisdiction should be pretty much the same. If you think that they cannot change, think again. National registry reforms happen. Not to mention that a company could be (de)registered as VAT payer multiple times. Obviously, the codes are not localizable. |
| internal_id | An id of the person that the company uses internally, e.g., some customer id. It is typically displayed on invoices. Therefore, subject to versioning. |
| bank_account_no, bank_name | A bank account of the person. Obviously, can change. Could also be implemented as a separate table for person’s bank accounts to support multiple bank accounts per person. However, never been asked for such a functionality by any accountant and prefer to keep it as simple as possible. |
| An email of the person. It could be displayed on invoices. Therefore, subject to versioning (though, in this case it’s questionable). |
領(lǐng)域 | 描述 |
| id | 合成密鑰。 我更喜歡使用它來保持一致性。 不過，在這種情況下，字段effective_date和人員person_id也可以用作群集主鍵。 |
| person_id | 數(shù)據(jù)版本所針對(duì)的人(個(gè)人資料)的ID |
| effective_date | 配置文件數(shù)據(jù)生效的日期 |
| person_name | 人名。 顯然，可以改變。 |
| address, municipality, city, street, house_number, longitude, latitude | 人員的地址(非結(jié)構(gòu)化和(可選)結(jié)構(gòu)化)。 顯然，可以改變。 |
| country_id | 人居住地的狀態(tài)。 不太明顯，但仍然可以改變。 例如，如果一家公司出于稅收目的成為州居民，即使該公司在該國(guó)沒有子公司，也被視為州居民。 |
| national_id_no, vat_no, social_security_no, business_certificate_no, individual_activity_certificate_no | 在州注冊(cè)表中標(biāo)識(shí)個(gè)人(公司)的代碼。 可能是特定于管轄區(qū)的，但在大多數(shù)管轄區(qū)中應(yīng)該幾乎相同。 如果您認(rèn)為它們無法更改，請(qǐng)?jiān)倏紤]一下。 國(guó)家注冊(cè)管理機(jī)構(gòu)改革正在進(jìn)行。 更不用說一家公司可以多次(注銷)注冊(cè)為增值稅支付方。 顯然，代碼不可本地化。 |
| internal_id | 公司內(nèi)部使用的人員的ID，例如某些客戶ID。 通常顯示在發(fā)票上。 因此，應(yīng)進(jìn)行版本控制。 |
| bank_account_no, bank_name | 該人的銀行帳戶。 顯然，可以改變。 也可以實(shí)現(xiàn)為個(gè)人銀行帳戶的單獨(dú)表格，以支持每個(gè)人多個(gè)銀行帳戶。 但是，從來沒有任何會(huì)計(jì)師要求提供這樣的功能，而是希望使其盡可能簡(jiǎn)單。 |
| 該人的電子郵件。 它可以顯示在發(fā)票上。 因此，受版本控制(盡管在這種情況下，這是有問題的)。 |
The last table within person profile infrastructure shipping_addressesis trivial – just an unstructured and, optionally, structured address plus standard bit flag is_archived. The latter might seem redundant, as the shipping address shall be cloned to the invoice. I only leave it as a means for an accountant to temporary hide it in lookup controls.
人員配置文件基礎(chǔ)結(jié)構(gòu)shipping_addresses的最后一張表很簡(jiǎn)單–只是一個(gè)非結(jié)構(gòu)化(可選)的結(jié)構(gòu)化地址以及標(biāo)準(zhǔn)位標(biāo)志is_archived 。 后者似乎是多余的，因?yàn)樗拓浀刂窇?yīng)復(fù)制到發(fā)票上。 我僅將其保留為會(huì)計(jì)師將其暫時(shí)隱藏在查找控件中的一種方法。
Once we have the person profile schema in place, we can fetch the effectual profile fields with a query:
一旦有了人員個(gè)人檔案架構(gòu)，就可以通過查詢獲取有效的個(gè)人檔案字段：
SELECT p.id, p.default_currency_id, p.default_regional_profile_id, _ p.default_accounts_receivable_id, p.default_accounts_payable_id, p.date_of_birth, p.contact_details, p.comments, p.is_client, p.is_supplier, p.is_natural_person, p.is_employee, p.is_archived, v.person_name, v.address, v.state_of_residence_id, v.national_id_no, v.vat_no, _ v.social_security_no, v.business_certificate_no, v.individual_activity_certificate_no, v.internal_id, v.bank_account_no, v.bank_name, v.email FROM person_profiles p LEFT JOIN person_profile_versions v ON v.person_profile_id = p.id AND v.effective_date = (SELECT MAX(q.effective_date) FROM person_profile_versions q WHERE q.person_profile_id=p.id)成本中心 (Cost Centres)
A cost centre is typically defined as a department within a business to which costs can be allocated. It’s a part of management accounting. (See wiki) We will implement cost centre in a bit more generic and simple way – as an extra dimension for analytics that is not strictly related namely to costs. Simply define a loose set of simple cost centre entities with an option of grouping that could be assigned to documents and ledger entries and consequently enables filtering of reports. Such a straightforward implementation will allow an accountant to use it not only as a classical cost centre but also as a project tracker or budgeting line.
成本中心通常定義為企業(yè)中可以分配成本的部門。 這是管理會(huì)計(jì)的一部分。 (請(qǐng)參閱Wiki )我們將以更通用和更簡(jiǎn)單的方式實(shí)現(xiàn)成本中心-作為與成本不嚴(yán)格相關(guān)的分析的額外維度。 只需定義一組松散的簡(jiǎn)單成本中心實(shí)體，即可選擇可以分組到文檔和分類帳條目的分組選項(xiàng)，從而啟用報(bào)表過濾功能。 這種簡(jiǎn)單明了的實(shí)現(xiàn)方式將使會(huì)計(jì)師不僅可以將其用作經(jīng)典的成本中心，還可以用作項(xiàng)目跟蹤器或預(yù)算線。
The schema for cost centres is trivial and hardly requires any further comments:
成本中心的架構(gòu)很簡(jiǎn)單，幾乎不需要任何其他注釋：
將人員和成本中心綁定到文檔 (Binding Persons and Cost Centres to Documents)
As it was already discussed in the second article in the series, the first point to add the persons and costs centres at is the general ledger. We have already left two fields for that purpose. So now, the full schema looks like that (the relevant portion):
正如本系列第二篇文章中已經(jīng)討論過的那樣，添加人員和成本中心的第一點(diǎn)是總帳。 我們已經(jīng)為此保留了兩個(gè)字段。 因此，現(xiàn)在，完整架構(gòu)如下所示(相關(guān)部分)：
However, that’s not the only point where the foreign keys to the person and cost centre should be added. Again, as discussed in the second article, not every document has a general ledger transaction (e.g., time sheet or a labour contract do not). Moreover, some documents might have both persons related an unrelated through a general ledger entry (e.g., an invoice with a guarantee). Therefore, we shall also add a foreign key reference at the document level. Even though the related persons might overlap (and in most case will overlap), it doesn’t break normalization as the reference has a different context. The link to a document is definitely a broader concept than a link to a ledger entry. On the other hand, we cannot streamline relation to a document to a relation to a particular ledger entry. Which brings us to conclusion that both references are required by the business logic of the domain.
但是，這不是應(yīng)該添加人員和成本中心的外鍵的唯一點(diǎn)。 再次，如第二篇文章所述，并非每個(gè)文檔都具有總帳事務(wù)(例如，時(shí)間表或勞動(dòng)合同沒有)。 此外，某些文件可能通過總分類帳條目(例如，帶擔(dān)保的發(fā)票)使兩個(gè)人之間沒有關(guān)聯(lián)。 因此，我們還將在文檔級(jí)別添加外鍵引用。 即使相關(guān)人員可能重疊(并且在大多數(shù)情況下會(huì)重疊)，但由于引用具有不同的上下文，因此不會(huì)破壞規(guī)范化。 與到分類帳條目的鏈接相比，文檔的鏈接絕對(duì)是一個(gè)更廣泛的概念。 另一方面，我們無法將與單據(jù)的關(guān)系簡(jiǎn)化為與特定分類帳條目的關(guān)系。 這使我們得出結(jié)論，領(lǐng)域的業(yè)務(wù)邏輯需要兩個(gè)引用。
Obviously, a (source) document might have both multiple persons and cost centres related. Which brings us to requirement of standard many-to-many tables. Therefore, the final schema for document to persons and costs centres relations looks like that (a relevant portion):
顯然，(原始)文檔可能同時(shí)具有多個(gè)人員和成本中心。 這使我們達(dá)到了標(biāo)準(zhǔn)的多對(duì)多表的要求。 因此，文件與人員和成本中心關(guān)系的最終方案如下所示(相關(guān)部分)：
It should also be noted that the application that will make use of the schema shall not ignore the persons and costs centres that has already been assigned to ledger entries. Those shall also fall into the document relations. Otherwise, a trivial select filtered by a person (or a costs centre) would also hit the “heavy” ledger entries table. This is only the most pragmatic reason. There are also a theoretic one. As described above, the relation document-person is different from the relation ledger entry – person as per domain business logic. Therefore, the relation to a person by a ledger entry and a document have a different business meaning per domain and both of them shall be kept in order to correctly describe both aspects. The convention (as you cannot guarantee it at the database level) also has benefits for the extensions. As every extended document shall use the document infrastructure, all of the documents (both for base functionality and extended) will be available by a single “interface”, i.e., we can have a single filterable registry for all of the documents.
還應(yīng)注意，將使用該模式的應(yīng)用程序不應(yīng)忽略已經(jīng)分配給分類帳條目的人員和成本中心。 這些也應(yīng)屬于文檔關(guān)系。 否則，由人員(或成本中心)篩選的瑣碎選擇也會(huì)打入“沉重”分類帳條目表。 這只是最務(wù)實(shí)的原因。 還有一個(gè)理論上的。 如上所述，關(guān)系文檔人員與關(guān)系分類帳條目不同-根據(jù)域業(yè)務(wù)邏輯的人員。 因此，分類帳分錄和單據(jù)與人的關(guān)系在每個(gè)域中具有不同的業(yè)務(wù)含義，因此應(yīng)保留它們兩者以正確描述這兩個(gè)方面。 約定(因?yàn)槟荒茉跀?shù)據(jù)庫(kù)級(jí)別上保證它)對(duì)于擴(kuò)展也有好處。 由于每個(gè)擴(kuò)展文檔都將使用文檔基礎(chǔ)結(jié)構(gòu)，因此所有文檔(用于基本功能和擴(kuò)展文檔)都將通過單個(gè)“接口”提供，即，我們可以為所有文檔提供一個(gè)可過濾的注冊(cè)表。
通用文檔編號(hào)方案 (Common Document Numeration Schema)
The last but not the least handy thing to implement in the common documents interface is the numeration schema. The documents that an accountant receives have numbers that they have. Meaning, the accountant just records a fact. However, the company itself issues some documents. And, at least for small companies, those are issued using the accounting software, i.e. our application. Therefore, the application should be able to handle the numeration of such documents. It might be culture specific, but as far as I’m concerned, the ways the document numbers are generated are:
在通用文檔界面中要實(shí)現(xiàn)的最后但并非最不方便的事情是計(jì)數(shù)模式。 會(huì)計(jì)師收到的文件具有其編號(hào)。 意思是，會(huì)計(jì)師只記錄一個(gè)事實(shí)。 但是，公司本身會(huì)發(fā)布一些文件。 而且，至少對(duì)于小公司而言，這些是使用會(huì)計(jì)軟件(即我們的應(yīng)用程序)發(fā)行的。 因此，應(yīng)用程序應(yīng)該能夠處理此類文檔的數(shù)量。 它可能是特定于文化的，但是就我而言，文檔編號(hào)的生成方式是：
- There is some document serial, that consists mostly of some letters, sometimes other characters and numbers; might also be an empty string; 有一些文檔序列，主要由一些字母，有時(shí)還包括其他字符和數(shù)字組成； 也可能是一個(gè)空字符串；
- There is a base (running) number (integer) that is reset subject to the company policy: never, once a year, once a month or every day. 有一個(gè)根據(jù)公司政策重設(shè)的基本(運(yùn)行)數(shù)字(??整數(shù))：永遠(yuǎn)，每年一次，每月一次或每天一次。
- There is a formatted full document number that consists of: a serial (as a prefix), a date of the document (or part of the date, e.g., year only, or the date might be disregarded at all) and the base number. 有一個(gè)格式化的完整文檔編號(hào)，包括：序列號(hào)(作為前綴)，文檔日期(或日期的一部分，例如僅年份，或者日期可能根本不被考慮)和基數(shù)。
To sum it all up, the document number template using .NET format string (only for demonstration, any other could be used instead) could be described by the fields:
總而言之，使用.NET格式字符串的文檔編號(hào)模板(僅用于演示，可以使用其他任何格式)可以由以下字段描述：
- Serial – pretty much any string provided by the user; for sanity check, presumably max 20 characters; 串行–用戶提供的幾乎所有字符串； 進(jìn)行健全性檢查，大概最多20個(gè)字符；
- A numbering policy enumeration, i.e., reset running number: never, once a year, once a month or every day; 編號(hào)策略枚舉，即重置運(yùn)行編號(hào)：從不，每年一次，每月一次或每天一次；
A full number format string, e.g. “{0:yyyyMMdd}-{1:D6}” which for a document at 2019-12-31 with running number 1 would produce “20191231-000001”, or “{1}”, which for the same document would produce “1” (serial always goes as prefix, no need to include in the format string).
完整的數(shù)字格式字符串，例如“ {0：yyyyMMdd}-{1：D6} ”，對(duì)于運(yùn)行編號(hào)為1的2019-12-31的文檔，它將產(chǎn)生“ 20191231-000001 ”或“ {1} ”，因?yàn)橥晃臋n將產(chǎn)生“ 1 ”(序列始終作為前綴，不需要在格式字符串中包含)。
- A bit flag indicating that the full number is provided by an external system, i.e. whether the full number shall be “calculated” using running number and format string or a user (e.g., a CRM) should be allowed to enter the full number manually. 指示完整數(shù)字由外部系統(tǒng)提供的位標(biāo)志，即應(yīng)允許使用運(yùn)行編號(hào)和格式字符串“計(jì)算”完整數(shù)字，還是允許用戶(例如CRM)手動(dòng)輸入完整數(shù)字。
If you know of a real life use case that wouldn’t fit the template, please let me know.
如果您知道不適合該模板的現(xiàn)實(shí)用例，請(qǐng)告訴我。
Obviously, a change in template (company’s numbering policy for some type of document) cannot affect numbers of the documents that has already been issued. Which brings us to the following options:
顯然，模板的更改(公司對(duì)某種類型的文檔的編號(hào)策略)不會(huì)影響已經(jīng)發(fā)行的文檔的編號(hào)。 這為我們帶來了以下選擇：
- Make the template read-only if it has been used by a document 如果模板已被文檔使用，則將其設(shè)為只讀
- Copy template fields to the (complex) document number fields 將模板字段復(fù)制到(復(fù)雜)文檔編號(hào)字段
- Anything in between the previous solutions 先前解決方案之間的任何情況
Actually, I don’t see any serious cons for all of the options. My choice is based on the way most accountants see/understand the document-numbering concept. And they tend to associate the numbering schema with the serial part, i.e., when they choose a serial from a lookup accountants (most of them) expect a valid (next) number to be assigned to the document. And otherwise, accountants are not used to a field “numbering policy” as well as seeing multiple “numbering policies” with the same serial, which makes it non-intuitive. To make the numbering function as intuitive as I can, I chose the following mixed solution:
實(shí)際上，我看不到所有選項(xiàng)的嚴(yán)重弊端。 我的選擇基于大多數(shù)會(huì)計(jì)師對(duì)文件編號(hào)概念的理解/理解。 而且，他們傾向于將編號(hào)模式與序列號(hào)相關(guān)聯(lián)，即，當(dāng)他們從查詢會(huì)計(jì)師中選擇序列號(hào)時(shí)(大多數(shù))，他們希望將有效(下一個(gè))編號(hào)分配給文檔。 否則，會(huì)計(jì)師將不會(huì)習(xí)慣于“編號(hào)策略”字段，也不會(huì)看到具有相同序列號(hào)的多個(gè)“編號(hào)策略”，這使其變得不直觀。 為了使編號(hào)功能盡可能直觀，我選擇了以下混合解決方案：
- The serial part of a template is unique per document type and identifies the template (at least from the accountant’s point of view). 模板的序列部分對(duì)于每種文檔類型都是唯一的，并且可以標(biāo)識(shí)模板(至少?gòu)臅?huì)計(jì)師的角度而言)。
- The serial part of a template is made read-only the moment a document uses the template. 當(dāng)文檔使用模板時(shí)，模板的串行部分將變?yōu)橹蛔x狀態(tài)。
- The rest of the template fields remain editable but their values are copied to the document, i.e., change does not affect previous documents. 其余模板字段保持可編輯狀態(tài)，但是它們的值已復(fù)制到文檔中，即，更改不會(huì)影響以前的文檔。
There are two ways to implement this solution: plainly copy numbering policy, external system flag and format string to the document or keep a separate “numbering policy versions” table that holds the same fields and gets a new row whenever a policy changes. The first option will cost extra ~60 MB for one million document database. The second option will save the space and (maybe?) will be more normalized but somehow more complex. As the disk space costs are quite negligible, I opt for the more simple solution – three field copy. The resulting schema will be the following (the relevant portion):
有兩種方法可以實(shí)現(xiàn)此解決方案：將編號(hào)策略，外部系統(tǒng)標(biāo)志和格式字符串簡(jiǎn)單地復(fù)制到文檔中，或保留一個(gè)單獨(dú)的“編號(hào)策略版本”表，該表包含相同的字段并在策略更改時(shí)獲得新的一行。 對(duì)于一百萬個(gè)文檔數(shù)據(jù)庫(kù)，第一個(gè)選項(xiàng)將花費(fèi)額外的?60 MB。 第二個(gè)選項(xiàng)將節(jié)省空間，并且(也許？)將被更規(guī)范化，但是以某種方式更加復(fù)雜。 由于磁盤空間成本可以忽略不計(jì)，因此我選擇了更簡(jiǎn)單的解決方案-三字段復(fù)制。 結(jié)果模式如下(相關(guān)部分)：
表格complex_numbers的詳細(xì)信息 (Details for the table complex_numbers)
| Fields | Description |
| document_id | An id of the document that the number is of. As we have 0..1 relation here, it’s both a primary key and a foreign key. |
| template_id | An id of the template that defines the numbering rules. |
| reset_policy | A numbering policy, i.e., periods when the running number shall be reset. Copied from template. |
| format_string | A format string used to produce a full number by the document date and running number. Copied from template. |
| has_external_provider | A bit flag indicating that the full number is created and formatted by a third party (e.g. a CRM), i.e.,?format_string and running_no shall not be used. Copied from template. |
| running_no | A running (sequence) number of the document within the reset period. |
| full_no | A full (formatted) number of the document. |
領(lǐng)域 | 描述 |
| document_id | 該編號(hào)所屬的文檔的ID。 因?yàn)檫@里有0..1關(guān)系，所以它既是主鍵又是外鍵。 |
| template_id | 定義編號(hào)規(guī)則的模板的ID。 |
| reset_policy | 編號(hào)策略，即運(yùn)行編號(hào)應(yīng)重置的時(shí)間段。 從模板復(fù)制。 |
| format_string | 格式字符串，用于按憑證日期和運(yùn)行編號(hào)產(chǎn)生完整的數(shù)字。 從模板復(fù)制。 |
| has_external_provider | 指示完整號(hào)碼由第三方(例如CRM)創(chuàng)建并設(shè)置格式的位標(biāo)記，即format_string和running_no不應(yīng)使用。 從模板復(fù)制。 |
| running_no | 重置期間內(nèi)文檔的運(yùn)行(序列)編號(hào)。 |
| full_no | 文件的完整(格式)數(shù)。 |
表格complex_number_templates的詳細(xì)信息 (Details for the table complex_number_templates)
| Fields | Description |
| id | An id of the complex number template. A standard surrogate primary key |
| document_type | A base (source) document type that the template is meant for. |
| extended_document_type_id | An extended (source) document type that the template is meant for. |
| serial | A serial of a document, i.e. an arbitrary string (including an empty one) to prefix the full number of a document. Should be unique per document_type and extended_document_type_id. Shall not be changes after the template is used for a document. |
| reset_policy | A numbering policy, i.e., periods when the running number shall be reset. |
| format_string | A format string used to produce a full number by the document date and running number. |
| has_external_provider | A bit flag indicating that the full number is created and formatted by a third party (e.g., a CRM), i.e. format_string shall not be used. |
| is_archived | A standard bit flag indicating that the template is no longer in use and is kept only as a historical record. (as the template used in a document cannot be deleted) |
| comments | Accountant’s comments regarding the template. For internal use only. |
| inserted_at, inserted_by, updated_at, updated_by | Standard audit trail fields as described in the first article in this series . Obviously, the complex number template is a root entity. |
領(lǐng)域 | 描述 |
| id | 復(fù)數(shù)模板的ID。 標(biāo)準(zhǔn)代理主鍵 |
| document_type | 模板所針對(duì)的基本(源)文檔類型。 |
| extended_document_type_id | 模板所針對(duì)的擴(kuò)展(源)文檔類型。 |
| serial | 文檔的序列號(hào)，即以字符串開頭的任意字符串(包括一個(gè)空字符串)。 每個(gè)document_type和extended_document_type_id應(yīng)該唯一。 模板用于文檔后不得更改。 |
| reset_policy | 編號(hào)策略，即運(yùn)行編號(hào)應(yīng)重置的時(shí)間段。 |
| format_string | 格式字符串，用于按憑證日期和運(yùn)行編號(hào)產(chǎn)生完整的數(shù)字。 |
| has_external_provider | 指示完整號(hào)碼由第三方(例如CRM)創(chuàng)建并設(shè)置格式的位標(biāo)記，即format_string不應(yīng)使用。 |
| is_archived | 一個(gè)標(biāo)準(zhǔn)位標(biāo)志，指示該模板不再使用，僅保留為歷史記錄。 (因?yàn)闊o法刪除文檔中使用的模板) |
| comments | 會(huì)計(jì)師對(duì)模板的評(píng)論。 僅限內(nèi)部使用。 |
| inserted_at, inserted_by, updated_at, updated_by | 標(biāo)準(zhǔn)審計(jì)跟蹤字段，如本系列第一篇文章中所述。 顯然，復(fù)數(shù)模板是一個(gè)根實(shí)體。 |
Having the schema in place, we can now fetch a next number for an arbitrary complex number schema with an id=123 and a document dated 2019-05-26 in a generic way:
有了模式之后，我們現(xiàn)在可以以通用方式為id=123和日期為2019-05-26的任意復(fù)數(shù)模式獲取下一個(gè)數(shù)字：
SELECT MAX(c.running_no) + 1 AS NextRunningNo FROM documents d LEFT JOIN complex_numbers c ON c.document_id = d.id WHERE c.template_id = 123 AND (c.reset_policy = 'never' OR (c.reset_policy = 'day' AND d.document_date = '2019-05-26') OR (c.reset_policy = 'month' AND YEAR(d.document_date) = YEAR('2019-05-26') AND MONTH(d.document_date) = MONTH('2019-05-26')) OR (c.reset_policy = 'quarter' AND YEAR(d.document_date) = YEAR('2019-05-26') AND QUARTER(d.document_date) = QUARTER('2019-05-26')) OR (c.reset_policy = 'halfyear' AND YEAR(d.document_date) = YEAR('2019-05-26') AND ((MONTH(d.document_date) < 7 AND MONTH('2019-05-26') < 7) OR (MONTH(d.document_date) > 6 AND MONTH('2019-05-26') > 6))) OR (c.reset_policy = 'year' AND YEAR(d.document_date) = YEAR('2019-05-26')));結(jié)論 (Conclusion)
In this article, we have finished developing database schema for the core accounting infrastructure: company profile, person profiles, generic document numbering policy.
在本文中，我們已經(jīng)完成了針對(duì)核心會(huì)計(jì)基礎(chǔ)結(jié)構(gòu)的數(shù)據(jù)庫(kù)架構(gòu)的開發(fā)：公司資料，人員資料，通用文檔編號(hào)策略。
Future?articles will be dedicated to concrete types of (source) documents and accounting operations. At the moment, I haven’t made up my mind where to start from.
以后的文章將專門討論(源)單據(jù)和會(huì)計(jì)操作的具體類型。 目前，我還沒有決定從哪里開始。
翻譯自: https://www.codeproject.com/Articles/5164951/Database-for-Financial-Accounting-Application-III
mimic-iii數(shù)據(jù)庫(kù)
總結(jié)
以上是生活随笔為你收集整理的mimic-iii数据库_财务会计应用程序数据库III：完成基础结构的全部?jī)?nèi)容，希望文章能夠幫你解決所遇到的問題。
- 上一篇： X9C102PIZ数字电位器-中文
- 下一篇： spring security 实现微信