Re: Replacing vendor and customer tables

["Chris Travers" <..hidden..>]

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:
> Hiya;
>
> 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