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 <[EMAIL PROTECTED]> 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
>
> [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
