Hi Chris;
>
> Looking at these tables at the moment here are the thoughts that
> occur to me:
>
> 1) BIC and IBAN are not really used by the rest of the application. I
> am thinking of breaking these off into a separate table anyway.
This sounds very reasonable, to me
> 2) A customer could also be a vendor. So entity_id could not be
> assumed to be unique. Furthermore, you need an ability to determine
> whether the credit limit is for ar or ap purposes.
I was under the impression that JD's entity patches gave us the
ability to have a customer that is also a vendor, using the same
entity ID? Would we not be using that it in this case?
Also, isn't the AR/AP delineation based on Customer/Vendor anyway? So
an entity_class tag column would give us AR or AP credit limits, it
seems to me.
However, it would create some ugliness with, fx, requiring 2 rows in
the metadata table, one for customer, and the next for vendor, both
keyed to the same entity.
>
> So it is a little more complicated than it initially appears, but I
> would like to see these tables go away too and would be glad to help.
:) Isn't it always?
>
> Here is my rough idea of how it should work:
>
> CREATE TABLE entity_bank_account (
> id serial not null,
> entity_id int references entity(id) not null,
> bic varchar,
> iban varchar,
> UNIQUE (id),
> PRIMARY KEY (bic, iban)
> );
>
> CREATE TABLE entity_credit_account (
> id serial not null,
> entity_id int references entity(id),
> discount numeric,
> discountterms int,
> terms int,
> creditlimit numeric,
> credit_type varchar(2),
> entity_account varchar,
> business_type int references business(id),
> language_code varchar(6) references language(code),
> pricegroup_id int references pricegroup (id),
> currency varchar(3), --todo, move currency to separate table
> startdate date default now(),
> enddate date,
> PRIMARY KEY (entity_account),
> UNIQUE (id),
> CHECK (credit_type IN ('ar', 'ap'))
> );
>
> ALTER TABLE company ADD COLUMN sic_code varchar;
>
> CREATE TABLE entity_account_to_contact (
> entity_credit_id int references entity_credit_account(id),
> contact_class_id int references contact_class(id) not null,
> contact text,
> primary key(entity_credit_id, contact)
> );
I haven't run across this before; where would I find what it's for?
Thanks,
Aurynn
>>
>> I'm digging around in the Vendor and Customer tables, per JD's entity
>> modifications, and it looks like both tables are fundamentally
>> identical - The only difference is the customer table has
>> customer.invoice_notes and customer.id.
>>
>> I'm going to remove both tables and replace it with entity_metadata,
>> fkey'd to both entity and entity_class. Queries that directly read
>> from vendor or customer would be easily modified to, for example:
>>
>> SELECT * FROM entity_metadata em
>> JOIN entity e on em.entity_id = e.id
>> WHERE e.name = ?
>>
>> to replace the query used to verify a vendor or customer in Add
>> Invoice.
>>
>> Entity_metadata would have the invoice_notes, but lack the id field.
>>
>> Thoughts?
>>
>> Thanks,
>> Aurynn Shaw
>>
>> The PostgreSQL Company - Command Prompt, Inc. 1.503.667.4564 ext 103
>> PostgreSQL Replication, Consulting, Custom Development, 24x7 support
>>
>> [EMAIL PROTECTED]
>>
>>
>>
>> ---------------------------------------------------------------------
>> ----
>> This SF.net email is sponsored by DB2 Express
>> Download DB2 Express C - the FREE version of DB2 express and take
>> control of your XML. No limits. Just data. Click to get it now.
>> http://sourceforge.net/powerbar/db2/
>> _______________________________________________
>> Ledger-smb-devel mailing list
>> [email protected]
>> https://lists.sourceforge.net/lists/listinfo/ledger-smb-devel
>>
>
> ----------------------------------------------------------------------
> ---
> This SF.net email is sponsored by DB2 Express
> Download DB2 Express C - the FREE version of DB2 express and take
> control of your XML. No limits. Just data. Click to get it now.
> http://sourceforge.net/powerbar/db2/
> _______________________________________________
> Ledger-smb-devel mailing list
> [email protected]
> https://lists.sourceforge.net/lists/listinfo/ledger-smb-devel
Aurynn Shaw
The PostgreSQL Company - Command Prompt, Inc. 1.503.667.4564 ext 103
PostgreSQL Replication, Consulting, Custom Development, 24x7 support
[EMAIL PROTECTED]
-------------------------------------------------------------------------
This SF.net email is sponsored by DB2 Express
Download DB2 Express C - the FREE version of DB2 express and take
control of your XML. No limits. Just data. Click to get it now.
http://sourceforge.net/powerbar/db2/
_______________________________________________
Ledger-smb-devel mailing list
[email protected]
https://lists.sourceforge.net/lists/listinfo/ledger-smb-devel
