diff options
| author | einhverfr <einhverfr@4979c152-3d1c-0410-bac9-87ea11338e46> | 2007-06-13 18:30:01 +0000 |
|---|---|---|
| committer | einhverfr <einhverfr@4979c152-3d1c-0410-bac9-87ea11338e46> | 2007-06-13 18:30:01 +0000 |
| commit | 18bc4f0a2563a98273dae60b50235346816ca2cf (patch) | |
| tree | 4c3b8177f437f790478baeb8ad5ef40fa3282698 /doc/database/ledgersmb.html | |
| parent | 8f8e696c078e655e4116d92816036b5ea039a65a (diff) | |
Updating database schema diagrams and documentation
git-svn-id: https://ledger-smb.svn.sourceforge.net/svnroot/ledger-smb/trunk@1274 4979c152-3d1c-0410-bac9-87ea11338e46
Diffstat (limited to 'doc/database/ledgersmb.html')
| -rw-r--r-- | doc/database/ledgersmb.html | 2981 |
1 files changed, 2533 insertions, 448 deletions
diff --git a/doc/database/ledgersmb.html b/doc/database/ledgersmb.html index 1011d507..078a1434 100644 --- a/doc/database/ledgersmb.html +++ b/doc/database/ledgersmb.html @@ -4,7 +4,7 @@ <html> <head> - <title>Index for ledgersmb</title> + <title>Index for lsmb13-new</title> <meta http-equiv="Content-Type" content="text/html; charset=UTF-8"> <style type="text/css"> BODY { @@ -112,12 +112,12 @@ <body> <!-- Primary Index --> - <p><br><br>Dumped on 2007-03-22</p> -<h1><a name="index">Index of database - ledgersmb</a></h1> + <p><br><br>Dumped on 2007-06-13</p> +<h1><a name="index">Index of database - lsmb13-new</a></h1> <ul> <li><a name="public.schema">public</a></li><ul> - <li><a href="#public.table.acc-trans">acc_trans</a></li><li><a href="#public.table.ap">ap</a></li><li><a href="#public.table.ar">ar</a></li><li><a href="#public.table.assembly">assembly</a></li><li><a href="#public.table.audittrail">audittrail</a></li><li><a href="#public.table.business">business</a></li><li><a href="#public.table.chart">chart</a></li><li><a href="#public.table.custom-field-catalog">custom_field_catalog</a></li><li><a href="#public.table.custom-table-catalog">custom_table_catalog</a></li><li><a href="#public.table.customer">customer</a></li><li><a href="#public.table.customertax">customertax</a></li><li><a href="#public.table.defaults">defaults</a></li><li><a href="#public.table.department">department</a></li><li><a href="#public.table.dpt-trans">dpt_trans</a></li><li><a href="#public.table.employee">employee</a></li><li><a href="#public.table.exchangerate">exchangerate</a></li><li><a href="#public.table.gifi">gifi</a></li><li><a href="#public.table.gl">gl</a></li><li><a href="#public.table.inventory">inventory</a></li><li><a href="#public.table.invoice">invoice</a></li><li><a href="#public.table.jcitems">jcitems</a></li><li><a href="#public.table.language">language</a></li><li><a href="#public.table.makemodel">makemodel</a></li><li><a href="#public.table.oe">oe</a></li><li><a href="#public.table.orderitems">orderitems</a></li><li><a href="#public.table.parts">parts</a></li><li><a href="#public.table.partscustomer">partscustomer</a></li><li><a href="#public.table.partsgroup">partsgroup</a></li><li><a href="#public.table.partstax">partstax</a></li><li><a href="#public.table.partsvendor">partsvendor</a></li><li><a href="#public.table.pricegroup">pricegroup</a></li><li><a href="#public.table.project">project</a></li><li><a href="#public.table.recurring">recurring</a></li><li><a href="#public.table.recurringemail">recurringemail</a></li><li><a href="#public.table.recurringprint">recurringprint</a></li><li><a href="#public.table.shipto">shipto</a></li><li><a href="#public.table.sic">sic</a></li><li><a href="#public.table.status">status</a></li><li><a href="#public.table.tax">tax</a></li><li><a href="#public.table.taxcategory">taxcategory</a></li><li><a href="#public.table.taxmodule">taxmodule</a></li><li><a href="#public.table.transactions">transactions</a></li><li><a href="#public.table.translation">translation</a></li><li><a href="#public.table.vendor">vendor</a></li><li><a href="#public.table.vendortax">vendortax</a></li><li><a href="#public.table.warehouse">warehouse</a></li><li><a href="#public.table.yearend">yearend</a></li> + <li><a href="#public.table.acc-trans">acc_trans</a></li><li><a href="#public.table.ap">ap</a></li><li><a href="#public.table.ar">ar</a></li><li><a href="#public.table.assembly">assembly</a></li><li><a href="#public.table.audittrail">audittrail</a></li><li><a href="#public.table.business">business</a></li><li><a href="#public.table.chart">chart</a></li><li><a href="#public.table.company">company</a></li><li><a href="#public.table.company-to-contact">company_to_contact</a></li><li><a href="#public.table.company-to-entity">company_to_entity</a></li><li><a href="#public.table.company-to-location">company_to_location</a></li><li><a href="#public.table.contact-class">contact_class</a></li><li><a href="#public.table.country">country</a></li><li><a href="#public.table.custom-field-catalog">custom_field_catalog</a></li><li><a href="#public.table.custom-table-catalog">custom_table_catalog</a></li><li><a href="#public.table.customer">customer</a></li><li><a href="#public.table.customertax">customertax</a></li><li><a href="#public.table.defaults">defaults</a></li><li><a href="#public.table.department">department</a></li><li><a href="#public.table.dpt-trans">dpt_trans</a></li><li><a href="#public.table.employee">employee</a></li><li><a href="#public.table.entity">entity</a></li><li><a href="#public.table.entity-class">entity_class</a></li><li><a href="#public.table.entity-class-to-entity">entity_class_to_entity</a></li><li><a href="#public.table.entity-other-name">entity_other_name</a></li><li><a href="#public.table.exchangerate">exchangerate</a></li><li><a href="#public.table.gifi">gifi</a></li><li><a href="#public.table.gl">gl</a></li><li><a href="#public.table.inventory">inventory</a></li><li><a href="#public.table.invoice">invoice</a></li><li><a href="#public.table.jcitems">jcitems</a></li><li><a href="#public.table.language">language</a></li><li><a href="#public.table.location">location</a></li><li><a href="#public.table.makemodel">makemodel</a></li><li><a href="#public.table.oe">oe</a></li><li><a href="#public.table.oe-class">oe_class</a></li><li><a href="#public.table.orderitems">orderitems</a></li><li><a href="#public.table.parts">parts</a></li><li><a href="#public.table.partscustomer">partscustomer</a></li><li><a href="#public.table.partsgroup">partsgroup</a></li><li><a href="#public.table.partstax">partstax</a></li><li><a href="#public.table.partsvendor">partsvendor</a></li><li><a href="#public.table.person">person</a></li><li><a href="#public.table.person-to-company">person_to_company</a></li><li><a href="#public.table.person-to-contact">person_to_contact</a></li><li><a href="#public.table.person-to-entity">person_to_entity</a></li><li><a href="#public.table.pricegroup">pricegroup</a></li><li><a href="#public.table.project">project</a></li><li><a href="#public.table.recurring">recurring</a></li><li><a href="#public.table.recurringemail">recurringemail</a></li><li><a href="#public.table.recurringprint">recurringprint</a></li><li><a href="#public.table.salutation">salutation</a></li><li><a href="#public.table.shipto">shipto</a></li><li><a href="#public.table.sic">sic</a></li><li><a href="#public.table.status">status</a></li><li><a href="#public.table.tax">tax</a></li><li><a href="#public.table.taxcategory">taxcategory</a></li><li><a href="#public.table.taxmodule">taxmodule</a></li><li><a href="#public.table.transactions">transactions</a></li><li><a href="#public.table.translation">translation</a></li><li><a href="#public.table.vendor">vendor</a></li><li><a href="#public.table.vendortax">vendortax</a></li><li><a href="#public.table.warehouse">warehouse</a></li><li><a href="#public.table.yearend">yearend</a></li> <li><a href="#public.function.add-custom-field-character-varying-character-varying-character-varying">add_custom_field( character varying, character varying, character varying )</a></li><li><a href="#public.function.avgcost-integer">avgcost( integer )</a></li><li><a href="#public.function.check-department">check_department( )</a></li><li><a href="#public.function.check-inventory">check_inventory( )</a></li><li><a href="#public.function.del-customer">del_customer( )</a></li><li><a href="#public.function.del-department">del_department( )</a></li><li><a href="#public.function.del-exchangerate">del_exchangerate( )</a></li><li><a href="#public.function.del-recurring">del_recurring( )</a></li><li><a href="#public.function.del-vendor">del_vendor( )</a></li><li><a href="#public.function.del-yearend">del_yearend( )</a></li><li><a href="#public.function.drop-custom-field-character-varying-character-varying">drop_custom_field( character varying, character varying )</a></li><li><a href="#public.function.lastcost-integer">lastcost( integer )</a></li><li><a href="#public.function.trigger-parts-short">trigger_parts_short( )</a></li><li><a href="#public.function.update-user-integer-text">update_user( integer, text )</a></li> </ul> @@ -395,7 +395,7 @@ </td> <td>id</td> - <td>integer</td> + <td>serial</td> <td><i> PRIMARY KEY @@ -405,7 +405,7 @@ - DEFAULT nextval('id'::regclass) + </i> </td> @@ -446,16 +446,24 @@ <tr class="tr1"> <td> + + <a href="#public.table.entity">entity.id</a> + + </td> - <td>vendor_id</td> + <td>entity_id</td> <td>integer</td> <td><i> + + + + </i> - + <br><br> Used to be customer_id, but customer is now metadata. You need to push to entity </td> </tr> @@ -622,13 +630,21 @@ <tr class="tr0"> <td> + + <a href="#public.table.person">person.id</a> + + </td> - <td>employee_id</td> + <td>person_id</td> <td>integer</td> <td><i> + + + + NOT NULL </i> @@ -796,8 +812,6 @@ <!-- Indexes --> - ap_employee_id_key employee_id - ap_id_key id ap_invnumber_key invnumber @@ -808,8 +822,6 @@ ap_transdate_key transdate - ap_vendor_id_key vendor_id - <!-- View Definition --> @@ -845,7 +857,7 @@ </td> <td>id</td> - <td>integer</td> + <td>serial</td> <td><i> PRIMARY KEY @@ -855,7 +867,7 @@ - DEFAULT nextval('id'::regclass) + </i> </td> @@ -896,16 +908,24 @@ <tr class="tr1"> <td> + + <a href="#public.table.entity">entity.id</a> + + </td> - <td>customer_id</td> + <td>entity_id</td> <td>integer</td> <td><i> + + + + </i> - + <br><br> Used to be customer_id, but customer is now metadata. You need to push to entity </td> </tr> @@ -1104,13 +1124,21 @@ <tr class="tr0"> <td> + + <a href="#public.table.person">person.id</a> + + </td> - <td>employee_id</td> + <td>person_id</td> <td>integer</td> <td><i> + + + + NOT NULL </i> @@ -1246,10 +1274,6 @@ <!-- Indexes --> - ar_customer_id_key customer_id - - ar_employee_id_key employee_id - ar_id_key id ar_invnumber_key invnumber @@ -1528,13 +1552,21 @@ <tr class="tr0"> <td> + + <a href="#public.table.person">person.id</a> + + </td> - <td>employee_id</td> + <td>person_id</td> <td>integer</td> <td><i> + + + + NOT NULL </i> @@ -1618,7 +1650,7 @@ </td> <td>id</td> - <td>integer</td> + <td>serial</td> <td><i> PRIMARY KEY @@ -1628,7 +1660,7 @@ - DEFAULT nextval('id'::regclass) + </i> </td> @@ -1718,7 +1750,7 @@ </td> <td>id</td> - <td>integer</td> + <td>serial</td> <td><i> PRIMARY KEY @@ -1728,7 +1760,7 @@ - DEFAULT nextval('id'::regclass) + </i> </td> @@ -1900,13 +1932,13 @@ <hr> <h2>Table: - <a name="public.table.custom-field-catalog">custom_field_catalog</a> + <a name="public.table.company">company</a> </h2> <table width="100%" cellspacing="0" cellpadding="3"> - <caption>custom_field_catalog Structure</caption> + <caption>company Structure</caption> <tr> <th>F-Key</th> <th>Name</th> @@ -1920,10 +1952,58 @@ </td> - <td>field_id</td> + <td>id</td> <td>serial</td> <td><i> + + + + UNIQUE + + + + NOT NULL + + </i> + + </td> + </tr> + + <tr class="tr1"> + <td> + + + <a href="#public.table.entity">entity.id</a> + + + </td> + <td>entity_id</td> + <td>integer</td> + <td><i> + + + + + + + NOT NULL + + </i> + + </td> + </tr> + + <tr class="tr0"> + <td> + + + + </td> + <td>legal_name</td> + <td>text</td> + <td><i> + PRIMARY KEY @@ -1941,11 +2021,11 @@ <td> - <a href="#public.table.custom-table-catalog">custom_table_catalog.table_id</a> + <a href="#public.table.entity-class">entity_class.id</a> </td> - <td>table_id</td> + <td>entity_class_id</td> <td>integer</td> <td><i> @@ -1954,7 +2034,7 @@ - + NOT NULL </i> @@ -1964,16 +2044,47 @@ <tr class="tr0"> <td> + + + + <a href="#public.table.location">location.id</a> + + </td> - <td>field_name</td> - <td>text</td> + <td>primary_location_id</td> + <td>integer</td> <td><i> + PRIMARY KEY + + + + + + + + </i> + <br><br> This is the location that should show up by default for any forms + </td> + </tr> + + <tr class="tr1"> + <td> + + </td> + <td>tax_id</td> + <td>text</td> + <td><i> + + + + </i> + <br><br> In the US this would be a EIN. </td> </tr> @@ -1988,9 +2099,39 @@ <!-- Constraint List --> + <p> </p> + <table width="100%" cellspacing="0" cellpadding="3"> + <caption>company Constraints</caption> + <tr> + <th>Name</th> + <th>Constraint</th> + </tr> + + <tr class="tr0"> + <td>company_legal_name_check</td> + <td>CHECK ((legal_name ~ '[[:alnum:]_]'::text))</td> + </tr> + + </table> + <!-- Foreign Key Discovery --> + <p>Tables referencing this one via Foreign Key Constraints:</p> + + <ul> + <li><a href="#public.table.company-to-contact">company_to_contact</a></li> + </ul> + + <ul> + <li><a href="#public.table.company-to-entity">company_to_entity</a></li> + </ul> + + <ul> + <li><a href="#public.table.company-to-location">company_to_location</a></li> + </ul> + + <!-- Indexes --> @@ -2008,13 +2149,15 @@ <hr> <h2>Table: - <a name="public.table.custom-table-catalog">custom_table_catalog</a> + <a name="public.table.company-to-contact">company_to_contact</a> </h2> + <p> To keep track of the relationship between multiple contact methods and a single company </p> + <table width="100%" cellspacing="0" cellpadding="3"> - <caption>custom_table_catalog Structure</caption> + <caption>company_to_contact Structure</caption> <tr> <th>F-Key</th> <th>Name</th> @@ -2026,12 +2169,20 @@ <td> + <a href="#public.table.company">company.id</a> + + + </td> - <td>table_id</td> - <td>serial</td> + <td>company_id</td> + <td>integer</td> <td><i> + + + + PRIMARY KEY @@ -2048,11 +2199,26 @@ <tr class="tr1"> <td> + + <a href="#public.table.contact-class">contact_class.id</a> + + + + </td> - <td>extends</td> - <td>text</td> + <td>contact_class_id</td> + <td>integer</td> <td><i> + + + + + PRIMARY KEY + + + + @@ -2064,11 +2230,18 @@ <tr class="tr0"> <td> + + </td> - <td>table_name</td> + <td>contact</td> <td>text</td> <td><i> + PRIMARY KEY + + + + @@ -2088,16 +2261,24 @@ <!-- Constraint List --> + <p> </p> + <table width="100%" cellspacing="0" cellpadding="3"> + <caption>company_to_contact Constraints</caption> + <tr> + <th>Name</th> + <th>Constraint</th> + </tr> + + <tr class="tr0"> + <td>company_to_contact_contact_check</td> + <td>CHECK ((contact ~ '[[:alnum:]_]'::text))</td> + </tr> + + </table> + <!-- Foreign Key Discovery --> - <p>Tables referencing this one via Foreign Key Constraints:</p> - - <ul> - <li><a href="#public.table.custom-field-catalog">custom_field_catalog</a></li> - </ul> - - <!-- Indexes --> @@ -2115,13 +2296,13 @@ <hr> <h2>Table: - <a name="public.table.customer">customer</a> + <a name="public.table.company-to-entity">company_to_entity</a> </h2> <table width="100%" cellspacing="0" cellpadding="3"> - <caption>customer Structure</caption> + <caption>company_to_entity Structure</caption> <tr> <th>F-Key</th> <th>Name</th> @@ -2133,12 +2314,20 @@ <td> + <a href="#public.table.company">company.id</a> + + + </td> - <td>id</td> + <td>company_id</td> <td>integer</td> <td><i> + + + + PRIMARY KEY @@ -2146,7 +2335,7 @@ - DEFAULT nextval('id'::regclass) + </i> </td> @@ -2155,11 +2344,26 @@ <tr class="tr1"> <td> + + <a href="#public.table.entity">entity.id</a> + + + + </td> - <td>name</td> - <td>character varying(64)</td> + <td>entity_id</td> + <td>integer</td> <td><i> + + + + + PRIMARY KEY + + + + @@ -2172,8 +2376,8 @@ <td> </td> - <td>address1</td> - <td>character varying(32)</td> + <td>related_how</td> + <td>text</td> <td><i> @@ -2188,26 +2392,101 @@ <td> </td> - <td>address2</td> - <td>character varying(32)</td> + <td>created</td> + <td>date</td> <td><i> - - + NOT NULL + DEFAULT ('now'::text)::date </i> </td> </tr> + </table> + + <!-- Inherits --> + + + + + + + <!-- Constraint List --> + + <p> </p> + <table width="100%" cellspacing="0" cellpadding="3"> + <caption>company_to_entity Constraints</caption> + <tr> + <th>Name</th> + <th>Constraint</th> + </tr> + + <tr class="tr0"> + <td>company_to_entity_check</td> + <td>CHECK ((entity_id <> company_id))</td> + </tr> + + </table> + + + <!-- Foreign Key Discovery --> + + + <!-- Indexes --> + + + <!-- View Definition --> + + + <!-- List off permissions --> + + + <p> + <a href="#index">Index</a> - + <a href="#public.schema">Schema public</a> + </p> + + <hr> + <h2>Table: + <a name="public.table.company-to-location">company_to_location</a> + </h2> + + + + <table width="100%" cellspacing="0" cellpadding="3"> + <caption>company_to_location Structure</caption> + <tr> + <th>F-Key</th> + <th>Name</th> + <th>Type</th> + <th>Description</th> + </tr> + <tr class="tr0"> <td> + + <a href="#public.table.location">location.id</a> + + + + </td> - <td>city</td> - <td>character varying(32)</td> + <td>location_id</td> + <td>integer</td> <td><i> + + + + + PRIMARY KEY + + + + @@ -2219,11 +2498,26 @@ <tr class="tr1"> <td> + + <a href="#public.table.company">company.id</a> + + + + </td> - <td>state</td> - <td>character varying(32)</td> + <td>company_id</td> + <td>integer</td> <td><i> + + + + + PRIMARY KEY + + + + @@ -2232,16 +2526,69 @@ </td> </tr> + </table> + + <!-- Inherits --> + + + + + + + <!-- Constraint List --> + + + <!-- Foreign Key Discovery --> + + + <!-- Indexes --> + + + <!-- View Definition --> + + + <!-- List off permissions --> + + + <p> + <a href="#index">Index</a> - + <a href="#public.schema">Schema public</a> + </p> + + <hr> + <h2>Table: + <a name="public.table.contact-class">contact_class</a> + </h2> + + + + <table width="100%" cellspacing="0" cellpadding="3"> + <caption>contact_class Structure</caption> + <tr> + <th>F-Key</th> + <th>Name</th> + <th>Type</th> + <th>Description</th> + </tr> + <tr class="tr0"> <td> + + </td> - <td>zipcode</td> - <td>character varying(10)</td> + <td>id</td> + <td>serial</td> <td><i> + + + UNIQUE + + + NOT NULL </i> @@ -2251,11 +2598,18 @@ <tr class="tr1"> <td> + + </td> - <td>country</td> - <td>character varying(32)</td> + <td>class</td> + <td>text</td> <td><i> + PRIMARY KEY + + + + @@ -2264,14 +2618,92 @@ </td> </tr> + </table> + + <!-- Inherits --> + + + + + + + <!-- Constraint List --> + + <p> </p> + <table width="100%" cellspacing="0" cellpadding="3"> + <caption>contact_class Constraints</caption> + <tr> + <th>Name</th> + <th>Constraint</th> + </tr> + + <tr class="tr0"> + <td>contact_class_class_check</td> + <td>CHECK (("class" ~ '[[:alnum:]_]'::text))</td> + </tr> + + </table> + + + <!-- Foreign Key Discovery --> + + <p>Tables referencing this one via Foreign Key Constraints:</p> + + <ul> + <li><a href="#public.table.company-to-contact">company_to_contact</a></li> + </ul> + + <ul> + <li><a href="#public.table.person-to-contact">person_to_contact</a></li> + </ul> + + + + <!-- Indexes --> + + + <!-- View Definition --> + + + <!-- List off permissions --> + + + <p> + <a href="#index">Index</a> - + <a href="#public.schema">Schema public</a> + </p> + + <hr> + <h2>Table: + <a name="public.table.country">country</a> + </h2> + + + + <table width="100%" cellspacing="0" cellpadding="3"> + <caption>country Structure</caption> + <tr> + <th>F-Key</th> + <th>Name</th> + <th>Type</th> + <th>Description</th> + </tr> + <tr class="tr0"> <td> + + </td> - <td>contact</td> - <td>character varying(64)</td> + <td>id</td> + <td>serial</td> <td><i> + PRIMARY KEY + + + + @@ -2284,26 +2716,137 @@ <td> </td> - <td>phone</td> - <td>character varying(20)</td> + <td>name</td> + <td>text</td> <td><i> + NOT NULL + + </i> + + </td> + </tr> + + <tr class="tr0"> + <td> + + </td> + <td>short_name</td> + <td>text</td> + <td><i> + + NOT NULL </i> </td> </tr> + <tr class="tr1"> + <td> + + </td> + <td>itu</td> + <td>text</td> + <td><i> + + + + + </i> + <br><br> The ITU Telecommunication Standardization Sector code for calling internationally. For example, the US is 1, Great Britain is 44 + </td> + </tr> + + </table> + + <!-- Inherits --> + + + + + + + <!-- Constraint List --> + + <p> </p> + <table width="100%" cellspacing="0" cellpadding="3"> + <caption>country Constraints</caption> + <tr> + <th>Name</th> + <th>Constraint</th> + </tr> + + <tr class="tr0"> + <td>country_name_check</td> + <td>CHECK ((name ~ '[[:alnum:]_]'::text))</td> + </tr> + + <tr class="tr1"> + <td>country_short_name_check</td> + <td>CHECK ((short_name ~ '[[:alnum:]_]'::text))</td> + </tr> + + </table> + + + <!-- Foreign Key Discovery --> + + <p>Tables referencing this one via Foreign Key Constraints:</p> + + <ul> + <li><a href="#public.table.location">location</a></li> + </ul> + + + + <!-- Indexes --> + + + <!-- View Definition --> + + + <!-- List off permissions --> + + + <p> + <a href="#index">Index</a> - + <a href="#public.schema">Schema public</a> + </p> + + <hr> + <h2>Table: + <a name="public.table.custom-field-catalog">custom_field_catalog</a> + </h2> + + + + <table width="100%" cellspacing="0" cellpadding="3"> + <caption>custom_field_catalog Structure</caption> + <tr> + <th>F-Key</th> + <th>Name</th> + <th>Type</th> + <th>Description</th> + </tr> + <tr class="tr0"> <td> + + </td> - <td>fax</td> - <td>character varying(20)</td> + <td>field_id</td> + <td>serial</td> <td><i> + PRIMARY KEY + + + + @@ -2315,11 +2858,19 @@ <tr class="tr1"> <td> + + <a href="#public.table.custom-table-catalog">custom_table_catalog.table_id</a> + + </td> - <td>email</td> - <td>text</td> + <td>table_id</td> + <td>integer</td> <td><i> + + + + @@ -2332,7 +2883,7 @@ <td> </td> - <td>notes</td> + <td>field_name</td> <td>text</td> <td><i> @@ -2344,12 +2895,80 @@ </td> </tr> + </table> + + <!-- Inherits --> + + + + + + + <!-- Constraint List --> + + + <!-- Foreign Key Discovery --> + + + <!-- Indexes --> + + + <!-- View Definition --> + + + <!-- List off permissions --> + + + <p> + <a href="#index">Index</a> - + <a href="#public.schema">Schema public</a> + </p> + + <hr> + <h2>Table: + <a name="public.table.custom-table-catalog">custom_table_catalog</a> + </h2> + + + + <table width="100%" cellspacing="0" cellpadding="3"> + <caption>custom_table_catalog Structure</caption> + <tr> + <th>F-Key</th> + <th>Name</th> + <th>Type</th> + <th>Description</th> + </tr> + + <tr class="tr0"> + <td> + + + + </td> + <td>table_id</td> + <td>serial</td> + <td><i> + + PRIMARY KEY + + + + + + + + </i> + + </td> + </tr> + <tr class="tr1"> <td> </td> - <td>discount</td> - <td>numeric</td> + <td>extends</td> + <td>text</td> <td><i> @@ -2364,13 +2983,90 @@ <td> </td> - <td>taxincluded</td> - <td>boolean</td> + <td>table_name</td> + <td>text</td> <td><i> - DEFAULT false + + </i> + + </td> + </tr> + + </table> + + <!-- Inherits --> + + + + + + + <!-- Constraint List --> + + + <!-- Foreign Key Discovery --> + + <p>Tables referencing this one via Foreign Key Constraints:</p> + + <ul> + <li><a href="#public.table.custom-field-catalog">custom_field_catalog</a></li> + </ul> + + + + <!-- Indexes --> + + + <!-- View Definition --> + + + <!-- List off permissions --> + + + <p> + <a href="#index">Index</a> - + <a href="#public.schema">Schema public</a> + </p> + + <hr> + <h2>Table: + <a name="public.table.customer">customer</a> + </h2> + + <p> This is now a metadata table that holds information specific to customers. Source info is not part of the entity management </p> + + + + <table width="100%" cellspacing="0" cellpadding="3"> + <caption>customer Structure</caption> + <tr> + <th>F-Key</th> + <th>Name</th> + <th>Type</th> + <th>Description</th> + </tr> + + <tr class="tr0"> + <td> + + + + </td> + <td>id</td> + <td>serial</td> + <td><i> + + PRIMARY KEY + + + + + + + </i> </td> @@ -2379,8 +3075,32 @@ <tr class="tr1"> <td> + + <a href="#public.table.entity">entity.id</a> + + </td> - <td>creditlimit</td> + <td>entity_id</td> + <td>integer</td> + <td><i> + + + + + + + + + </i> + <br><br> This is the relationship between entities and customers + </td> + </tr> + + <tr class="tr0"> + <td> + + </td> + <td>discount</td> <td>numeric</td> <td><i> @@ -2392,12 +3112,28 @@ </td> </tr> + <tr class="tr1"> + <td> + + </td> + <td>taxincluded</td> + <td>boolean</td> + <td><i> + + + + DEFAULT false + </i> + + </td> + </tr> + <tr class="tr0"> <td> </td> - <td>terms</td> - <td>smallint</td> + <td>creditlimit</td> + <td>numeric</td> <td><i> @@ -2412,8 +3148,8 @@ <td> </td> - <td>customernumber</td> - <td>character varying(32)</td> + <td>terms</td> + <td>smallint</td> <td><i> @@ -2428,8 +3164,8 @@ <td> </td> - <td>cc</td> - <td>text</td> + <td>customernumber</td> + <td>character varying(32)</td> <td><i> @@ -2444,7 +3180,7 @@ <td> </td> - <td>bcc</td> + <td>cc</td> <td>text</td> <td><i> @@ -2460,8 +3196,8 @@ <td> </td> - <td>business_id</td> - <td>integer</td> + <td>bcc</td> + <td>text</td> <td><i> @@ -2476,8 +3212,8 @@ <td> </td> - <td>taxnumber</td> - <td>character varying(32)</td> + <td>business_id</td> + <td>integer</td> <td><i> @@ -2540,8 +3276,8 @@ <td> </td> - <td>employee_id</td> - <td>integer</td> + <td>language_code</td> + <td>character varying(6)</td> <td><i> @@ -2556,8 +3292,8 @@ <td> </td> - <td>language_code</td> - <td>character varying(6)</td> + <td>pricegroup_id</td> + <td>integer</td> <td><i> @@ -2572,8 +3308,8 @@ <td> </td> - <td>pricegroup_id</td> - <td>integer</td> + <td>curr</td> + <td>character(3)</td> <td><i> @@ -2588,13 +3324,13 @@ <td> </td> - <td>curr</td> - <td>character(3)</td> + <td>startdate</td> + <td>date</td> <td><i> - + DEFAULT ('now'::text)::date </i> </td> @@ -2604,7 +3340,7 @@ <td> </td> - <td>startdate</td> + <td>enddate</td> <td>date</td> <td><i> @@ -2620,8 +3356,8 @@ <td> </td> - <td>enddate</td> - <td>date</td> + <td>invoice_notes</td> + <td>text</td> <td><i> @@ -2649,14 +3385,10 @@ <!-- Indexes --> - customer_contact_key lower((contact)::text) - customer_customernumber_key customernumber customer_id_key id - customer_name_key lower((name)::text) - <!-- View Definition --> @@ -2869,7 +3601,7 @@ </td> <td>id</td> - <td>integer</td> + <td>serial</td> <td><i> PRIMARY KEY @@ -2879,7 +3611,7 @@ - DEFAULT nextval('id'::regclass) + </i> </td> @@ -3037,6 +3769,8 @@ <a name="public.table.employee">employee</a> </h2> + <p> Is a metadata table specific to employees </p> + <table width="100%" cellspacing="0" cellpadding="3"> @@ -3055,7 +3789,7 @@ </td> <td>id</td> - <td>integer</td> + <td>serial</td> <td><i> PRIMARY KEY @@ -3065,7 +3799,7 @@ - DEFAULT nextval('id'::regclass) + </i> </td> @@ -3074,13 +3808,21 @@ <tr class="tr1"> <td> + + <a href="#public.table.entity">entity.id</a> + + </td> - <td>login</td> - <td>text</td> + <td>entity_id</td> + <td>integer</td> <td><i> + + + + NOT NULL </i> @@ -3091,8 +3833,8 @@ <td> </td> - <td>name</td> - <td>character varying(64)</td> + <td>login</td> + <td>text</td> <td><i> @@ -3107,13 +3849,13 @@ <td> </td> - <td>address1</td> - <td>character varying(32)</td> + <td>startdate</td> + <td>date</td> <td><i> - + DEFAULT ('now'::text)::date </i> </td> @@ -3123,8 +3865,8 @@ <td> </td> - <td>address2</td> - <td>character varying(32)</td> + <td>enddate</td> + <td>date</td> <td><i> @@ -3139,8 +3881,8 @@ <td> </td> - <td>city</td> - <td>character varying(32)</td> + <td>notes</td> + <td>text</td> <td><i> @@ -3155,8 +3897,8 @@ <td> </td> - <td>state</td> - <td>character varying(32)</td> + <td>role</td> + <td>character varying(20)</td> <td><i> @@ -3171,13 +3913,13 @@ <td> </td> - <td>zipcode</td> - <td>character varying(10)</td> + <td>sales</td> + <td>boolean</td> <td><i> - + DEFAULT false </i> </td> @@ -3187,8 +3929,8 @@ <td> </td> - <td>country</td> - <td>character varying(32)</td> + <td>ssn</td> + <td>character varying(20)</td> <td><i> @@ -3203,8 +3945,8 @@ <td> </td> - <td>workphone</td> - <td>character varying(20)</td> + <td>iban</td> + <td>character varying(34)</td> <td><i> @@ -3219,8 +3961,8 @@ <td> </td> - <td>homephone</td> - <td>character varying(20)</td> + <td>bic</td> + <td>character varying(11)</td> <td><i> @@ -3235,13 +3977,13 @@ <td> </td> - <td>startdate</td> - <td>date</td> + <td>managerid</td> + <td>integer</td> <td><i> - DEFAULT ('now'::text)::date + </i> </td> @@ -3251,8 +3993,8 @@ <td> </td> - <td>enddate</td> - <td>date</td> + <td>employeenumber</td> + <td>character varying(32)</td> <td><i> @@ -3267,8 +4009,8 @@ <td> </td> - <td>notes</td> - <td>text</td> + <td>dob</td> + <td>date</td> <td><i> @@ -3279,14 +4021,68 @@ </td> </tr> + </table> + + <!-- Inherits --> + + + + + + + <!-- Constraint List --> + + + <!-- Foreign Key Discovery --> + + + <!-- Indexes --> + + + <!-- View Definition --> + + + <!-- List off permissions --> + + + <p> + <a href="#index">Index</a> - + <a href="#public.schema">Schema public</a> + </p> + + <hr> + <h2>Table: + <a name="public.table.entity">entity</a> + </h2> + + <p> The primary entity table to map to all contacts </p> + + + + <table width="100%" cellspacing="0" cellpadding="3"> + <caption>entity Structure</caption> + <tr> + <th>F-Key</th> + <th>Name</th> + <th>Type</th> + <th>Description</th> + </tr> + <tr class="tr0"> <td> + + </td> - <td>role</td> - <td>character varying(20)</td> + <td>id</td> + <td>serial</td> <td><i> + PRIMARY KEY + + + + @@ -3299,90 +4095,314 @@ <td> </td> - <td>sales</td> - <td>boolean</td> + <td>name</td> + <td>text</td> <td><i> - DEFAULT false - </i> + </i> + <br><br> This is the common name of an entity. If it was a person it may be Joshua Drake, a company Acme Corp. You may also choose to use a domain such as commandprompt.com </td> </tr> <tr class="tr0"> <td> + + <a href="#public.table.entity-class">entity_class.id</a> + + </td> - <td>email</td> - <td>text</td> + <td>entity_class</td> + <td>integer</td> <td><i> + + + + NOT NULL </i> </td> </tr> - <tr class="tr1"> + </table> + + <!-- Inherits --> + + + + + + + <!-- Constraint List --> + + <p> </p> + <table width="100%" cellspacing="0" cellpadding="3"> + <caption>entity Constraints</caption> + <tr> + <th>Name</th> + <th>Constraint</th> + </tr> + + <tr class="tr0"> + <td>entity_name_check</td> + <td>CHECK ((name ~ '[[:alnum:]_]'::text))</td> + </tr> + + </table> + + + <!-- Foreign Key Discovery --> + + <p>Tables referencing this one via Foreign Key Constraints:</p> + + <ul> + <li><a href="#public.table.ap">ap</a></li> + </ul> + + <ul> + <li><a href="#public.table.ar">ar</a></li> + </ul> + + <ul> + <li><a href="#public.table.company">company</a></li> + </ul> + + <ul> + <li><a href="#public.table.company-to-entity">company_to_entity</a></li> + </ul> + + <ul> + <li><a href="#public.table.customer">customer</a></li> + </ul> + + <ul> + <li><a href="#public.table.employee">employee</a></li> + </ul> + + <ul> + <li><a href="#public.table.entity-class-to-entity">entity_class_to_entity</a></li> + </ul> + + <ul> + <li><a href="#public.table.entity-other-name">entity_other_name</a></li> + </ul> + + <ul> + <li><a href="#public.table.oe">oe</a></li> + </ul> + + <ul> + <li><a href="#public.table.partsvendor">partsvendor</a></li> + </ul> + + <ul> + <li><a href="#public.table.person">person</a></li> + </ul> + + <ul> + <li><a href="#public.table.person-to-entity">person_to_entity</a></li> + </ul> + + <ul> + <li><a href="#public.table.vendor">vendor</a></li> + </ul> + + + + <!-- Indexes --> + + + <!-- View Definition --> + + + <!-- List off permissions --> + + + <p> + <a href="#index">Index</a> - + <a href="#public.schema">Schema public</a> + </p> + + <hr> + <h2>Table: + <a name="public.table.entity-class">entity_class</a> + </h2> + + <p> Defines the class type such as vendor, customer, contact, employee </p> + + + + <table width="100%" cellspacing="0" cellpadding="3"> + <caption>entity_class Structure</caption> + <tr> + <th>F-Key</th> + <th>Name</th> + <th>Type</th> + <th>Description</th> + </tr> + + <tr class="tr0"> <td> + + </td> - <td>ssn</td> - <td>character varying(20)</td> + <td>id</td> + <td>serial</td> <td><i> + PRIMARY KEY + + + - </i> + </i> + <br><br> The first 7 values are reserved and permanent </td> </tr> - <tr class="tr0"> + <tr class="tr1"> <td> </td> - <td>iban</td> - <td>character varying(34)</td> + <td>class</td> + <td>text</td> <td><i> - + NOT NULL </i> </td> </tr> - <tr class="tr1"> + <tr class="tr0"> <td> </td> - <td>bic</td> - <td>character varying(11)</td> + <td>active</td> + <td>boolean</td> <td><i> - - + NOT NULL + DEFAULT true </i> </td> </tr> + </table> + + <!-- Inherits --> + + + + + + + <!-- Constraint List --> + + <p> </p> + <table width="100%" cellspacing="0" cellpadding="3"> + <caption>entity_class Constraints</caption> + <tr> + <th>Name</th> + <th>Constraint</th> + </tr> + + <tr class="tr0"> + <td>entity_class_class_check</td> + <td>CHECK (("class" ~ '[[:alnum:]_]'::text))</td> + </tr> + + </table> + + + <!-- Foreign Key Discovery --> + + <p>Tables referencing this one via Foreign Key Constraints:</p> + + <ul> + <li><a href="#public.table.company">company</a></li> + </ul> + + <ul> + <li><a href="#public.table.entity">entity</a></li> + </ul> + + <ul> + <li><a href="#public.table.entity-class-to-entity">entity_class_to_entity</a></li> + </ul> + + + + <!-- Indexes --> + + + <!-- View Definition --> + + + <!-- List off permissions --> + + + <p> + <a href="#index">Index</a> - + <a href="#public.schema">Schema public</a> + </p> + + <hr> + <h2>Table: + <a name="public.table.entity-class-to-entity">entity_class_to_entity</a> + </h2> + + <p> Relation builder for classes to entity </p> + + + + <table width="100%" cellspacing="0" cellpadding="3"> + <caption>entity_class_to_entity Structure</caption> + <tr> + <th>F-Key</th> + <th>Name</th> + <th>Type</th> + <th>Description</th> + </tr> + <tr class="tr0"> <td> + + <a href="#public.table.entity-class">entity_class.id</a> + + + + </td> - <td>managerid</td> + <td>entity_class_id</td> <td>integer</td> <td><i> + + + + + PRIMARY KEY + + + + @@ -3394,11 +4414,26 @@ <tr class="tr1"> <td> + + <a href="#public.table.entity">entity.id</a> + + + + </td> - <td>employeenumber</td> - <td>character varying(32)</td> + <td>entity_id</td> + <td>integer</td> <td><i> + + + + + PRIMARY KEY + + + + @@ -3407,14 +4442,99 @@ </td> </tr> + </table> + + <!-- Inherits --> + + + + + + + <!-- Constraint List --> + + + <!-- Foreign Key Discovery --> + + + <!-- Indexes --> + + + <!-- View Definition --> + + + <!-- List off permissions --> + + + <p> + <a href="#index">Index</a> - + <a href="#public.schema">Schema public</a> + </p> + + <hr> + <h2>Table: + <a name="public.table.entity-other-name">entity_other_name</a> + </h2> + + <p> Similar to company_other_name, a person may be jd, Joshua Drake, linuxpoet... all are the same person. </p> + + + + <table width="100%" cellspacing="0" cellpadding="3"> + <caption>entity_other_name Structure</caption> + <tr> + <th>F-Key</th> + <th>Name</th> + <th>Type</th> + <th>Description</th> + </tr> + <tr class="tr0"> <td> + + <a href="#public.table.entity">entity.id</a> + + + + </td> - <td>dob</td> - <td>date</td> + <td>entity_id</td> + <td>integer</td> <td><i> + + + + + PRIMARY KEY + + + + + + + + </i> + + </td> + </tr> + + <tr class="tr1"> + <td> + + + + </td> + <td>other_name</td> + <td>text</td> + <td><i> + + PRIMARY KEY + + + + @@ -3434,16 +4554,27 @@ <!-- Constraint List --> + <p> </p> + <table width="100%" cellspacing="0" cellpadding="3"> + <caption>entity_other_name Constraints</caption> + <tr> + <th>Name</th> + <th>Constraint</th> + </tr> + + <tr class="tr0"> + <td>entity_other_name_other_name_check</td> + <td>CHECK ((other_name ~ '[[:alnum:]_]'::text))</td> + </tr> + + </table> + <!-- Foreign Key Discovery --> <!-- Indexes --> - employee_id_key id - - employee_name_key lower((name)::text) - <!-- View Definition --> @@ -3688,7 +4819,7 @@ </td> <td>id</td> - <td>integer</td> + <td>serial</td> <td><i> PRIMARY KEY @@ -3698,7 +4829,7 @@ - DEFAULT nextval('id'::regclass) + </i> </td> @@ -3755,11 +4886,19 @@ <tr class="tr0"> <td> + + <a href="#public.table.person">person.id</a> + + </td> - <td>employee_id</td> + <td>person_id</td> <td>integer</td> <td><i> + + + + @@ -3819,8 +4958,6 @@ gl_description_key lower(description) - gl_employee_id_key employee_id - gl_id_key id gl_reference_key reference @@ -3954,13 +5091,21 @@ <tr class="tr0"> <td> + + <a href="#public.table.person">person.id</a> + + </td> - <td>employee_id</td> + <td>person_id</td> <td>integer</td> <td><i> + + + + NOT NULL </i> @@ -4042,7 +5187,7 @@ </td> <td>id</td> - <td>integer</td> + <td>serial</td> <td><i> PRIMARY KEY @@ -4052,7 +5197,7 @@ - DEFAULT nextval('invoiceid'::regclass) + </i> </td> @@ -4338,7 +5483,7 @@ </td> <td>id</td> - <td>integer</td> + <td>serial</td> <td><i> PRIMARY KEY @@ -4348,7 +5493,7 @@ - DEFAULT nextval('jcitemsid'::regclass) + </i> </td> @@ -4517,13 +5662,21 @@ <tr class="tr1"> <td> + + <a href="#public.table.person">person.id</a> + + </td> - <td>employee_id</td> + <td>person_id</td> <td>integer</td> <td><i> + + + + NOT NULL </i> @@ -4663,6 +5816,222 @@ <hr> <h2>Table: + <a name="public.table.location">location</a> + </h2> + + + + <table width="100%" cellspacing="0" cellpadding="3"> + <caption>location Structure</caption> + <tr> + <th>F-Key</th> + <th>Name</th> + <th>Type</th> + <th>Description</th> + </tr> + + <tr class="tr0"> + <td> + + + + </td> + <td>id</td> + <td>serial</td> + <td><i> + + PRIMARY KEY + + + + + + + + </i> + + </td> + </tr> + + <tr class="tr1"> + <td> + + </td> + <td>line_one</td> + <td>text</td> + <td><i> + + + NOT NULL + + </i> + + </td> + </tr> + + <tr class="tr0"> + <td> + + </td> + <td>line_two</td> + <td>text</td> + <td><i> + + + + + </i> + + </td> + </tr> + + <tr class="tr1"> + <td> + + </td> + <td>line_three</td> + <td>text</td> + <td><i> + + + + + </i> + + </td> + </tr> + + <tr class="tr0"> + <td> + + </td> + <td>city_province</td> + <td>text</td> + <td><i> + + + NOT NULL + + </i> + + </td> + </tr> + + <tr class="tr1"> + <td> + + + <a href="#public.table.country">country.id</a> + + + </td> + <td>country_id</td> + <td>integer</td> + <td><i> + + + + + + + NOT NULL + + </i> + + </td> + </tr> + + <tr class="tr0"> + <td> + + </td> + <td>mail_code</td> + <td>text</td> + <td><i> + + + NOT NULL + + </i> + + </td> + </tr> + + </table> + + <!-- Inherits --> + + + + + + + <!-- Constraint List --> + + <p> </p> + <table width="100%" cellspacing="0" cellpadding="3"> + <caption>location Constraints</caption> + <tr> + <th>Name</th> + <th>Constraint</th> + </tr> + + <tr class="tr0"> + <td>location_city_province_check</td> + <td>CHECK ((city_province ~ '[[:alnum:]_]'::text))</td> + </tr> + + <tr class="tr1"> + <td>location_line_one_check</td> + <td>CHECK ((line_one ~ '[[:alnum:]_]'::text))</td> + </tr> + + <tr class="tr0"> + <td>location_mail_code_check</td> + <td>CHECK ((mail_code ~ '[[:alnum:]_]'::text))</td> + </tr> + + </table> + + + <!-- Foreign Key Discovery --> + + <p>Tables referencing this one via Foreign Key Constraints:</p> + + <ul> + <li><a href="#public.table.company">company</a></li> + </ul> + + <ul> + <li><a href="#public.table.company-to-location">company_to_location</a></li> + </ul> + + <ul> + <li><a href="#public.table.person">person</a></li> + </ul> + + <ul> + <li><a href="#public.table.person-to-company">person_to_company</a></li> + </ul> + + + + <!-- Indexes --> + + + <!-- View Definition --> + + + <!-- List off permissions --> + + + <p> + <a href="#index">Index</a> - + <a href="#public.schema">Schema public</a> + </p> + + <hr> + <h2>Table: <a name="public.table.makemodel">makemodel</a> </h2> @@ -4790,7 +6159,7 @@ </td> <td>id</td> - <td>integer</td> + <td>serial</td> <td><i> PRIMARY KEY @@ -4800,7 +6169,7 @@ - DEFAULT nextval('id'::regclass) + </i> </td> @@ -4841,36 +6210,28 @@ <tr class="tr1"> <td> - </td> - <td>vendor_id</td> - <td>integer</td> - <td><i> - - - - - </i> - - </td> - </tr> - - <tr class="tr0"> - <td> + + <a href="#public.table.entity">entity.id</a> + </td> - <td>customer_id</td> + <td>entity_id</td> <td>integer</td> <td><i> + + + + NOT NULL </i> </td> </tr> - <tr class="tr1"> + <tr class="tr0"> <td> </td> @@ -4886,7 +6247,7 @@ </td> </tr> - <tr class="tr0"> + <tr class="tr1"> <td> </td> @@ -4902,7 +6263,7 @@ </td> </tr> - <tr class="tr1"> + <tr class="tr0"> <td> </td> @@ -4918,7 +6279,7 @@ </td> </tr> - <tr class="tr0"> + <tr class="tr1"> <td> </td> @@ -4934,7 +6295,7 @@ </td> </tr> - <tr class="tr1"> + <tr class="tr0"> <td> </td> @@ -4950,7 +6311,7 @@ </td> </tr> - <tr class="tr0"> + <tr class="tr1"> <td> </td> @@ -4966,7 +6327,7 @@ </td> </tr> - <tr class="tr1"> + <tr class="tr0"> <td> </td> @@ -4982,23 +6343,31 @@ </td> </tr> - <tr class="tr0"> + <tr class="tr1"> <td> + + <a href="#public.table.person">person.id</a> + + </td> - <td>employee_id</td> + <td>person_id</td> <td>integer</td> <td><i> + + + + NOT NULL </i> </td> </tr> - <tr class="tr1"> + <tr class="tr0"> <td> </td> @@ -5014,7 +6383,7 @@ </td> </tr> - <tr class="tr0"> + <tr class="tr1"> <td> </td> @@ -5030,7 +6399,7 @@ </td> </tr> - <tr class="tr1"> + <tr class="tr0"> <td> </td> @@ -5046,7 +6415,7 @@ </td> </tr> - <tr class="tr0"> + <tr class="tr1"> <td> </td> @@ -5062,7 +6431,7 @@ </td> </tr> - <tr class="tr1"> + <tr class="tr0"> <td> </td> @@ -5078,7 +6447,7 @@ </td> </tr> - <tr class="tr0"> + <tr class="tr1"> <td> </td> @@ -5094,7 +6463,7 @@ </td> </tr> - <tr class="tr1"> + <tr class="tr0"> <td> </td> @@ -5110,7 +6479,7 @@ </td> </tr> - <tr class="tr0"> + <tr class="tr1"> <td> </td> @@ -5126,7 +6495,7 @@ </td> </tr> - <tr class="tr1"> + <tr class="tr0"> <td> </td> @@ -5142,6 +6511,30 @@ </td> </tr> + <tr class="tr1"> + <td> + + + <a href="#public.table.oe-class">oe_class.id</a> + + + </td> + <td>oe_class_id</td> + <td>integer</td> + <td><i> + + + + + + + NOT NULL + + </i> + + </td> + </tr> + </table> <!-- Inherits --> @@ -5159,8 +6552,6 @@ <!-- Indexes --> - oe_employee_id_key employee_id - oe_id_key id oe_ordnumber_key ordnumber @@ -5181,6 +6572,122 @@ <hr> <h2>Table: + <a name="public.table.oe-class">oe_class</a> + </h2> + + <p> This could probably be done better. But I need to remove the customer_id/vendor_id relationship and instead rely on a classification </p> + + + + <table width="100%" cellspacing="0" cellpadding="3"> + <caption>oe_class Structure</caption> + <tr> + <th>F-Key</th> + <th>Name</th> + <th>Type</th> + <th>Description</th> + </tr> + + <tr class="tr0"> + <td> + + + + </td> + <td>id</td> + <td>smallint</td> + <td><i> + + + + + UNIQUE + + + + + + </i> + + </td> + </tr> + + <tr class="tr1"> + <td> + + + + </td> + <td>oe_class</td> + <td>text</td> + <td><i> + + PRIMARY KEY + + + + + + + + </i> + + </td> + </tr> + + </table> + + <!-- Inherits --> + + + + + + + <!-- Constraint List --> + + <p> </p> + <table width="100%" cellspacing="0" cellpadding="3"> + <caption>oe_class Constraints</caption> + <tr> + <th>Name</th> + <th>Constraint</th> + </tr> + + <tr class="tr0"> + <td>oe_class_id_check</td> + <td>CHECK (((id = 1) OR (id = 2)))</td> + </tr> + + </table> + + + <!-- Foreign Key Discovery --> + + <p>Tables referencing this one via Foreign Key Constraints:</p> + + <ul> + <li><a href="#public.table.oe">oe</a></li> + </ul> + + + + <!-- Indexes --> + + + <!-- View Definition --> + + + <!-- List off permissions --> + + + <p> + <a href="#index">Index</a> - + <a href="#public.schema">Schema public</a> + </p> + + <hr> + <h2>Table: <a name="public.table.orderitems">orderitems</a> </h2> @@ -5202,7 +6709,7 @@ </td> <td>id</td> - <td>integer</td> + <td>serial</td> <td><i> PRIMARY KEY @@ -5212,7 +6719,7 @@ - DEFAULT nextval('orderitemsid'::regclass) + </i> </td> @@ -5466,7 +6973,7 @@ </td> <td>id</td> - <td>integer</td> + <td>serial</td> <td><i> PRIMARY KEY @@ -5476,7 +6983,7 @@ - DEFAULT nextval('id'::regclass) + </i> </td> @@ -6159,7 +7666,7 @@ </td> <td>id</td> - <td>integer</td> + <td>serial</td> <td><i> PRIMARY KEY @@ -6169,7 +7676,7 @@ - DEFAULT nextval('id'::regclass) + </i> </td> @@ -6374,13 +7881,21 @@ <tr class="tr0"> <td> + + <a href="#public.table.entity">entity.id</a> + + </td> - <td>vendor_id</td> + <td>entity_id</td> <td>integer</td> <td><i> + + + + NOT NULL </i> @@ -6509,7 +8024,666 @@ partsvendor_parts_id_key parts_id - partsvendor_vendor_id_key vendor_id + + <!-- View Definition --> + + + <!-- List off permissions --> + + + <p> + <a href="#index">Index</a> - + <a href="#public.schema">Schema public</a> + </p> + + <hr> + <h2>Table: + <a name="public.table.person">person</a> + </h2> + + <p> Every person, must have an entity to derive a common or display name. The correct way to get class information on a person would be person.entity_id->entity_class_to_entity.entity_id. </p> + + + + <table width="100%" cellspacing="0" cellpadding="3"> + <caption>person Structure</caption> + <tr> + <th>F-Key</th> + <th>Name</th> + <th>Type</th> + <th>Description</th> + </tr> + + <tr class="tr0"> + <td> + + + + </td> + <td>id</td> + <td>serial</td> + <td><i> + + PRIMARY KEY + + + + + + + + </i> + + </td> + </tr> + + <tr class="tr1"> + <td> + + + <a href="#public.table.entity">entity.id</a> + + + </td> + <td>entity_id</td> + <td>integer</td> + <td><i> + + + + + + + NOT NULL + + </i> + + </td> + </tr> + + <tr class="tr0"> + <td> + + + <a href="#public.table.salutation">salutation.id</a> + + + </td> + <td>salutation_id</td> + <td>integer</td> + <td><i> + + + + + + + + + </i> + + </td> + </tr> + + <tr class="tr1"> + <td> + + </td> + <td>first_name</td> + <td>text</td> + <td><i> + + + NOT NULL + + </i> + + </td> + </tr> + + <tr class="tr0"> + <td> + + </td> + <td>middle_name</td> + <td>text</td> + <td><i> + + + + + </i> + + </td> + </tr> + + <tr class="tr1"> + <td> + + </td> + <td>last_name</td> + <td>text</td> + <td><i> + + + NOT NULL + + </i> + + </td> + </tr> + + <tr class="tr0"> + <td> + + + <a href="#public.table.location">location.id</a> + + + </td> + <td>primary_location_id</td> + <td>integer</td> + <td><i> + + + + + + + + + </i> + + </td> + </tr> + + </table> + + <!-- Inherits --> + + + + + + + <!-- Constraint List --> + + <p> </p> + <table width="100%" cellspacing="0" cellpadding="3"> + <caption>person Constraints</caption> + <tr> + <th>Name</th> + <th>Constraint</th> + </tr> + + <tr class="tr0"> + <td>person_first_name_check</td> + <td>CHECK ((first_name ~ '[[:alnum:]_]'::text))</td> + </tr> + + <tr class="tr1"> + <td>person_last_name_check</td> + <td>CHECK ((last_name ~ '[[:alnum:]_]'::text))</td> + </tr> + + </table> + + + <!-- Foreign Key Discovery --> + + <p>Tables referencing this one via Foreign Key Constraints:</p> + + <ul> + <li><a href="#public.table.ap">ap</a></li> + </ul> + + <ul> + <li><a href="#public.table.ar">ar</a></li> + </ul> + + <ul> + <li><a href="#public.table.audittrail">audittrail</a></li> + </ul> + + <ul> + <li><a href="#public.table.gl">gl</a></li> + </ul> + + <ul> + <li><a href="#public.table.inventory">inventory</a></li> + </ul> + + <ul> + <li><a href="#public.table.jcitems">jcitems</a></li> + </ul> + + <ul> + <li><a href="#public.table.oe">oe</a></li> + </ul> + + <ul> + <li><a href="#public.table.person-to-company">person_to_company</a></li> + </ul> + + <ul> + <li><a href="#public.table.person-to-contact">person_to_contact</a></li> + </ul> + + <ul> + <li><a href="#public.table.person-to-entity">person_to_entity</a></li> + </ul> + + + + <!-- Indexes --> + + + <!-- View Definition --> + + + <!-- List off permissions --> + + + <p> + <a href="#index">Index</a> - + <a href="#public.schema">Schema public</a> + </p> + + <hr> + <h2>Table: + <a name="public.table.person-to-company">person_to_company</a> + </h2> + + + + <table width="100%" cellspacing="0" cellpadding="3"> + <caption>person_to_company Structure</caption> + <tr> + <th>F-Key</th> + <th>Name</th> + <th>Type</th> + <th>Description</th> + </tr> + + <tr class="tr0"> + <td> + + + <a href="#public.table.location">location.id</a> + + + + + </td> + <td>location_id</td> + <td>integer</td> + <td><i> + + + + + + PRIMARY KEY + + + + + + + + </i> + + </td> + </tr> + + <tr class="tr1"> + <td> + + + <a href="#public.table.person">person.id</a> + + + + + </td> + <td>person_id</td> + <td>integer</td> + <td><i> + + + + + + PRIMARY KEY + + + + + + + + </i> + + </td> + </tr> + + </table> + + <!-- Inherits --> + + + + + + + <!-- Constraint List --> + + + <!-- Foreign Key Discovery --> + + + <!-- Indexes --> + + + <!-- View Definition --> + + + <!-- List off permissions --> + + + <p> + <a href="#index">Index</a> - + <a href="#public.schema">Schema public</a> + </p> + + <hr> + <h2>Table: + <a name="public.table.person-to-contact">person_to_contact</a> + </h2> + + <p> To keep track of the relationship between multiple contact methods and a single individual </p> + + + + <table width="100%" cellspacing="0" cellpadding="3"> + <caption>person_to_contact Structure</caption> + <tr> + <th>F-Key</th> + <th>Name</th> + <th>Type</th> + <th>Description</th> + </tr> + + <tr class="tr0"> + <td> + + + <a href="#public.table.person">person.id</a> + + + + + </td> + <td>person_id</td> + <td>integer</td> + <td><i> + + + + + + PRIMARY KEY + + + + + + + + </i> + + </td> + </tr> + + <tr class="tr1"> + <td> + + + <a href="#public.table.contact-class">contact_class.id</a> + + + + + </td> + <td>contact_class_id</td> + <td>integer</td> + <td><i> + + + + + + PRIMARY KEY + + + + + + + + </i> + + </td> + </tr> + + <tr class="tr0"> + <td> + + + + </td> + <td>contact</td> + <td>text</td> + <td><i> + + PRIMARY KEY + + + + + + + + </i> + + </td> + </tr> + + </table> + + <!-- Inherits --> + + + + + + + <!-- Constraint List --> + + <p> </p> + <table width="100%" cellspacing="0" cellpadding="3"> + <caption>person_to_contact Constraints</caption> + <tr> + <th>Name</th> + <th>Constraint</th> + </tr> + + <tr class="tr0"> + <td>person_to_contact_contact_check</td> + <td>CHECK ((contact ~ '[[:alnum:]_]'::text))</td> + </tr> + + </table> + + + <!-- Foreign Key Discovery --> + + + <!-- Indexes --> + + + <!-- View Definition --> + + + <!-- List off permissions --> + + + <p> + <a href="#index">Index</a> - + <a href="#public.schema">Schema public</a> + </p> + + <hr> + <h2>Table: + <a name="public.table.person-to-entity">person_to_entity</a> + </h2> + + + + <table width="100%" cellspacing="0" cellpadding="3"> + <caption>person_to_entity Structure</caption> + <tr> + <th>F-Key</th> + <th>Name</th> + <th>Type</th> + <th>Description</th> + </tr> + + <tr class="tr0"> + <td> + + + <a href="#public.table.person">person.id</a> + + + + + </td> + <td>person_id</td> + <td>integer</td> + <td><i> + + + + + + PRIMARY KEY + + + + + + + + </i> + + </td> + </tr> + + <tr class="tr1"> + <td> + + + <a href="#public.table.entity">entity.id</a> + + + + + </td> + <td>entity_id</td> + <td>integer</td> + <td><i> + + + + + + PRIMARY KEY + + + + + + + + </i> + + </td> + </tr> + + <tr class="tr0"> + <td> + + </td> + <td>related_how</td> + <td>text</td> + <td><i> + + + + + </i> + + </td> + </tr> + + <tr class="tr1"> + <td> + + </td> + <td>created</td> + <td>date</td> + <td><i> + + + NOT NULL + DEFAULT ('now'::text)::date + </i> + + </td> + </tr> + + </table> + + <!-- Inherits --> + + + + + + + <!-- Constraint List --> + + <p> </p> + <table width="100%" cellspacing="0" cellpadding="3"> + <caption>person_to_entity Constraints</caption> + <tr> + <th>Name</th> + <th>Constraint</th> + </tr> + + <tr class="tr0"> + <td>person_to_entity_check</td> + <td>CHECK ((entity_id <> person_id))</td> + </tr> + + </table> + + + <!-- Foreign Key Discovery --> + + + <!-- Indexes --> <!-- View Definition --> @@ -6546,7 +8720,7 @@ </td> <td>id</td> - <td>integer</td> + <td>serial</td> <td><i> PRIMARY KEY @@ -6556,7 +8730,7 @@ - DEFAULT nextval('id'::regclass) + </i> </td> @@ -6634,7 +8808,7 @@ </td> <td>id</td> - <td>integer</td> + <td>serial</td> <td><i> PRIMARY KEY @@ -6644,7 +8818,7 @@ - DEFAULT nextval('id'::regclass) + </i> </td> @@ -7239,6 +9413,105 @@ <hr> <h2>Table: + <a name="public.table.salutation">salutation</a> + </h2> + + + + <table width="100%" cellspacing="0" cellpadding="3"> + <caption>salutation Structure</caption> + <tr> + <th>F-Key</th> + <th>Name</th> + <th>Type</th> + <th>Description</th> + </tr> + + <tr class="tr0"> + <td> + + + + </td> + <td>id</td> + <td>serial</td> + <td><i> + + + + + UNIQUE + + + + NOT NULL + + </i> + + </td> + </tr> + + <tr class="tr1"> + <td> + + + + </td> + <td>salutation</td> + <td>text</td> + <td><i> + + PRIMARY KEY + + + + + + + + </i> + + </td> + </tr> + + </table> + + <!-- Inherits --> + + + + + + + <!-- Constraint List --> + + + <!-- Foreign Key Discovery --> + + <p>Tables referencing this one via Foreign Key Constraints:</p> + + <ul> + <li><a href="#public.table.person">person</a></li> + </ul> + + + + <!-- Indexes --> + + + <!-- View Definition --> + + + <!-- List off permissions --> + + + <p> + <a href="#index">Index</a> - + <a href="#public.schema">Schema public</a> + </p> + + <hr> + <h2>Table: <a name="public.table.shipto">shipto</a> </h2> @@ -8305,6 +10578,8 @@ <a name="public.table.vendor">vendor</a> </h2> + <p> Now a meta data table </p> + <table width="100%" cellspacing="0" cellpadding="3"> @@ -8323,7 +10598,7 @@ </td> <td>id</td> - <td>integer</td> + <td>serial</td> <td><i> PRIMARY KEY @@ -8333,70 +10608,6 @@ - DEFAULT nextval('id'::regclass) - </i> - - </td> - </tr> - - <tr class="tr1"> - <td> - - </td> - <td>name</td> - <td>character varying(64)</td> - <td><i> - - - - - </i> - - </td> - </tr> - - <tr class="tr0"> - <td> - - </td> - <td>address1</td> - <td>character varying(32)</td> - <td><i> - - - - - </i> - - </td> - </tr> - - <tr class="tr1"> - <td> - - </td> - <td>address2</td> - <td>character varying(32)</td> - <td><i> - - - - - </i> - - </td> - </tr> - - <tr class="tr0"> - <td> - - </td> - <td>city</td> - <td>character varying(32)</td> - <td><i> - - - </i> @@ -8406,109 +10617,21 @@ <tr class="tr1"> <td> - </td> - <td>state</td> - <td>character varying(32)</td> - <td><i> - - - - - </i> - - </td> - </tr> - - <tr class="tr0"> - <td> - - </td> - <td>zipcode</td> - <td>character varying(10)</td> - <td><i> - - - - - </i> - - </td> - </tr> - - <tr class="tr1"> - <td> - - </td> - <td>country</td> - <td>character varying(32)</td> - <td><i> - - - - - </i> - - </td> - </tr> - - <tr class="tr0"> - <td> - - </td> - <td>contact</td> - <td>character varying(64)</td> - <td><i> - - - - - </i> - - </td> - </tr> - - <tr class="tr1"> - <td> - - </td> - <td>phone</td> - <td>character varying(20)</td> - <td><i> - - - - - </i> - - </td> - </tr> - - <tr class="tr0"> - <td> + + <a href="#public.table.entity">entity.id</a> + </td> - <td>fax</td> - <td>character varying(20)</td> + <td>entity_id</td> + <td>integer</td> <td><i> + - - - </i> - - </td> - </tr> - - <tr class="tr1"> - <td> - - </td> - <td>email</td> - <td>text</td> - <td><i> + - + NOT NULL </i> @@ -8519,22 +10642,6 @@ <td> </td> - <td>notes</td> - <td>text</td> - <td><i> - - - - - </i> - - </td> - </tr> - - <tr class="tr1"> - <td> - - </td> <td>terms</td> <td>smallint</td> <td><i> @@ -8547,7 +10654,7 @@ </td> </tr> - <tr class="tr0"> + <tr class="tr1"> <td> </td> @@ -8563,7 +10670,7 @@ </td> </tr> - <tr class="tr1"> + <tr class="tr0"> <td> </td> @@ -8579,7 +10686,7 @@ </td> </tr> - <tr class="tr0"> + <tr class="tr1"> <td> </td> @@ -8595,7 +10702,7 @@ </td> </tr> - <tr class="tr1"> + <tr class="tr0"> <td> </td> @@ -8611,7 +10718,7 @@ </td> </tr> - <tr class="tr0"> + <tr class="tr1"> <td> </td> @@ -8627,7 +10734,7 @@ </td> </tr> - <tr class="tr1"> + <tr class="tr0"> <td> </td> @@ -8643,7 +10750,7 @@ </td> </tr> - <tr class="tr0"> + <tr class="tr1"> <td> </td> @@ -8659,7 +10766,7 @@ </td> </tr> - <tr class="tr1"> + <tr class="tr0"> <td> </td> @@ -8675,7 +10782,7 @@ </td> </tr> - <tr class="tr0"> + <tr class="tr1"> <td> </td> @@ -8691,7 +10798,7 @@ </td> </tr> - <tr class="tr1"> + <tr class="tr0"> <td> </td> @@ -8707,7 +10814,7 @@ </td> </tr> - <tr class="tr0"> + <tr class="tr1"> <td> </td> @@ -8723,7 +10830,7 @@ </td> </tr> - <tr class="tr1"> + <tr class="tr0"> <td> </td> @@ -8739,22 +10846,6 @@ </td> </tr> - <tr class="tr0"> - <td> - - </td> - <td>employee_id</td> - <td>integer</td> - <td><i> - - - - - </i> - - </td> - </tr> - <tr class="tr1"> <td> @@ -8852,12 +10943,8 @@ <!-- Indexes --> - vendor_contact_key lower((contact)::text) - vendor_id_key id - vendor_name_key lower((name)::text) - vendor_vendornumber_key vendornumber @@ -8951,8 +11038,6 @@ <!-- Indexes --> - vendortax_vendor_id_key vendor_id - <!-- View Definition --> @@ -8988,7 +11073,7 @@ </td> <td>id</td> - <td>integer</td> + <td>serial</td> <td><i> PRIMARY KEY @@ -8998,7 +11083,7 @@ - DEFAULT nextval('id'::regclass) + </i> </td> |