[Date Prev][Date Next][Thread Prev][Thread Next][Date Index][Thread Index]
Re: Replacing vendor and customer tables
- Subject: Re: Replacing vendor and customer tables
- From: "Chris Travers" <..hidden..>
- Date: Fri, 6 Jul 2007 11:17:27 -0700
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.
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.
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.
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,
PRIMARY KEY (bic, iban)
CREATE TABLE entity_credit_account (
id serial not null,
entity_id int references entity(id),
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(),
PRIMARY KEY (entity_account),
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,
primary key(entity_credit_id, contact)
As I say, this is just a rough idea. If you work out something, I
will create a view for now so that the impact on ar/ap is as little as
possible while we work out the rest of the deatils.
On 7/6/07, Aurynn Shaw <..hidden..> wrote:
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.
The PostgreSQL Company - Command Prompt, Inc. 1.503.667.4564 ext 103
PostgreSQL Replication, Consulting, Custom Development, 24x7 support
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.
Ledger-smb-devel mailing list