[Date Prev][Date Next][Thread Prev][Thread Next][Date Index][Thread Index]

Re: Replacing vendor and customer tables

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

..hidden..
--------------------------------------------------------------------- ---- 
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
..hidden..
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
..hidden..
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

..hidden..