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

Re: Replacing vendor and customer tables

Hi Aurynn,

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,
   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)

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.

Best Wishes,
Chris Travers

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.


Aurynn Shaw

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