{"id":10105,"date":"2023-06-19T17:59:31","date_gmt":"2023-06-19T08:59:31","guid":{"rendered":"https:\/\/www.sambuichi.jp\/?p=10105"},"modified":"2023-06-19T18:15:21","modified_gmt":"2023-06-19T09:15:21","slug":"achieving-data-consistency-and-clarity-in-the-table-structure","status":"publish","type":"post","link":"https:\/\/www.sambuichi.jp\/?p=10105","title":{"rendered":"Achieving Data Consistency and Clarity in the Table Structure"},"content":{"rendered":"<p>Views: 24<\/p><div id=\"header\">\n<h1>Achieving Data Consistency and Clarity in the Table Structure<\/h1>\n<div id=\"toc\" class=\"toc2\">\n<div id=\"toctitle\">Table of Contents<\/div>\n<ul class=\"sectlevel1\">\n<li><a href=\"#_refine_trial_balance_table\">1. Refine Trial Balance Table<\/a><\/li>\n<li><a href=\"#_missing_4_currency_codes\">2. Missing 4 currency codes<\/a><\/li>\n<li><a href=\"#_how_to_find_out_functional_currency_code\">3. How to find out functional currency code?<\/a><\/li>\n<li><a href=\"#_4_currency_codes\">4. 4 currency codes<\/a><\/li>\n<li><a href=\"#_no_235_were_not_improved_with_your_solution\">5. No.2,3,5 were not improved with your solution<\/a><\/li>\n<\/ul>\n<\/div>\n<\/div>\n<div id=\"content_adoc\">\n<div id=\"preamble\">\n<div class=\"sectionbody\">\n<div class=\"paragraph data-line-7\">\n<p>Nobuyuki SAMBUICHI<\/p>\n<\/div>\n<div class=\"paragraph data-line-9\">\n<p><a href=\"https:\/\/www.iso.org\/committee\/5648297.html\" data-href=\"https:\/\/www.iso.org\/committee\/5648297.html\">ISO\/TC295 Audit data services<\/a>\/SG1 Semantic model Convener<\/p>\n<\/div>\n<div class=\"paragraph data-line-11\">\n<p>I recommend the result for  4 currency codes, but it is important in designing a semantic data model to understand what kind of results will be achieved if the model is designed on the premise of <strong>normalization<\/strong>.<\/p>\n<\/div>\n<\/div>\n<\/div>\n<div class=\"sect1 data-line-13\">\n<h2 id=\"_refine_trial_balance_table\">1. Refine Trial Balance Table<\/h2>\n<div class=\"sectionbody\">\n<div class=\"paragraph data-line-15\">\n<div class=\"title\">User<\/div>\n<p>Can you point out the problems with following table definition?<br \/>\nPK means the primary key. REF means reference identifier.<\/p>\n<\/div>\n<div class=\"paragraph data-line-18\">\n<p>Can you refine this table design?<br \/>\nRequirements are;<br \/>\n1. simple structure from the point of view of object class and association among object class.<br \/>\n2. easy to navigate to get necessary data.<\/p>\n<\/div>\n<div class=\"paragraph data-line-23\">\n<p>Explain how your design solves current problems and show how to retrieve Beginning_Functional_Amount and Ending_Functional_Amount with Functional_Currency_Code for specified GL_Account_Number, Fiscal_Year and Accounting_Period.<\/p>\n<\/div>\n<table class=\"tableblock frame-all grid-all stretch data-line-26\">\n<colgroup>\n<col style=\"width: 6.6666%;\">\n<col style=\"width: 6.6666%;\">\n<col style=\"width: 26.6666%;\">\n<col style=\"width: 6.6666%;\">\n<col style=\"width: 53.3336%;\">\n<\/colgroup>\n<tbody>\n<tr>\n<td class=\"tableblock halign-center valign-top\">\n<p class=\"tableblock\">No.<\/p>\n<\/td>\n<td class=\"tableblock halign-center valign-top\">\n<p class=\"tableblock\">key<\/p>\n<\/td>\n<td class=\"tableblock halign-left valign-top\">\n<p class=\"tableblock\">Name<\/p>\n<\/td>\n<td class=\"tableblock halign-center valign-top\">\n<p class=\"tableblock\">Datatype<\/p>\n<\/td>\n<td class=\"tableblock halign-left valign-top\">\n<p class=\"tableblock\">Description<\/p>\n<\/td>\n<\/tr>\n<tr>\n<td class=\"tableblock halign-center valign-top\">\n<p class=\"tableblock\">1<\/p>\n<\/td>\n<td class=\"tableblock halign-center valign-top\">\n<p class=\"tableblock\">PK\/REF<\/p>\n<\/td>\n<td class=\"tableblock halign-left valign-top\">\n<p class=\"tableblock\">GL_Account_Number<\/p>\n<\/td>\n<td class=\"tableblock halign-center valign-top\">\n<p class=\"tableblock\">String<\/p>\n<\/td>\n<td class=\"tableblock halign-left valign-top\">\n<p class=\"tableblock\">The GL account number. The number is often generated by the system.<\/p>\n<\/td>\n<\/tr>\n<tr>\n<td class=\"tableblock halign-center valign-top\">\n<p class=\"tableblock\">2<\/p>\n<\/td>\n<td class=\"tableblock halign-center valign-top\">\n<p class=\"tableblock\">PK\/REF<\/p>\n<\/td>\n<td class=\"tableblock halign-left valign-top\">\n<p class=\"tableblock\">Fiscal_Year<\/p>\n<\/td>\n<td class=\"tableblock halign-center valign-top\">\n<p class=\"tableblock\">String<\/p>\n<\/td>\n<td class=\"tableblock halign-left valign-top\">\n<p class=\"tableblock\">Fiscal year in which the Balance_As_Of_Date occurs.<\/p>\n<\/td>\n<\/tr>\n<tr>\n<td class=\"tableblock halign-center valign-top\">\n<p class=\"tableblock\">3<\/p>\n<\/td>\n<td class=\"tableblock halign-center valign-top\">\n<p class=\"tableblock\">PK\/REF<\/p>\n<\/td>\n<td class=\"tableblock halign-left valign-top\">\n<p class=\"tableblock\">Accounting_Period<\/p>\n<\/td>\n<td class=\"tableblock halign-center valign-top\">\n<p class=\"tableblock\">String<\/p>\n<\/td>\n<td class=\"tableblock halign-left valign-top\">\n<p class=\"tableblock\">Accounting period in which the Balance_As_Of_Date occurs.<\/p>\n<\/td>\n<\/tr>\n<tr>\n<td class=\"tableblock halign-center valign-top\">\n<p class=\"tableblock\">4<\/p>\n<\/td>\n<td class=\"tableblock halign-center valign-top\"><\/td>\n<td class=\"tableblock halign-left valign-top\">\n<p class=\"tableblock\">Balance_As_Of_Date<\/p>\n<\/td>\n<td class=\"tableblock halign-center valign-top\">\n<p class=\"tableblock\">Date<\/p>\n<\/td>\n<td class=\"tableblock halign-left valign-top\">\n<p class=\"tableblock\">The date through which the provided balance reflects account activity.<\/p>\n<\/td>\n<\/tr>\n<tr>\n<td class=\"tableblock halign-center valign-top\">\n<p class=\"tableblock\">5<\/p>\n<\/td>\n<td class=\"tableblock halign-center valign-top\"><\/td>\n<td class=\"tableblock halign-left valign-top\">\n<p class=\"tableblock\">Functional_Currency_Code<\/p>\n<\/td>\n<td class=\"tableblock halign-center valign-top\">\n<p class=\"tableblock\">String<\/p>\n<\/td>\n<td class=\"tableblock halign-left valign-top\">\n<p class=\"tableblock\">The code of functional or group currency related to the balance (ISO 4217).<\/p>\n<\/td>\n<\/tr>\n<tr>\n<td class=\"tableblock halign-center valign-top\">\n<p class=\"tableblock\">6<\/p>\n<\/td>\n<td class=\"tableblock halign-center valign-top\"><\/td>\n<td class=\"tableblock halign-left valign-top\">\n<p class=\"tableblock\">Reporting_Currency_Code<\/p>\n<\/td>\n<td class=\"tableblock halign-center valign-top\">\n<p class=\"tableblock\">String<\/p>\n<\/td>\n<td class=\"tableblock halign-left valign-top\">\n<p class=\"tableblock\">The code of currency used for nonconsolidated reporting as opposed to functional, consolidated reporting, local or actual amounts (ISO 4217).<\/p>\n<\/td>\n<\/tr>\n<tr>\n<td class=\"tableblock halign-center valign-top\">\n<p class=\"tableblock\">7<\/p>\n<\/td>\n<td class=\"tableblock halign-center valign-top\"><\/td>\n<td class=\"tableblock halign-left valign-top\">\n<p class=\"tableblock\">Local_Currency_Code<\/p>\n<\/td>\n<td class=\"tableblock halign-center valign-top\">\n<p class=\"tableblock\">String<\/p>\n<\/td>\n<td class=\"tableblock halign-left valign-top\">\n<p class=\"tableblock\">The code of currency used for local country reporting requirements (ISO 4217).<\/p>\n<\/td>\n<\/tr>\n<tr>\n<td class=\"tableblock halign-center valign-top\">\n<p class=\"tableblock\">8<\/p>\n<\/td>\n<td class=\"tableblock halign-center valign-top\"><\/td>\n<td class=\"tableblock halign-left valign-top\">\n<p class=\"tableblock\">Transaction_Currency_Code<\/p>\n<\/td>\n<td class=\"tableblock halign-center valign-top\">\n<p class=\"tableblock\">String<\/p>\n<\/td>\n<td class=\"tableblock halign-left valign-top\">\n<p class=\"tableblock\">The code of currency used in actual transaction (ISO 4217).<\/p>\n<\/td>\n<\/tr>\n<tr>\n<td class=\"tableblock halign-center valign-top\">\n<p class=\"tableblock\">9<\/p>\n<\/td>\n<td class=\"tableblock halign-center valign-top\"><\/td>\n<td class=\"tableblock halign-left valign-top\">\n<p class=\"tableblock\">Beginning_Functional_Amount<\/p>\n<\/td>\n<td class=\"tableblock halign-center valign-top\">\n<p class=\"tableblock\">Decimal<\/p>\n<\/td>\n<td class=\"tableblock halign-left valign-top\">\n<p class=\"tableblock\">Beginning balance amount of the period (same amount as the ending balance from the prior period) recorded in the functional or group currency. No multi-currency translation should be performed on this amount because all are recorded in a single currency.<\/p>\n<\/td>\n<\/tr>\n<tr>\n<td class=\"tableblock halign-center valign-top\">\n<p class=\"tableblock\">10<\/p>\n<\/td>\n<td class=\"tableblock halign-center valign-top\"><\/td>\n<td class=\"tableblock halign-left valign-top\">\n<p class=\"tableblock\">Beginning_Reporting_Amount<\/p>\n<\/td>\n<td class=\"tableblock halign-center valign-top\">\n<p class=\"tableblock\">Decimal<\/p>\n<\/td>\n<td class=\"tableblock halign-left valign-top\">\n<p class=\"tableblock\">Beginning balance amount for the period in the currency used for statutory reporting.<\/p>\n<\/td>\n<\/tr>\n<tr>\n<td class=\"tableblock halign-center valign-top\">\n<p class=\"tableblock\">11<\/p>\n<\/td>\n<td class=\"tableblock halign-center valign-top\"><\/td>\n<td class=\"tableblock halign-left valign-top\">\n<p class=\"tableblock\">Beginning_Local_Amount<\/p>\n<\/td>\n<td class=\"tableblock halign-center valign-top\">\n<p class=\"tableblock\">Decimal<\/p>\n<\/td>\n<td class=\"tableblock halign-left valign-top\">\n<p class=\"tableblock\">Beginning balance amount for the period in the local currency used for multi-currency tracking.<\/p>\n<\/td>\n<\/tr>\n<tr>\n<td class=\"tableblock halign-center valign-top\">\n<p class=\"tableblock\">12<\/p>\n<\/td>\n<td class=\"tableblock halign-center valign-top\"><\/td>\n<td class=\"tableblock halign-left valign-top\">\n<p class=\"tableblock\">Beginning_Transaction_Amount<\/p>\n<\/td>\n<td class=\"tableblock halign-center valign-top\">\n<p class=\"tableblock\">Decimal<\/p>\n<\/td>\n<td class=\"tableblock halign-left valign-top\">\n<p class=\"tableblock\">Beginning balance amount for the period in the transaction currency used for multi-currency tracking.<\/p>\n<\/td>\n<\/tr>\n<tr>\n<td class=\"tableblock halign-center valign-top\">\n<p class=\"tableblock\">13<\/p>\n<\/td>\n<td class=\"tableblock halign-center valign-top\"><\/td>\n<td class=\"tableblock halign-left valign-top\">\n<p class=\"tableblock\">Ending_Functional_Amount<\/p>\n<\/td>\n<td class=\"tableblock halign-center valign-top\">\n<p class=\"tableblock\">Decimal<\/p>\n<\/td>\n<td class=\"tableblock halign-left valign-top\">\n<p class=\"tableblock\">Ending balance amount for the period recorded in the functional or group currency. No multi-currency translation should be performed on this amount because all are recorded in a single currency.<\/p>\n<\/td>\n<\/tr>\n<tr>\n<td class=\"tableblock halign-center valign-top\">\n<p class=\"tableblock\">14<\/p>\n<\/td>\n<td class=\"tableblock halign-center valign-top\"><\/td>\n<td class=\"tableblock halign-left valign-top\">\n<p class=\"tableblock\">Ending_Reporting_Amount<\/p>\n<\/td>\n<td class=\"tableblock halign-center valign-top\">\n<p class=\"tableblock\">Decimal<\/p>\n<\/td>\n<td class=\"tableblock halign-left valign-top\">\n<p class=\"tableblock\">Ending balance amount for the period in the currency used for statutory reporting.<\/p>\n<\/td>\n<\/tr>\n<tr>\n<td class=\"tableblock halign-center valign-top\">\n<p class=\"tableblock\">15<\/p>\n<\/td>\n<td class=\"tableblock halign-center valign-top\"><\/td>\n<td class=\"tableblock halign-left valign-top\">\n<p class=\"tableblock\">Ending_Local_Amount<\/p>\n<\/td>\n<td class=\"tableblock halign-center valign-top\">\n<p class=\"tableblock\">Decimal<\/p>\n<\/td>\n<td class=\"tableblock halign-left valign-top\">\n<p class=\"tableblock\">Ending balance amount for the period in local currency used for multicurrency tracking.<\/p>\n<\/td>\n<\/tr>\n<tr>\n<td class=\"tableblock halign-center valign-top\">\n<p class=\"tableblock\">16<\/p>\n<\/td>\n<td class=\"tableblock halign-center valign-top\"><\/td>\n<td class=\"tableblock halign-left valign-top\">\n<p class=\"tableblock\">Ending_Transaction_Amount<\/p>\n<\/td>\n<td class=\"tableblock halign-center valign-top\">\n<p class=\"tableblock\">Decimal<\/p>\n<\/td>\n<td class=\"tableblock halign-left valign-top\">\n<p class=\"tableblock\">Ending balance amount for the period in the transaction currency used for multi-currency tracking.<\/p>\n<\/td>\n<\/tr>\n<tr>\n<td class=\"tableblock halign-center valign-top\">\n<p class=\"tableblock\">17<\/p>\n<\/td>\n<td class=\"tableblock halign-center valign-top\"><\/td>\n<td class=\"tableblock halign-left valign-top\">\n<p class=\"tableblock\">Business_Segment_X<\/p>\n<\/td>\n<td class=\"tableblock halign-center valign-top\">\n<p class=\"tableblock\">String<\/p>\n<\/td>\n<td class=\"tableblock halign-left valign-top\">\n<p class=\"tableblock\">A reserved field that shall be used for business segments\/structures. The \u2018X\u2019 signifies an organizational level. Each number used to replace the \u2018X\u2019 is associated with a unique reference level. For example, division, department, business unit, purchasing organization, project or legal entity.<\/p>\n<\/td>\n<\/tr>\n<\/tbody>\n<\/table>\n<div class=\"paragraph data-line-137\">\n<div class=\"title\">ChatGPT<\/div>\n<p>The problems with the current table definition are:<\/p>\n<\/div>\n<div class=\"paragraph data-line-139\">\n<p>1 <strong>Composite Primary Key<\/strong>: The table uses a composite primary key (GL_Account_Number, Fiscal_Year, Accounting_Period). This can lead to complexity in querying and may not be compatible with certain ORM tools.<\/p>\n<\/div>\n<div class=\"paragraph data-line-141\">\n<p>2 <strong>Currency Redundancy<\/strong>: There are multiple fields storing currency information (Functional_Currency_Code, Reporting_Currency_Code, Local_Currency_Code, Transaction_Currency_Code). This could result in data duplication and inconsistency.<\/p>\n<\/div>\n<div class=\"paragraph data-line-143\">\n<p>3 <strong>Balance Redundancy<\/strong>: Similar to the currency issue, balance amounts are stored in multiple fields in different currencies (Beginning_Functional_Amount, Beginning_Reporting_Amount, Beginning_Local_Amount, Beginning_Transaction_Amount, etc.). This can also lead to data inconsistency.<\/p>\n<\/div>\n<div class=\"paragraph data-line-145\">\n<p>To refine this design, we can create two tables: a <code>Balance<\/code> table and a <code>Currency<\/code> table. The <code>Balance<\/code> table will store balance amounts and link to a <code>Currency<\/code> record, and the <code>Currency<\/code> table will store currency codes and descriptions.<\/p>\n<\/div>\n<div class=\"paragraph data-line-147\">\n<p>1 <code>Balance<\/code> table:<\/p>\n<\/div>\n<table class=\"tableblock frame-all grid-all stretch data-line-150\">\n<colgroup>\n<col style=\"width: 6.6666%;\">\n<col style=\"width: 6.6666%;\">\n<col style=\"width: 26.6666%;\">\n<col style=\"width: 6.6666%;\">\n<col style=\"width: 53.3336%;\">\n<\/colgroup>\n<tbody>\n<tr>\n<td class=\"tableblock halign-center valign-top\">\n<p class=\"tableblock\">No.<\/p>\n<\/td>\n<td class=\"tableblock halign-center valign-top\">\n<p class=\"tableblock\">key<\/p>\n<\/td>\n<td class=\"tableblock halign-left valign-top\">\n<p class=\"tableblock\">Name<\/p>\n<\/td>\n<td class=\"tableblock halign-center valign-top\">\n<p class=\"tableblock\">Datatype<\/p>\n<\/td>\n<td class=\"tableblock halign-left valign-top\">\n<p class=\"tableblock\">Description<\/p>\n<\/td>\n<\/tr>\n<tr>\n<td class=\"tableblock halign-center valign-top\">\n<p class=\"tableblock\">1<\/p>\n<\/td>\n<td class=\"tableblock halign-center valign-top\">\n<p class=\"tableblock\">PK<\/p>\n<\/td>\n<td class=\"tableblock halign-left valign-top\">\n<p class=\"tableblock\">Balance_ID<\/p>\n<\/td>\n<td class=\"tableblock halign-center valign-top\">\n<p class=\"tableblock\">String<\/p>\n<\/td>\n<td class=\"tableblock halign-left valign-top\">\n<p class=\"tableblock\">Unique balance record ID<\/p>\n<\/td>\n<\/tr>\n<tr>\n<td class=\"tableblock halign-center valign-top\">\n<p class=\"tableblock\">2<\/p>\n<\/td>\n<td class=\"tableblock halign-center valign-top\"><\/td>\n<td class=\"tableblock halign-left valign-top\">\n<p class=\"tableblock\">GL_Account_Number<\/p>\n<\/td>\n<td class=\"tableblock halign-center valign-top\">\n<p class=\"tableblock\">String<\/p>\n<\/td>\n<td class=\"tableblock halign-left valign-top\">\n<p class=\"tableblock\">The GL account number.<\/p>\n<\/td>\n<\/tr>\n<tr>\n<td class=\"tableblock halign-center valign-top\">\n<p class=\"tableblock\">3<\/p>\n<\/td>\n<td class=\"tableblock halign-center valign-top\"><\/td>\n<td class=\"tableblock halign-left valign-top\">\n<p class=\"tableblock\">Fiscal_Year<\/p>\n<\/td>\n<td class=\"tableblock halign-center valign-top\">\n<p class=\"tableblock\">String<\/p>\n<\/td>\n<td class=\"tableblock halign-left valign-top\">\n<p class=\"tableblock\">Fiscal year in which the Balance_As_Of_Date occurs.<\/p>\n<\/td>\n<\/tr>\n<tr>\n<td class=\"tableblock halign-center valign-top\">\n<p class=\"tableblock\">4<\/p>\n<\/td>\n<td class=\"tableblock halign-center valign-top\"><\/td>\n<td class=\"tableblock halign-left valign-top\">\n<p class=\"tableblock\">Accounting_Period<\/p>\n<\/td>\n<td class=\"tableblock halign-center valign-top\">\n<p class=\"tableblock\">String<\/p>\n<\/td>\n<td class=\"tableblock halign-left valign-top\">\n<p class=\"tableblock\">Accounting period in which the Balance_As_Of_Date occurs.<\/p>\n<\/td>\n<\/tr>\n<tr>\n<td class=\"tableblock halign-center valign-top\">\n<p class=\"tableblock\">5<\/p>\n<\/td>\n<td class=\"tableblock halign-center valign-top\"><\/td>\n<td class=\"tableblock halign-left valign-top\">\n<p class=\"tableblock\">Balance_As_Of_Date<\/p>\n<\/td>\n<td class=\"tableblock halign-center valign-top\">\n<p class=\"tableblock\">Date<\/p>\n<\/td>\n<td class=\"tableblock halign-left valign-top\">\n<p class=\"tableblock\">The date through which the provided balance reflects account activity.<\/p>\n<\/td>\n<\/tr>\n<tr>\n<td class=\"tableblock halign-center valign-top\">\n<p class=\"tableblock\">6<\/p>\n<\/td>\n<td class=\"tableblock halign-center valign-top\">\n<p class=\"tableblock\">REF<\/p>\n<\/td>\n<td class=\"tableblock halign-left valign-top\">\n<p class=\"tableblock\">Currency_ID<\/p>\n<\/td>\n<td class=\"tableblock halign-center valign-top\">\n<p class=\"tableblock\">String<\/p>\n<\/td>\n<td class=\"tableblock halign-left valign-top\">\n<p class=\"tableblock\">Reference to the Currency table<\/p>\n<\/td>\n<\/tr>\n<tr>\n<td class=\"tableblock halign-center valign-top\">\n<p class=\"tableblock\">7<\/p>\n<\/td>\n<td class=\"tableblock halign-center valign-top\"><\/td>\n<td class=\"tableblock halign-left valign-top\">\n<p class=\"tableblock\">Beginning_Amount<\/p>\n<\/td>\n<td class=\"tableblock halign-center valign-top\">\n<p class=\"tableblock\">Decimal<\/p>\n<\/td>\n<td class=\"tableblock halign-left valign-top\">\n<p class=\"tableblock\">Beginning balance amount of the period<\/p>\n<\/td>\n<\/tr>\n<tr>\n<td class=\"tableblock halign-center valign-top\">\n<p class=\"tableblock\">8<\/p>\n<\/td>\n<td class=\"tableblock halign-center valign-top\"><\/td>\n<td class=\"tableblock halign-left valign-top\">\n<p class=\"tableblock\">Ending_Amount<\/p>\n<\/td>\n<td class=\"tableblock halign-center valign-top\">\n<p class=\"tableblock\">Decimal<\/p>\n<\/td>\n<td class=\"tableblock halign-left valign-top\">\n<p class=\"tableblock\">Ending balance amount for the period<\/p>\n<\/td>\n<\/tr>\n<tr>\n<td class=\"tableblock halign-center valign-top\">\n<p class=\"tableblock\">9<\/p>\n<\/td>\n<td class=\"tableblock halign-center valign-top\"><\/td>\n<td class=\"tableblock halign-left valign-top\">\n<p class=\"tableblock\">Business_Segment_X<\/p>\n<\/td>\n<td class=\"tableblock halign-center valign-top\">\n<p class=\"tableblock\">String<\/p>\n<\/td>\n<td class=\"tableblock halign-left valign-top\">\n<p class=\"tableblock\">A reserved field for business segments\/structures<\/p>\n<\/td>\n<\/tr>\n<\/tbody>\n<\/table>\n<div class=\"paragraph data-line-163\">\n<p>2 <code>Currency<\/code> table:<\/p>\n<\/div>\n<table class=\"tableblock frame-all grid-all stretch data-line-166\">\n<colgroup>\n<col style=\"width: 6.6666%;\">\n<col style=\"width: 6.6666%;\">\n<col style=\"width: 26.6666%;\">\n<col style=\"width: 6.6666%;\">\n<col style=\"width: 53.3336%;\">\n<\/colgroup>\n<tbody>\n<tr>\n<td class=\"tableblock halign-center valign-top\">\n<p class=\"tableblock\">No.<\/p>\n<\/td>\n<td class=\"tableblock halign-center valign-top\">\n<p class=\"tableblock\">key<\/p>\n<\/td>\n<td class=\"tableblock halign-left valign-top\">\n<p class=\"tableblock\">Name<\/p>\n<\/td>\n<td class=\"tableblock halign-center valign-top\">\n<p class=\"tableblock\">Datatype<\/p>\n<\/td>\n<td class=\"tableblock halign-left valign-top\">\n<p class=\"tableblock\">Description<\/p>\n<\/td>\n<\/tr>\n<tr>\n<td class=\"tableblock halign-center valign-top\">\n<p class=\"tableblock\">1<\/p>\n<\/td>\n<td class=\"tableblock halign-center valign-top\">\n<p class=\"tableblock\">PK<\/p>\n<\/td>\n<td class=\"tableblock halign-left valign-top\">\n<p class=\"tableblock\">Currency_ID<\/p>\n<\/td>\n<td class=\"tableblock halign-center valign-top\">\n<p class=\"tableblock\">String<\/p>\n<\/td>\n<td class=\"tableblock halign-left valign-top\">\n<p class=\"tableblock\">Unique currency record ID<\/p>\n<\/td>\n<\/tr>\n<tr>\n<td class=\"tableblock halign-center valign-top\">\n<p class=\"tableblock\">2<\/p>\n<\/td>\n<td class=\"tableblock halign-center valign-top\"><\/td>\n<td class=\"tableblock halign-left valign-top\">\n<p class=\"tableblock\">Functional_Code<\/p>\n<\/td>\n<td class=\"tableblock halign-center valign-top\">\n<p class=\"tableblock\">String<\/p>\n<\/td>\n<td class=\"tableblock halign-left valign-top\">\n<p class=\"tableblock\">The code of functional currency (ISO 4217).<\/p>\n<\/td>\n<\/tr>\n<tr>\n<td class=\"tableblock halign-center valign-top\">\n<p class=\"tableblock\">3<\/p>\n<\/td>\n<td class=\"tableblock halign-center valign-top\"><\/td>\n<td class=\"tableblock halign-left valign-top\">\n<p class=\"tableblock\">Reporting_Code<\/p>\n<\/td>\n<td class=\"tableblock halign-center valign-top\">\n<p class=\"tableblock\">String<\/p>\n<\/td>\n<td class=\"tableblock halign-left valign-top\">\n<p class=\"tableblock\">The code of reporting currency (ISO 4217).<\/p>\n<\/td>\n<\/tr>\n<tr>\n<td class=\"tableblock halign-center valign-top\">\n<p class=\"tableblock\">4<\/p>\n<\/td>\n<td class=\"tableblock halign-center valign-top\"><\/td>\n<td class=\"tableblock halign-left valign-top\">\n<p class=\"tableblock\">Local_Code<\/p>\n<\/td>\n<td class=\"tableblock halign-center valign-top\">\n<p class=\"tableblock\">String<\/p>\n<\/td>\n<td class=\"tableblock halign-left valign-top\">\n<p class=\"tableblock\">The code of local currency (ISO 4217).<\/p>\n<\/td>\n<\/tr>\n<tr>\n<td class=\"tableblock halign-center valign-top\">\n<p class=\"tableblock\">5<\/p>\n<\/td>\n<td class=\"tableblock halign-center valign-top\"><\/td>\n<td class=\"tableblock halign-left valign-top\">\n<p class=\"tableblock\">Transaction_Code<\/p>\n<\/td>\n<td class=\"tableblock halign-center valign-top\">\n<p class=\"tableblock\">String<\/p>\n<\/td>\n<td class=\"tableblock halign-left valign-top\">\n<p class=\"tableblock\">The code of transaction currency (ISO 4217).<\/p>\n<\/td>\n<\/tr>\n<\/tbody>\n<\/table>\n<div class=\"paragraph data-line-175\">\n<p>To retrieve <code>Beginning_Amount<\/code> and <code>Ending_Amount<\/code> with <code>Functional_Code<\/code> for specified <code>GL_Account_Number<\/code>, <code>Fiscal_Year<\/code>, and <code>Accounting_Period<\/code>, you would use a query like:<\/p>\n<\/div>\n<div class=\"listingblock data-line-177\">\n<div class=\"content\">\n<pre class=\"highlight\"><code class=\"language-SQL\" data-lang=\"SQL\">SELECT b.Beginning_Amount, b.Ending_Amount, c.Functional_Code\r\nFROM Balance b\r\nJOIN Currency c ON b.Currency_ID = c.Currency_ID\r\nWHERE b.GL_Account_Number = :GL_Account_Number AND\r\n      b.Fiscal_Year = :Fiscal_Year AND\r\n      b.Accounting_Period = :Accounting_Period;<\/code><\/pre>\n<\/div>\n<\/div>\n<div class=\"paragraph data-line-186\">\n<p>This design reduces the redundancy of currency codes and balance amounts and simplifies the primary key to a single column. This will make it easier to manage data consistency, query data, and work with ORM tools<\/p>\n<\/div>\n<\/div>\n<\/div>\n<div class=\"sect1 data-line-188\">\n<h2 id=\"_missing_4_currency_codes\">2. Missing 4 currency codes<\/h2>\n<div class=\"sectionbody\">\n<div class=\"paragraph data-line-190\">\n<div class=\"title\">User<\/div>\n<p>Balance table shall contain balance amounts for four types of currency. We cannot expect Beginning_Amount is in functional currency.<\/p>\n<\/div>\n<div class=\"paragraph data-line-193\">\n<div class=\"title\">ChatGPT<\/div>\n<p>Apologies for the misunderstanding. I see now that each balance record tracks the amounts in four different currencies. We should indeed represent this in our tables.<\/p>\n<\/div>\n<div class=\"paragraph data-line-195\">\n<p>Let&#8217;s refine the <code>Balance<\/code> table to include balance amounts for each currency type:<\/p>\n<\/div>\n<div class=\"paragraph data-line-197\">\n<p>1 <code>Balance<\/code> table:<\/p>\n<\/div>\n<table class=\"tableblock frame-all grid-all stretch data-line-200\">\n<colgroup>\n<col style=\"width: 6.6666%;\">\n<col style=\"width: 6.6666%;\">\n<col style=\"width: 26.6666%;\">\n<col style=\"width: 6.6666%;\">\n<col style=\"width: 53.3336%;\">\n<\/colgroup>\n<tbody>\n<tr>\n<td class=\"tableblock halign-center valign-top\">\n<p class=\"tableblock\">No.<\/p>\n<\/td>\n<td class=\"tableblock halign-center valign-top\">\n<p class=\"tableblock\">key<\/p>\n<\/td>\n<td class=\"tableblock halign-left valign-top\">\n<p class=\"tableblock\">Name<\/p>\n<\/td>\n<td class=\"tableblock halign-center valign-top\">\n<p class=\"tableblock\">Datatype<\/p>\n<\/td>\n<td class=\"tableblock halign-left valign-top\">\n<p class=\"tableblock\">Description<\/p>\n<\/td>\n<\/tr>\n<tr>\n<td class=\"tableblock halign-center valign-top\">\n<p class=\"tableblock\">1<\/p>\n<\/td>\n<td class=\"tableblock halign-center valign-top\">\n<p class=\"tableblock\">PK<\/p>\n<\/td>\n<td class=\"tableblock halign-left valign-top\">\n<p class=\"tableblock\">Balance_ID<\/p>\n<\/td>\n<td class=\"tableblock halign-center valign-top\">\n<p class=\"tableblock\">String<\/p>\n<\/td>\n<td class=\"tableblock halign-left valign-top\">\n<p class=\"tableblock\">Unique balance record ID<\/p>\n<\/td>\n<\/tr>\n<tr>\n<td class=\"tableblock halign-center valign-top\">\n<p class=\"tableblock\">2<\/p>\n<\/td>\n<td class=\"tableblock halign-center valign-top\"><\/td>\n<td class=\"tableblock halign-left valign-top\">\n<p class=\"tableblock\">GL_Account_Number<\/p>\n<\/td>\n<td class=\"tableblock halign-center valign-top\">\n<p class=\"tableblock\">String<\/p>\n<\/td>\n<td class=\"tableblock halign-left valign-top\">\n<p class=\"tableblock\">The GL account number.<\/p>\n<\/td>\n<\/tr>\n<tr>\n<td class=\"tableblock halign-center valign-top\">\n<p class=\"tableblock\">3<\/p>\n<\/td>\n<td class=\"tableblock halign-center valign-top\"><\/td>\n<td class=\"tableblock halign-left valign-top\">\n<p class=\"tableblock\">Fiscal_Year<\/p>\n<\/td>\n<td class=\"tableblock halign-center valign-top\">\n<p class=\"tableblock\">String<\/p>\n<\/td>\n<td class=\"tableblock halign-left valign-top\">\n<p class=\"tableblock\">Fiscal year in which the Balance_As_Of_Date occurs.<\/p>\n<\/td>\n<\/tr>\n<tr>\n<td class=\"tableblock halign-center valign-top\">\n<p class=\"tableblock\">4<\/p>\n<\/td>\n<td class=\"tableblock halign-center valign-top\"><\/td>\n<td class=\"tableblock halign-left valign-top\">\n<p class=\"tableblock\">Accounting_Period<\/p>\n<\/td>\n<td class=\"tableblock halign-center valign-top\">\n<p class=\"tableblock\">String<\/p>\n<\/td>\n<td class=\"tableblock halign-left valign-top\">\n<p class=\"tableblock\">Accounting period in which the Balance_As_Of_Date occurs.<\/p>\n<\/td>\n<\/tr>\n<tr>\n<td class=\"tableblock halign-center valign-top\">\n<p class=\"tableblock\">5<\/p>\n<\/td>\n<td class=\"tableblock halign-center valign-top\"><\/td>\n<td class=\"tableblock halign-left valign-top\">\n<p class=\"tableblock\">Balance_As_Of_Date<\/p>\n<\/td>\n<td class=\"tableblock halign-center valign-top\">\n<p class=\"tableblock\">Date<\/p>\n<\/td>\n<td class=\"tableblock halign-left valign-top\">\n<p class=\"tableblock\">The date through which the provided balance reflects account activity.<\/p>\n<\/td>\n<\/tr>\n<tr>\n<td class=\"tableblock halign-center valign-top\">\n<p class=\"tableblock\">6<\/p>\n<\/td>\n<td class=\"tableblock halign-center valign-top\"><\/td>\n<td class=\"tableblock halign-left valign-top\">\n<p class=\"tableblock\">Beginning_Functional_Amount<\/p>\n<\/td>\n<td class=\"tableblock halign-center valign-top\">\n<p class=\"tableblock\">Decimal<\/p>\n<\/td>\n<td class=\"tableblock halign-left valign-top\">\n<p class=\"tableblock\">Beginning balance amount of the period in functional currency<\/p>\n<\/td>\n<\/tr>\n<tr>\n<td class=\"tableblock halign-center valign-top\">\n<p class=\"tableblock\">7<\/p>\n<\/td>\n<td class=\"tableblock halign-center valign-top\"><\/td>\n<td class=\"tableblock halign-left valign-top\">\n<p class=\"tableblock\">Beginning_Reporting_Amount<\/p>\n<\/td>\n<td class=\"tableblock halign-center valign-top\">\n<p class=\"tableblock\">Decimal<\/p>\n<\/td>\n<td class=\"tableblock halign-left valign-top\">\n<p class=\"tableblock\">Beginning balance amount of the period in reporting currency<\/p>\n<\/td>\n<\/tr>\n<tr>\n<td class=\"tableblock halign-center valign-top\">\n<p class=\"tableblock\">8<\/p>\n<\/td>\n<td class=\"tableblock halign-center valign-top\"><\/td>\n<td class=\"tableblock halign-left valign-top\">\n<p class=\"tableblock\">Beginning_Local_Amount<\/p>\n<\/td>\n<td class=\"tableblock halign-center valign-top\">\n<p class=\"tableblock\">Decimal<\/p>\n<\/td>\n<td class=\"tableblock halign-left valign-top\">\n<p class=\"tableblock\">Beginning balance amount of the period in local currency<\/p>\n<\/td>\n<\/tr>\n<tr>\n<td class=\"tableblock halign-center valign-top\">\n<p class=\"tableblock\">9<\/p>\n<\/td>\n<td class=\"tableblock halign-center valign-top\"><\/td>\n<td class=\"tableblock halign-left valign-top\">\n<p class=\"tableblock\">Beginning_Transaction_Amount<\/p>\n<\/td>\n<td class=\"tableblock halign-center valign-top\">\n<p class=\"tableblock\">Decimal<\/p>\n<\/td>\n<td class=\"tableblock halign-left valign-top\">\n<p class=\"tableblock\">Beginning balance amount of the period in transaction currency<\/p>\n<\/td>\n<\/tr>\n<tr>\n<td class=\"tableblock halign-center valign-top\">\n<p class=\"tableblock\">10<\/p>\n<\/td>\n<td class=\"tableblock halign-center valign-top\"><\/td>\n<td class=\"tableblock halign-left valign-top\">\n<p class=\"tableblock\">Ending_Functional_Amount<\/p>\n<\/td>\n<td class=\"tableblock halign-center valign-top\">\n<p class=\"tableblock\">Decimal<\/p>\n<\/td>\n<td class=\"tableblock halign-left valign-top\">\n<p class=\"tableblock\">Ending balance amount for the period in functional currency<\/p>\n<\/td>\n<\/tr>\n<tr>\n<td class=\"tableblock halign-center valign-top\">\n<p class=\"tableblock\">11<\/p>\n<\/td>\n<td class=\"tableblock halign-center valign-top\"><\/td>\n<td class=\"tableblock halign-left valign-top\">\n<p class=\"tableblock\">Ending_Reporting_Amount<\/p>\n<\/td>\n<td class=\"tableblock halign-center valign-top\">\n<p class=\"tableblock\">Decimal<\/p>\n<\/td>\n<td class=\"tableblock halign-left valign-top\">\n<p class=\"tableblock\">Ending balance amount for the period in reporting currency<\/p>\n<\/td>\n<\/tr>\n<tr>\n<td class=\"tableblock halign-center valign-top\">\n<p class=\"tableblock\">12<\/p>\n<\/td>\n<td class=\"tableblock halign-center valign-top\"><\/td>\n<td class=\"tableblock halign-left valign-top\">\n<p class=\"tableblock\">Ending_Local_Amount<\/p>\n<\/td>\n<td class=\"tableblock halign-center valign-top\">\n<p class=\"tableblock\">Decimal<\/p>\n<\/td>\n<td class=\"tableblock halign-left valign-top\">\n<p class=\"tableblock\">Ending balance amount for the period in local currency<\/p>\n<\/td>\n<\/tr>\n<tr>\n<td class=\"tableblock halign-center valign-top\">\n<p class=\"tableblock\">13<\/p>\n<\/td>\n<td class=\"tableblock halign-center valign-top\"><\/td>\n<td class=\"tableblock halign-left valign-top\">\n<p class=\"tableblock\">Ending_Transaction_Amount<\/p>\n<\/td>\n<td class=\"tableblock halign-center valign-top\">\n<p class=\"tableblock\">Decimal<\/p>\n<\/td>\n<td class=\"tableblock halign-left valign-top\">\n<p class=\"tableblock\">Ending balance amount for the period in transaction currency<\/p>\n<\/td>\n<\/tr>\n<tr>\n<td class=\"tableblock halign-center valign-top\">\n<p class=\"tableblock\">14<\/p>\n<\/td>\n<td class=\"tableblock halign-center valign-top\"><\/td>\n<td class=\"tableblock halign-left valign-top\">\n<p class=\"tableblock\">Business_Segment_X<\/p>\n<\/td>\n<td class=\"tableblock halign-center valign-top\">\n<p class=\"tableblock\">String<\/p>\n<\/td>\n<td class=\"tableblock halign-left valign-top\">\n<p class=\"tableblock\">A reserved field for business segments\/structures<\/p>\n<\/td>\n<\/tr>\n<\/tbody>\n<\/table>\n<div class=\"paragraph data-line-218\">\n<p>This modified table can still answer the original query directly:<\/p>\n<\/div>\n<div class=\"listingblock data-line-220\">\n<div class=\"content\">\n<pre class=\"highlight\"><code class=\"language-SQL\" data-lang=\"SQL\">SELECT Beginning_Functional_Amount, Ending_Functional_Amount\r\nFROM Balance\r\nWHERE GL_Account_Number = :GL_Account_Number AND\r\n      Fiscal_Year = :Fiscal_Year AND\r\n      Accounting_Period = :Accounting_Period;<\/code><\/pre>\n<\/div>\n<\/div>\n<div class=\"paragraph data-line-228\">\n<p>This design retains all the original data in a simpler form. Now each balance record directly contains the balance amounts in each of the four types of currency, reducing the need for extra joins or lookups when querying balance data.<\/p>\n<\/div>\n<\/div>\n<\/div>\n<div class=\"sect1 data-line-230\">\n<h2 id=\"_how_to_find_out_functional_currency_code\">3. How to find out functional currency code?<\/h2>\n<div class=\"sectionbody\">\n<div class=\"paragraph data-line-232\">\n<div class=\"title\">User<\/div>\n<p>You forget to specify functional currency. How to find out functional currency code?<\/p>\n<\/div>\n<div class=\"paragraph data-line-235\">\n<div class=\"title\">ChatGPT<\/div>\n<p>My apologies for the oversight. Given that the balance amounts are recorded in different currencies, it does make sense to have currency identifiers for each type of balance. In this case, instead of creating a separate <code>Currency<\/code> table, we can modify the <code>Balance<\/code> table to include currency codes for each amount type:<\/p>\n<\/div>\n<div class=\"paragraph data-line-237\">\n<p>1 <code>Balance<\/code> table:<\/p>\n<\/div>\n<table class=\"tableblock frame-all grid-all stretch data-line-240\">\n<colgroup>\n<col style=\"width: 6.6666%;\">\n<col style=\"width: 6.6666%;\">\n<col style=\"width: 26.6666%;\">\n<col style=\"width: 6.6666%;\">\n<col style=\"width: 53.3336%;\">\n<\/colgroup>\n<tbody>\n<tr>\n<td class=\"tableblock halign-center valign-top\">\n<p class=\"tableblock\">No.<\/p>\n<\/td>\n<td class=\"tableblock halign-center valign-top\">\n<p class=\"tableblock\">key<\/p>\n<\/td>\n<td class=\"tableblock halign-left valign-top\">\n<p class=\"tableblock\">Name<\/p>\n<\/td>\n<td class=\"tableblock halign-center valign-top\">\n<p class=\"tableblock\">Datatype<\/p>\n<\/td>\n<td class=\"tableblock halign-left valign-top\">\n<p class=\"tableblock\">Description<\/p>\n<\/td>\n<\/tr>\n<tr>\n<td class=\"tableblock halign-center valign-top\">\n<p class=\"tableblock\">1<\/p>\n<\/td>\n<td class=\"tableblock halign-center valign-top\">\n<p class=\"tableblock\">PK<\/p>\n<\/td>\n<td class=\"tableblock halign-left valign-top\">\n<p class=\"tableblock\">Balance_ID<\/p>\n<\/td>\n<td class=\"tableblock halign-center valign-top\">\n<p class=\"tableblock\">String<\/p>\n<\/td>\n<td class=\"tableblock halign-left valign-top\">\n<p class=\"tableblock\">Unique balance record ID<\/p>\n<\/td>\n<\/tr>\n<tr>\n<td class=\"tableblock halign-center valign-top\">\n<p class=\"tableblock\">2<\/p>\n<\/td>\n<td class=\"tableblock halign-center valign-top\"><\/td>\n<td class=\"tableblock halign-left valign-top\">\n<p class=\"tableblock\">GL_Account_Number<\/p>\n<\/td>\n<td class=\"tableblock halign-center valign-top\">\n<p class=\"tableblock\">String<\/p>\n<\/td>\n<td class=\"tableblock halign-left valign-top\">\n<p class=\"tableblock\">The GL account number.<\/p>\n<\/td>\n<\/tr>\n<tr>\n<td class=\"tableblock halign-center valign-top\">\n<p class=\"tableblock\">3<\/p>\n<\/td>\n<td class=\"tableblock halign-center valign-top\"><\/td>\n<td class=\"tableblock halign-left valign-top\">\n<p class=\"tableblock\">Fiscal_Year<\/p>\n<\/td>\n<td class=\"tableblock halign-center valign-top\">\n<p class=\"tableblock\">String<\/p>\n<\/td>\n<td class=\"tableblock halign-left valign-top\">\n<p class=\"tableblock\">Fiscal year in which the Balance_As_Of_Date occurs.<\/p>\n<\/td>\n<\/tr>\n<tr>\n<td class=\"tableblock halign-center valign-top\">\n<p class=\"tableblock\">4<\/p>\n<\/td>\n<td class=\"tableblock halign-center valign-top\"><\/td>\n<td class=\"tableblock halign-left valign-top\">\n<p class=\"tableblock\">Accounting_Period<\/p>\n<\/td>\n<td class=\"tableblock halign-center valign-top\">\n<p class=\"tableblock\">String<\/p>\n<\/td>\n<td class=\"tableblock halign-left valign-top\">\n<p class=\"tableblock\">Accounting period in which the Balance_As_Of_Date occurs.<\/p>\n<\/td>\n<\/tr>\n<tr>\n<td class=\"tableblock halign-center valign-top\">\n<p class=\"tableblock\">5<\/p>\n<\/td>\n<td class=\"tableblock halign-center valign-top\"><\/td>\n<td class=\"tableblock halign-left valign-top\">\n<p class=\"tableblock\">Balance_As_Of_Date<\/p>\n<\/td>\n<td class=\"tableblock halign-center valign-top\">\n<p class=\"tableblock\">Date<\/p>\n<\/td>\n<td class=\"tableblock halign-left valign-top\">\n<p class=\"tableblock\">The date through which the provided balance reflects account activity.<\/p>\n<\/td>\n<\/tr>\n<tr>\n<td class=\"tableblock halign-center valign-top\">\n<p class=\"tableblock\">6<\/p>\n<\/td>\n<td class=\"tableblock halign-center valign-top\"><\/td>\n<td class=\"tableblock halign-left valign-top\">\n<p class=\"tableblock\">Functional_Currency_Code<\/p>\n<\/td>\n<td class=\"tableblock halign-center valign-top\">\n<p class=\"tableblock\">String<\/p>\n<\/td>\n<td class=\"tableblock halign-left valign-top\">\n<p class=\"tableblock\">The code of functional currency (ISO 4217).<\/p>\n<\/td>\n<\/tr>\n<tr>\n<td class=\"tableblock halign-center valign-top\">\n<p class=\"tableblock\">7<\/p>\n<\/td>\n<td class=\"tableblock halign-center valign-top\"><\/td>\n<td class=\"tableblock halign-left valign-top\">\n<p class=\"tableblock\">Beginning_Functional_Amount<\/p>\n<\/td>\n<td class=\"tableblock halign-center valign-top\">\n<p class=\"tableblock\">Decimal<\/p>\n<\/td>\n<td class=\"tableblock halign-left valign-top\">\n<p class=\"tableblock\">Beginning balance amount of the period in functional currency<\/p>\n<\/td>\n<\/tr>\n<tr>\n<td class=\"tableblock halign-center valign-top\">\n<p class=\"tableblock\">8<\/p>\n<\/td>\n<td class=\"tableblock halign-center valign-top\"><\/td>\n<td class=\"tableblock halign-left valign-top\">\n<p class=\"tableblock\">Beginning_Reporting_Amount<\/p>\n<\/td>\n<td class=\"tableblock halign-center valign-top\">\n<p class=\"tableblock\">Decimal<\/p>\n<\/td>\n<td class=\"tableblock halign-left valign-top\">\n<p class=\"tableblock\">Beginning balance amount of the period in reporting currency<\/p>\n<\/td>\n<\/tr>\n<tr>\n<td class=\"tableblock halign-center valign-top\">\n<p class=\"tableblock\">9<\/p>\n<\/td>\n<td class=\"tableblock halign-center valign-top\"><\/td>\n<td class=\"tableblock halign-left valign-top\">\n<p class=\"tableblock\">Beginning_Local_Amount<\/p>\n<\/td>\n<td class=\"tableblock halign-center valign-top\">\n<p class=\"tableblock\">Decimal<\/p>\n<\/td>\n<td class=\"tableblock halign-left valign-top\">\n<p class=\"tableblock\">Beginning balance amount of the period in local currency<\/p>\n<\/td>\n<\/tr>\n<tr>\n<td class=\"tableblock halign-center valign-top\">\n<p class=\"tableblock\">10<\/p>\n<\/td>\n<td class=\"tableblock halign-center valign-top\"><\/td>\n<td class=\"tableblock halign-left valign-top\">\n<p class=\"tableblock\">Beginning_Transaction_Amount<\/p>\n<\/td>\n<td class=\"tableblock halign-center valign-top\">\n<p class=\"tableblock\">Decimal<\/p>\n<\/td>\n<td class=\"tableblock halign-left valign-top\">\n<p class=\"tableblock\">Beginning balance amount of the period in transaction currency<\/p>\n<\/td>\n<\/tr>\n<tr>\n<td class=\"tableblock halign-center valign-top\">\n<p class=\"tableblock\">11<\/p>\n<\/td>\n<td class=\"tableblock halign-center valign-top\"><\/td>\n<td class=\"tableblock halign-left valign-top\">\n<p class=\"tableblock\">Ending_Functional_Amount<\/p>\n<\/td>\n<td class=\"tableblock halign-center valign-top\">\n<p class=\"tableblock\">Decimal<\/p>\n<\/td>\n<td class=\"tableblock halign-left valign-top\">\n<p class=\"tableblock\">Ending balance amount for the period in functional currency<\/p>\n<\/td>\n<\/tr>\n<tr>\n<td class=\"tableblock halign-center valign-top\">\n<p class=\"tableblock\">12<\/p>\n<\/td>\n<td class=\"tableblock halign-center valign-top\"><\/td>\n<td class=\"tableblock halign-left valign-top\">\n<p class=\"tableblock\">Ending_Reporting_Amount<\/p>\n<\/td>\n<td class=\"tableblock halign-center valign-top\">\n<p class=\"tableblock\">Decimal<\/p>\n<\/td>\n<td class=\"tableblock halign-left valign-top\">\n<p class=\"tableblock\">Ending balance amount for the period in reporting currency<\/p>\n<\/td>\n<\/tr>\n<tr>\n<td class=\"tableblock halign-center valign-top\">\n<p class=\"tableblock\">13<\/p>\n<\/td>\n<td class=\"tableblock halign-center valign-top\"><\/td>\n<td class=\"tableblock halign-left valign-top\">\n<p class=\"tableblock\">Ending_Local_Amount<\/p>\n<\/td>\n<td class=\"tableblock halign-center valign-top\">\n<p class=\"tableblock\">Decimal<\/p>\n<\/td>\n<td class=\"tableblock halign-left valign-top\">\n<p class=\"tableblock\">Ending balance amount for the period in local currency<\/p>\n<\/td>\n<\/tr>\n<tr>\n<td class=\"tableblock halign-center valign-top\">\n<p class=\"tableblock\">14<\/p>\n<\/td>\n<td class=\"tableblock halign-center valign-top\"><\/td>\n<td class=\"tableblock halign-left valign-top\">\n<p class=\"tableblock\">Ending_Transaction_Amount<\/p>\n<\/td>\n<td class=\"tableblock halign-center valign-top\">\n<p class=\"tableblock\">Decimal<\/p>\n<\/td>\n<td class=\"tableblock halign-left valign-top\">\n<p class=\"tableblock\">Ending balance amount for the period in transaction currency<\/p>\n<\/td>\n<\/tr>\n<tr>\n<td class=\"tableblock halign-center valign-top\">\n<p class=\"tableblock\">15<\/p>\n<\/td>\n<td class=\"tableblock halign-center valign-top\"><\/td>\n<td class=\"tableblock halign-left valign-top\">\n<p class=\"tableblock\">Business_Segment_X<\/p>\n<\/td>\n<td class=\"tableblock halign-center valign-top\">\n<p class=\"tableblock\">String<\/p>\n<\/td>\n<td class=\"tableblock halign-left valign-top\">\n<p class=\"tableblock\">A reserved field for business segments\/structures<\/p>\n<\/td>\n<\/tr>\n<\/tbody>\n<\/table>\n<div class=\"paragraph data-line-259\">\n<p>The query to retrieve <code>Beginning_Functional_Amount<\/code> and <code>Ending_Functional_Amount<\/code> with <code>Functional_Currency_Code<\/code> for specified <code>GL_Account_Number<\/code>, <code>Fiscal_Year<\/code>, and <code>Accounting_Period<\/code> would be:<\/p>\n<\/div>\n<div class=\"listingblock data-line-261\">\n<div class=\"content\">\n<pre class=\"highlight\"><code class=\"language-SQL\" data-lang=\"SQL\">SELECT Beginning_Functional_Amount, Ending_Functional_Amount, Functional_Currency_Code\r\nFROM Balance\r\nWHERE GL_Account_Number = :GL_Account_Number AND\r\n      Fiscal_Year = :Fiscal_Year AND\r\n      Accounting_Period = :Accounting_Period;<\/code><\/pre>\n<\/div>\n<\/div>\n<div class=\"paragraph data-line-269\">\n<p>This design ensures that the functional currency code, which is critical to understand the context of the balance amounts, is associated directly with the balance records. It retains the simplicity of having a single unique identifier for each record, making it easier to work with in queries and ORM frameworks.<\/p>\n<\/div>\n<\/div>\n<\/div>\n<div class=\"sect1 data-line-271\">\n<h2 id=\"_4_currency_codes\">4. 4 currency codes<\/h2>\n<div class=\"sectionbody\">\n<div class=\"paragraph data-line-273\">\n<div class=\"title\">User<\/div>\n<p>What if we need balance amount in local currency or reporting currency or transaction currency?<br \/>\nAlso descrive what kinds of problem was solved with your design.<\/p>\n<\/div>\n<div class=\"paragraph data-line-277\">\n<div class=\"title\">ChatGPT<\/div>\n<p>You&#8217;re right, if you need the balance amounts in different currencies, you will also need to store the currency codes for these respective amounts. So, let&#8217;s add these currency codes to the <code>Balance<\/code> table:<\/p>\n<\/div>\n<div class=\"paragraph data-line-279\">\n<p>1 <code>Balance<\/code> table:<\/p>\n<\/div>\n<table class=\"tableblock frame-all grid-all stretch data-line-282\">\n<colgroup>\n<col style=\"width: 6.6666%;\">\n<col style=\"width: 6.6666%;\">\n<col style=\"width: 26.6666%;\">\n<col style=\"width: 6.6666%;\">\n<col style=\"width: 53.3336%;\">\n<\/colgroup>\n<tbody>\n<tr>\n<td class=\"tableblock halign-center valign-top\">\n<p class=\"tableblock\">No.<\/p>\n<\/td>\n<td class=\"tableblock halign-center valign-top\">\n<p class=\"tableblock\">key<\/p>\n<\/td>\n<td class=\"tableblock halign-left valign-top\">\n<p class=\"tableblock\">Name<\/p>\n<\/td>\n<td class=\"tableblock halign-center valign-top\">\n<p class=\"tableblock\">Datatype<\/p>\n<\/td>\n<td class=\"tableblock halign-left valign-top\">\n<p class=\"tableblock\">Description<\/p>\n<\/td>\n<\/tr>\n<tr>\n<td class=\"tableblock halign-center valign-top\">\n<p class=\"tableblock\">1<\/p>\n<\/td>\n<td class=\"tableblock halign-center valign-top\">\n<p class=\"tableblock\">PK<\/p>\n<\/td>\n<td class=\"tableblock halign-left valign-top\">\n<p class=\"tableblock\">Balance_ID<\/p>\n<\/td>\n<td class=\"tableblock halign-center valign-top\">\n<p class=\"tableblock\">String<\/p>\n<\/td>\n<td class=\"tableblock halign-left valign-top\">\n<p class=\"tableblock\">Unique balance record ID<\/p>\n<\/td>\n<\/tr>\n<tr>\n<td class=\"tableblock halign-center valign-top\">\n<p class=\"tableblock\">2<\/p>\n<\/td>\n<td class=\"tableblock halign-center valign-top\"><\/td>\n<td class=\"tableblock halign-left valign-top\">\n<p class=\"tableblock\">GL_Account_Number<\/p>\n<\/td>\n<td class=\"tableblock halign-center valign-top\">\n<p class=\"tableblock\">String<\/p>\n<\/td>\n<td class=\"tableblock halign-left valign-top\">\n<p class=\"tableblock\">The GL account number<\/p>\n<\/td>\n<\/tr>\n<tr>\n<td class=\"tableblock halign-center valign-top\">\n<p class=\"tableblock\">3<\/p>\n<\/td>\n<td class=\"tableblock halign-center valign-top\"><\/td>\n<td class=\"tableblock halign-left valign-top\">\n<p class=\"tableblock\">Fiscal_Year<\/p>\n<\/td>\n<td class=\"tableblock halign-center valign-top\">\n<p class=\"tableblock\">String<\/p>\n<\/td>\n<td class=\"tableblock halign-left valign-top\">\n<p class=\"tableblock\">Fiscal year in which the Balance_As_Of_Date occurs<\/p>\n<\/td>\n<\/tr>\n<tr>\n<td class=\"tableblock halign-center valign-top\">\n<p class=\"tableblock\">4<\/p>\n<\/td>\n<td class=\"tableblock halign-center valign-top\"><\/td>\n<td class=\"tableblock halign-left valign-top\">\n<p class=\"tableblock\">Accounting_Period<\/p>\n<\/td>\n<td class=\"tableblock halign-center valign-top\">\n<p class=\"tableblock\">String<\/p>\n<\/td>\n<td class=\"tableblock halign-left valign-top\">\n<p class=\"tableblock\">Accounting period in which the Balance_As_Of_Date occurs<\/p>\n<\/td>\n<\/tr>\n<tr>\n<td class=\"tableblock halign-center valign-top\">\n<p class=\"tableblock\">5<\/p>\n<\/td>\n<td class=\"tableblock halign-center valign-top\"><\/td>\n<td class=\"tableblock halign-left valign-top\">\n<p class=\"tableblock\">Balance_As_Of_Date<\/p>\n<\/td>\n<td class=\"tableblock halign-center valign-top\">\n<p class=\"tableblock\">Date<\/p>\n<\/td>\n<td class=\"tableblock halign-left valign-top\">\n<p class=\"tableblock\">The date through which the provided balance reflects account activity<\/p>\n<\/td>\n<\/tr>\n<tr>\n<td class=\"tableblock halign-center valign-top\">\n<p class=\"tableblock\">6<\/p>\n<\/td>\n<td class=\"tableblock halign-center valign-top\"><\/td>\n<td class=\"tableblock halign-left valign-top\">\n<p class=\"tableblock\">Functional_Currency_Code<\/p>\n<\/td>\n<td class=\"tableblock halign-center valign-top\">\n<p class=\"tableblock\">String<\/p>\n<\/td>\n<td class=\"tableblock halign-left valign-top\">\n<p class=\"tableblock\">The code of functional currency (ISO 4217)<\/p>\n<\/td>\n<\/tr>\n<tr>\n<td class=\"tableblock halign-center valign-top\">\n<p class=\"tableblock\">7<\/p>\n<\/td>\n<td class=\"tableblock halign-center valign-top\"><\/td>\n<td class=\"tableblock halign-left valign-top\">\n<p class=\"tableblock\">Beginning_Functional_Amount<\/p>\n<\/td>\n<td class=\"tableblock halign-center valign-top\">\n<p class=\"tableblock\">Decimal<\/p>\n<\/td>\n<td class=\"tableblock halign-left valign-top\">\n<p class=\"tableblock\">Beginning balance amount of the period in functional currency<\/p>\n<\/td>\n<\/tr>\n<tr>\n<td class=\"tableblock halign-center valign-top\">\n<p class=\"tableblock\">8<\/p>\n<\/td>\n<td class=\"tableblock halign-center valign-top\"><\/td>\n<td class=\"tableblock halign-left valign-top\">\n<p class=\"tableblock\">Reporting_Currency_Code<\/p>\n<\/td>\n<td class=\"tableblock halign-center valign-top\">\n<p class=\"tableblock\">String<\/p>\n<\/td>\n<td class=\"tableblock halign-left valign-top\">\n<p class=\"tableblock\">The code of reporting currency (ISO 4217)<\/p>\n<\/td>\n<\/tr>\n<tr>\n<td class=\"tableblock halign-center valign-top\">\n<p class=\"tableblock\">9<\/p>\n<\/td>\n<td class=\"tableblock halign-center valign-top\"><\/td>\n<td class=\"tableblock halign-left valign-top\">\n<p class=\"tableblock\">Beginning_Reporting_Amount<\/p>\n<\/td>\n<td class=\"tableblock halign-center valign-top\">\n<p class=\"tableblock\">Decimal<\/p>\n<\/td>\n<td class=\"tableblock halign-left valign-top\">\n<p class=\"tableblock\">Beginning balance amount of the period in reporting currency<\/p>\n<\/td>\n<\/tr>\n<tr>\n<td class=\"tableblock halign-center valign-top\">\n<p class=\"tableblock\">10<\/p>\n<\/td>\n<td class=\"tableblock halign-center valign-top\"><\/td>\n<td class=\"tableblock halign-left valign-top\">\n<p class=\"tableblock\">Local_Currency_Code<\/p>\n<\/td>\n<td class=\"tableblock halign-center valign-top\">\n<p class=\"tableblock\">String<\/p>\n<\/td>\n<td class=\"tableblock halign-left valign-top\">\n<p class=\"tableblock\">The code of local currency (ISO 4217)<\/p>\n<\/td>\n<\/tr>\n<tr>\n<td class=\"tableblock halign-center valign-top\">\n<p class=\"tableblock\">11<\/p>\n<\/td>\n<td class=\"tableblock halign-center valign-top\"><\/td>\n<td class=\"tableblock halign-left valign-top\">\n<p class=\"tableblock\">Beginning_Local_Amount<\/p>\n<\/td>\n<td class=\"tableblock halign-center valign-top\">\n<p class=\"tableblock\">Decimal<\/p>\n<\/td>\n<td class=\"tableblock halign-left valign-top\">\n<p class=\"tableblock\">Beginning balance amount of the period in local currency<\/p>\n<\/td>\n<\/tr>\n<tr>\n<td class=\"tableblock halign-center valign-top\">\n<p class=\"tableblock\">12<\/p>\n<\/td>\n<td class=\"tableblock halign-center valign-top\"><\/td>\n<td class=\"tableblock halign-left valign-top\">\n<p class=\"tableblock\">Transaction_Currency_Code<\/p>\n<\/td>\n<td class=\"tableblock halign-center valign-top\">\n<p class=\"tableblock\">String<\/p>\n<\/td>\n<td class=\"tableblock halign-left valign-top\">\n<p class=\"tableblock\">The code of transaction currency (ISO 4217)<\/p>\n<\/td>\n<\/tr>\n<tr>\n<td class=\"tableblock halign-center valign-top\">\n<p class=\"tableblock\">13<\/p>\n<\/td>\n<td class=\"tableblock halign-center valign-top\"><\/td>\n<td class=\"tableblock halign-left valign-top\">\n<p class=\"tableblock\">Beginning_Transaction_Amount<\/p>\n<\/td>\n<td class=\"tableblock halign-center valign-top\">\n<p class=\"tableblock\">Decimal<\/p>\n<\/td>\n<td class=\"tableblock halign-left valign-top\">\n<p class=\"tableblock\">Beginning balance amount of the period in transaction currency<\/p>\n<\/td>\n<\/tr>\n<tr>\n<td class=\"tableblock halign-center valign-top\">\n<p class=\"tableblock\">14<\/p>\n<\/td>\n<td class=\"tableblock halign-center valign-top\"><\/td>\n<td class=\"tableblock halign-left valign-top\">\n<p class=\"tableblock\">Ending_Functional_Amount<\/p>\n<\/td>\n<td class=\"tableblock halign-center valign-top\">\n<p class=\"tableblock\">Decimal<\/p>\n<\/td>\n<td class=\"tableblock halign-left valign-top\">\n<p class=\"tableblock\">Ending balance amount for the period in functional currency<\/p>\n<\/td>\n<\/tr>\n<tr>\n<td class=\"tableblock halign-center valign-top\">\n<p class=\"tableblock\">15<\/p>\n<\/td>\n<td class=\"tableblock halign-center valign-top\"><\/td>\n<td class=\"tableblock halign-left valign-top\">\n<p class=\"tableblock\">Ending_Reporting_Amount<\/p>\n<\/td>\n<td class=\"tableblock halign-center valign-top\">\n<p class=\"tableblock\">Decimal<\/p>\n<\/td>\n<td class=\"tableblock halign-left valign-top\">\n<p class=\"tableblock\">Ending balance amount for the period in reporting currency<\/p>\n<\/td>\n<\/tr>\n<tr>\n<td class=\"tableblock halign-center valign-top\">\n<p class=\"tableblock\">16<\/p>\n<\/td>\n<td class=\"tableblock halign-center valign-top\"><\/td>\n<td class=\"tableblock halign-left valign-top\">\n<p class=\"tableblock\">Ending_Local_Amount<\/p>\n<\/td>\n<td class=\"tableblock halign-center valign-top\">\n<p class=\"tableblock\">Decimal<\/p>\n<\/td>\n<td class=\"tableblock halign-left valign-top\">\n<p class=\"tableblock\">Ending balance amount for the period in local currency<\/p>\n<\/td>\n<\/tr>\n<tr>\n<td class=\"tableblock halign-center valign-top\">\n<p class=\"tableblock\">17<\/p>\n<\/td>\n<td class=\"tableblock halign-center valign-top\"><\/td>\n<td class=\"tableblock halign-left valign-top\">\n<p class=\"tableblock\">Ending_Transaction_Amount<\/p>\n<\/td>\n<td class=\"tableblock halign-center valign-top\">\n<p class=\"tableblock\">Decimal<\/p>\n<\/td>\n<td class=\"tableblock halign-left valign-top\">\n<p class=\"tableblock\">Ending balance amount for the period in transaction currency<\/p>\n<\/td>\n<\/tr>\n<tr>\n<td class=\"tableblock halign-center valign-top\">\n<p class=\"tableblock\">18<\/p>\n<\/td>\n<td class=\"tableblock halign-center valign-top\"><\/td>\n<td class=\"tableblock halign-left valign-top\">\n<p class=\"tableblock\">Business_Segment_X<\/p>\n<\/td>\n<td class=\"tableblock halign-center valign-top\">\n<p class=\"tableblock\">String<\/p>\n<\/td>\n<td class=\"tableblock halign-left valign-top\">\n<p class=\"tableblock\">A reserved field for business segments\/structures<\/p>\n<\/td>\n<\/tr>\n<\/tbody>\n<\/table>\n<div class=\"paragraph data-line-304\">\n<p>In this revised model, if you want the balance amounts in any specific currency, you can directly select them from the table. For example, to get the <code>Beginning_Local_Amount<\/code> and <code>Ending_Local_Amount<\/code> with <code>Local_Currency_Code<\/code> for a specified <code>GL_Account_Number<\/code>, <code>Fiscal_Year<\/code>, and <code>Accounting_Period<\/code>, the SQL query would look like this:<\/p>\n<\/div>\n<div class=\"listingblock data-line-306\">\n<div class=\"content\">\n<pre class=\"highlight\"><code class=\"language-SQL\" data-lang=\"SQL\">SELECT Beginning_Local_Amount, Ending_Local_Amount, Local_Currency_Code\r\nFROM Balance\r\nWHERE GL_Account_Number = :GL_Account_Number AND\r\n      Fiscal_Year = :Fiscal_Year AND\r\n      Accounting_Period = :Accounting_Period;<\/code><\/pre>\n<\/div>\n<\/div>\n<div class=\"paragraph data-line-314\">\n<p>Problems solved with this design:<\/p>\n<\/div>\n<div class=\"paragraph data-line-316\">\n<p>1 <strong>Simplicity<\/strong>: Each balance record is clearly associated with its related data including various currencies and amounts, making it easier to understand and manage. It retains a single unique identifier for each record (the <code>Balance_ID<\/code>), streamlining database interactions.<\/p>\n<\/div>\n<div class=\"paragraph data-line-318\">\n<p>2 <strong>Consistency<\/strong>: By including each currency code in the balance record, we maintain consistency and clarity of the data. Each balance amount is directly associated with its corresponding currency code.<\/p>\n<\/div>\n<div class=\"paragraph data-line-320\">\n<p>3 <strong>Reduced Complexity in Queries<\/strong>: The new table structure reduces the complexity of SQL queries required to retrieve data. All related data can be accessed in a single query without the need for joins.<\/p>\n<\/div>\n<div class=\"paragraph data-line-322\">\n<p>4 <strong>ORM Framework Compatibility<\/strong>: With a clear primary key (<code>Balance_ID<\/code>), this table design is more suitable for Object-Relational Mapping (ORM) frameworks, which can streamline application development.<\/p>\n<\/div>\n<div class=\"paragraph data-line-324\">\n<p>5 <strong>Flexibility<\/strong>: This design allows for easier addition or removal of different currency types or other fields, thanks to its straightforward structure.<\/p>\n<\/div>\n<\/div>\n<\/div>\n<div class=\"sect1 data-line-326\">\n<h2 id=\"_no_235_were_not_improved_with_your_solution\">5. No.2,3,5 were not improved with your solution<\/h2>\n<div class=\"sectionbody\">\n<div class=\"paragraph data-line-328\">\n<div class=\"title\">User<\/div>\n<p>The difference is only adding Balance_ID. No.2,3,5 has not improved with your solution.<\/p>\n<\/div>\n<div class=\"paragraph data-line-331\">\n<div class=\"title\">ChatGPT<\/div>\n<p>You&#8217;re correct, I appreciate your patience and feedback. The introduction of <code>Balance_ID<\/code> simplifies the primary key, but there is room for improvement in managing the various currencies and reducing query complexity.<\/p>\n<\/div>\n<div class=\"paragraph data-line-333\">\n<p>To address your concerns, let&#8217;s consider the normalization rules and create separate tables to hold different types of balances in different currencies. This separation should make it easier to manage the various balances and retrieve specific data.<\/p>\n<\/div>\n<div class=\"paragraph data-line-335\">\n<p>1 <code>Account<\/code> table:<\/p>\n<\/div>\n<table class=\"tableblock frame-all grid-all stretch data-line-338\">\n<colgroup>\n<col style=\"width: 6.6666%;\">\n<col style=\"width: 6.6666%;\">\n<col style=\"width: 26.6666%;\">\n<col style=\"width: 6.6666%;\">\n<col style=\"width: 53.3336%;\">\n<\/colgroup>\n<tbody>\n<tr>\n<td class=\"tableblock halign-center valign-top\">\n<p class=\"tableblock\">No.<\/p>\n<\/td>\n<td class=\"tableblock halign-center valign-top\">\n<p class=\"tableblock\">key<\/p>\n<\/td>\n<td class=\"tableblock halign-left valign-top\">\n<p class=\"tableblock\">Name<\/p>\n<\/td>\n<td class=\"tableblock halign-center valign-top\">\n<p class=\"tableblock\">Datatype<\/p>\n<\/td>\n<td class=\"tableblock halign-left valign-top\">\n<p class=\"tableblock\">Description<\/p>\n<\/td>\n<\/tr>\n<tr>\n<td class=\"tableblock halign-center valign-top\">\n<p class=\"tableblock\">1<\/p>\n<\/td>\n<td class=\"tableblock halign-center valign-top\">\n<p class=\"tableblock\">PK<\/p>\n<\/td>\n<td class=\"tableblock halign-left valign-top\">\n<p class=\"tableblock\">Account_ID<\/p>\n<\/td>\n<td class=\"tableblock halign-center valign-top\">\n<p class=\"tableblock\">String<\/p>\n<\/td>\n<td class=\"tableblock halign-left valign-top\">\n<p class=\"tableblock\">Unique account record ID<\/p>\n<\/td>\n<\/tr>\n<tr>\n<td class=\"tableblock halign-center valign-top\">\n<p class=\"tableblock\">2<\/p>\n<\/td>\n<td class=\"tableblock halign-center valign-top\"><\/td>\n<td class=\"tableblock halign-left valign-top\">\n<p class=\"tableblock\">GL_Account_Number<\/p>\n<\/td>\n<td class=\"tableblock halign-center valign-top\">\n<p class=\"tableblock\">String<\/p>\n<\/td>\n<td class=\"tableblock halign-left valign-top\">\n<p class=\"tableblock\">The GL account number<\/p>\n<\/td>\n<\/tr>\n<tr>\n<td class=\"tableblock halign-center valign-top\">\n<p class=\"tableblock\">3<\/p>\n<\/td>\n<td class=\"tableblock halign-center valign-top\"><\/td>\n<td class=\"tableblock halign-left valign-top\">\n<p class=\"tableblock\">Business_Segment_X<\/p>\n<\/td>\n<td class=\"tableblock halign-center valign-top\">\n<p class=\"tableblock\">String<\/p>\n<\/td>\n<td class=\"tableblock halign-left valign-top\">\n<p class=\"tableblock\">A reserved field for business segments\/structures<\/p>\n<\/td>\n<\/tr>\n<\/tbody>\n<\/table>\n<div class=\"paragraph data-line-345\">\n<p>2 <code>Balance<\/code> table:<\/p>\n<\/div>\n<table class=\"tableblock frame-all grid-all stretch data-line-348\">\n<colgroup>\n<col style=\"width: 6.6666%;\">\n<col style=\"width: 6.6666%;\">\n<col style=\"width: 26.6666%;\">\n<col style=\"width: 6.6666%;\">\n<col style=\"width: 53.3336%;\">\n<\/colgroup>\n<tbody>\n<tr>\n<td class=\"tableblock halign-center valign-top\">\n<p class=\"tableblock\">No.<\/p>\n<\/td>\n<td class=\"tableblock halign-center valign-top\">\n<p class=\"tableblock\">key<\/p>\n<\/td>\n<td class=\"tableblock halign-left valign-top\">\n<p class=\"tableblock\">Name<\/p>\n<\/td>\n<td class=\"tableblock halign-center valign-top\">\n<p class=\"tableblock\">Datatype<\/p>\n<\/td>\n<td class=\"tableblock halign-left valign-top\">\n<p class=\"tableblock\">Description<\/p>\n<\/td>\n<\/tr>\n<tr>\n<td class=\"tableblock halign-center valign-top\">\n<p class=\"tableblock\">1<\/p>\n<\/td>\n<td class=\"tableblock halign-center valign-top\">\n<p class=\"tableblock\">PK<\/p>\n<\/td>\n<td class=\"tableblock halign-left valign-top\">\n<p class=\"tableblock\">Balance_ID<\/p>\n<\/td>\n<td class=\"tableblock halign-center valign-top\">\n<p class=\"tableblock\">String<\/p>\n<\/td>\n<td class=\"tableblock halign-left valign-top\">\n<p class=\"tableblock\">Unique balance record ID<\/p>\n<\/td>\n<\/tr>\n<tr>\n<td class=\"tableblock halign-center valign-top\">\n<p class=\"tableblock\">2<\/p>\n<\/td>\n<td class=\"tableblock halign-center valign-top\">\n<p class=\"tableblock\">REF<\/p>\n<\/td>\n<td class=\"tableblock halign-left valign-top\">\n<p class=\"tableblock\">Account_ID<\/p>\n<\/td>\n<td class=\"tableblock halign-center valign-top\">\n<p class=\"tableblock\">String<\/p>\n<\/td>\n<td class=\"tableblock halign-left valign-top\">\n<p class=\"tableblock\">Foreign key to Account table<\/p>\n<\/td>\n<\/tr>\n<tr>\n<td class=\"tableblock halign-center valign-top\">\n<p class=\"tableblock\">3<\/p>\n<\/td>\n<td class=\"tableblock halign-center valign-top\"><\/td>\n<td class=\"tableblock halign-left valign-top\">\n<p class=\"tableblock\">Fiscal_Year<\/p>\n<\/td>\n<td class=\"tableblock halign-center valign-top\">\n<p class=\"tableblock\">String<\/p>\n<\/td>\n<td class=\"tableblock halign-left valign-top\">\n<p class=\"tableblock\">Fiscal year in which the Balance_As_Of_Date occurs<\/p>\n<\/td>\n<\/tr>\n<tr>\n<td class=\"tableblock halign-center valign-top\">\n<p class=\"tableblock\">4<\/p>\n<\/td>\n<td class=\"tableblock halign-center valign-top\"><\/td>\n<td class=\"tableblock halign-left valign-top\">\n<p class=\"tableblock\">Accounting_Period<\/p>\n<\/td>\n<td class=\"tableblock halign-center valign-top\">\n<p class=\"tableblock\">String<\/p>\n<\/td>\n<td class=\"tableblock halign-left valign-top\">\n<p class=\"tableblock\">Accounting period in which the Balance_As_Of_Date occurs<\/p>\n<\/td>\n<\/tr>\n<tr>\n<td class=\"tableblock halign-center valign-top\">\n<p class=\"tableblock\">5<\/p>\n<\/td>\n<td class=\"tableblock halign-center valign-top\"><\/td>\n<td class=\"tableblock halign-left valign-top\">\n<p class=\"tableblock\">Balance_As_Of_Date<\/p>\n<\/td>\n<td class=\"tableblock halign-center valign-top\">\n<p class=\"tableblock\">Date<\/p>\n<\/td>\n<td class=\"tableblock halign-left valign-top\">\n<p class=\"tableblock\">The date through which the provided balance reflects account activity<\/p>\n<\/td>\n<\/tr>\n<\/tbody>\n<\/table>\n<div class=\"paragraph data-line-357\">\n<p>3 <code>Currency_Balance<\/code> table:<\/p>\n<\/div>\n<table class=\"tableblock frame-all grid-all stretch data-line-360\">\n<colgroup>\n<col style=\"width: 6.6666%;\">\n<col style=\"width: 6.6666%;\">\n<col style=\"width: 26.6666%;\">\n<col style=\"width: 6.6666%;\">\n<col style=\"width: 53.3336%;\">\n<\/colgroup>\n<tbody>\n<tr>\n<td class=\"tableblock halign-center valign-top\">\n<p class=\"tableblock\">No.<\/p>\n<\/td>\n<td class=\"tableblock halign-center valign-top\">\n<p class=\"tableblock\">key<\/p>\n<\/td>\n<td class=\"tableblock halign-left valign-top\">\n<p class=\"tableblock\">Name<\/p>\n<\/td>\n<td class=\"tableblock halign-center valign-top\">\n<p class=\"tableblock\">Datatype<\/p>\n<\/td>\n<td class=\"tableblock halign-left valign-top\">\n<p class=\"tableblock\">Description<\/p>\n<\/td>\n<\/tr>\n<tr>\n<td class=\"tableblock halign-center valign-top\">\n<p class=\"tableblock\">1<\/p>\n<\/td>\n<td class=\"tableblock halign-center valign-top\">\n<p class=\"tableblock\">PK<\/p>\n<\/td>\n<td class=\"tableblock halign-left valign-top\">\n<p class=\"tableblock\">Currency_Balance_ID<\/p>\n<\/td>\n<td class=\"tableblock halign-center valign-top\">\n<p class=\"tableblock\">String<\/p>\n<\/td>\n<td class=\"tableblock halign-left valign-top\">\n<p class=\"tableblock\">Unique currency balance record ID<\/p>\n<\/td>\n<\/tr>\n<tr>\n<td class=\"tableblock halign-center valign-top\">\n<p class=\"tableblock\">2<\/p>\n<\/td>\n<td class=\"tableblock halign-center valign-top\">\n<p class=\"tableblock\">REF<\/p>\n<\/td>\n<td class=\"tableblock halign-left valign-top\">\n<p class=\"tableblock\">Balance_ID<\/p>\n<\/td>\n<td class=\"tableblock halign-center valign-top\">\n<p class=\"tableblock\">String<\/p>\n<\/td>\n<td class=\"tableblock halign-left valign-top\">\n<p class=\"tableblock\">Foreign key to Balance table<\/p>\n<\/td>\n<\/tr>\n<tr>\n<td class=\"tableblock halign-center valign-top\">\n<p class=\"tableblock\">3<\/p>\n<\/td>\n<td class=\"tableblock halign-center valign-top\"><\/td>\n<td class=\"tableblock halign-left valign-top\">\n<p class=\"tableblock\">Currency_Code<\/p>\n<\/td>\n<td class=\"tableblock halign-center valign-top\">\n<p class=\"tableblock\">String<\/p>\n<\/td>\n<td class=\"tableblock halign-left valign-top\">\n<p class=\"tableblock\">Currency of balance amount (ISO 4217)<\/p>\n<\/td>\n<\/tr>\n<tr>\n<td class=\"tableblock halign-center valign-top\">\n<p class=\"tableblock\">4<\/p>\n<\/td>\n<td class=\"tableblock halign-center valign-top\"><\/td>\n<td class=\"tableblock halign-left valign-top\">\n<p class=\"tableblock\">Beginning_Amount<\/p>\n<\/td>\n<td class=\"tableblock halign-center valign-top\">\n<p class=\"tableblock\">Decimal<\/p>\n<\/td>\n<td class=\"tableblock halign-left valign-top\">\n<p class=\"tableblock\">Beginning balance amount for the period in given currency<\/p>\n<\/td>\n<\/tr>\n<tr>\n<td class=\"tableblock halign-center valign-top\">\n<p class=\"tableblock\">5<\/p>\n<\/td>\n<td class=\"tableblock halign-center valign-top\"><\/td>\n<td class=\"tableblock halign-left valign-top\">\n<p class=\"tableblock\">Ending_Amount<\/p>\n<\/td>\n<td class=\"tableblock halign-center valign-top\">\n<p class=\"tableblock\">Decimal<\/p>\n<\/td>\n<td class=\"tableblock halign-left valign-top\">\n<p class=\"tableblock\">Ending balance amount for the period in given currency<\/p>\n<\/td>\n<\/tr>\n<\/tbody>\n<\/table>\n<div class=\"paragraph data-line-369\">\n<p>In this design, to retrieve <code>Beginning_Amount<\/code> and <code>Ending_Amount<\/code> in functional currency for a specified <code>GL_Account_Number<\/code>, <code>Fiscal_Year<\/code>, and <code>Accounting_Period<\/code>, you would need to join the <code>Account<\/code>, <code>Balance<\/code>, and <code>Currency_Balance<\/code> tables:<\/p>\n<\/div>\n<div class=\"listingblock data-line-371\">\n<div class=\"content\">\n<pre class=\"highlight\"><code class=\"language-SQL\" data-lang=\"SQL\">SELECT cb.Beginning_Amount, cb.Ending_Amount, cb.Currency_Code\r\nFROM Account a\r\nJOIN Balance b ON a.Account_ID = b.Account_ID\r\nJOIN Currency_Balance cb ON b.Balance_ID = cb.Balance_ID\r\nWHERE a.GL_Account_Number = :GL_Account_Number AND\r\n      b.Fiscal_Year = :Fiscal_Year AND\r\n      b.Accounting_Period = :Accounting_Period AND\r\n      cb.Currency_Code = :Functional_Currency_Code;<\/code><\/pre>\n<\/div>\n<\/div>\n<div class=\"paragraph data-line-382\">\n<p><strong>This design addresses the concerns as follows<\/strong>:<\/p>\n<\/div>\n<div class=\"paragraph data-line-384\">\n<p>1 <strong>Flexibility<\/strong>: By breaking down the balance details into multiple tables, the data becomes more manageable and scalable. If you want to add another type of balance or another currency, you can do so without altering existing tables.<\/p>\n<\/div>\n<div class=\"paragraph data-line-386\">\n<p>2 <strong>Normalization<\/strong>: The design adheres more closely to the principles of database normalization. Each piece of information is stored in its most logical place.<\/p>\n<\/div>\n<div class=\"paragraph data-line-388\">\n<p>3 <strong>Reduced Redundancy<\/strong>: There is less redundancy in the database as each piece of information is stored once.<\/p>\n<\/div>\n<div class=\"paragraph data-line-390\">\n<p>4 <strong>Ease of querying<\/strong>: While the SQL queries become slightly more complex due to the need to join tables, the structure of the data makes the intention of the queries clearer.<\/p>\n<\/div>\n<\/div>\n<\/div>\n<\/div>\n<div id=\"footer\">\n<div id=\"footer-text\">\nLast updated 2023-06-19 18:04:53 +0900\n<\/div>\n<\/div>\n","protected":false},"excerpt":{"rendered":"<p>Views: 24 Achieving Data Consistency and Clarity in the Table Structure Table of Contents 1. Refine Trial Bala [&hellip;]<\/p>\n","protected":false},"author":2,"featured_media":10106,"comment_status":"open","ping_status":"closed","sticky":false,"template":"","format":"standard","meta":[],"categories":[71,69,30],"tags":[],"_links":{"self":[{"href":"https:\/\/www.sambuichi.jp\/index.php?rest_route=\/wp\/v2\/posts\/10105"}],"collection":[{"href":"https:\/\/www.sambuichi.jp\/index.php?rest_route=\/wp\/v2\/posts"}],"about":[{"href":"https:\/\/www.sambuichi.jp\/index.php?rest_route=\/wp\/v2\/types\/post"}],"author":[{"embeddable":true,"href":"https:\/\/www.sambuichi.jp\/index.php?rest_route=\/wp\/v2\/users\/2"}],"replies":[{"embeddable":true,"href":"https:\/\/www.sambuichi.jp\/index.php?rest_route=%2Fwp%2Fv2%2Fcomments&post=10105"}],"version-history":[{"count":2,"href":"https:\/\/www.sambuichi.jp\/index.php?rest_route=\/wp\/v2\/posts\/10105\/revisions"}],"predecessor-version":[{"id":10110,"href":"https:\/\/www.sambuichi.jp\/index.php?rest_route=\/wp\/v2\/posts\/10105\/revisions\/10110"}],"wp:featuredmedia":[{"embeddable":true,"href":"https:\/\/www.sambuichi.jp\/index.php?rest_route=\/wp\/v2\/media\/10106"}],"wp:attachment":[{"href":"https:\/\/www.sambuichi.jp\/index.php?rest_route=%2Fwp%2Fv2%2Fmedia&parent=10105"}],"wp:term":[{"taxonomy":"category","embeddable":true,"href":"https:\/\/www.sambuichi.jp\/index.php?rest_route=%2Fwp%2Fv2%2Fcategories&post=10105"},{"taxonomy":"post_tag","embeddable":true,"href":"https:\/\/www.sambuichi.jp\/index.php?rest_route=%2Fwp%2Fv2%2Ftags&post=10105"}],"curies":[{"name":"wp","href":"https:\/\/api.w.org\/{rel}","templated":true}]}}