> 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?