partsvendor, entity_id and vendor_id

[Ian Goodacre <..hidden..>]

I encountered an error entering a purchase order with a new part:

DBD::Pg::st execute failed: ERROR:  column "vendor_id" of relation
"partsvendor" does not exist

Rather than vendor_id, the partsvendor relation has an entity_id column
but, despite what one might guess from the name, this is not a reference
to the id in the entity relation.

The partsvendor relation is created with:

CREATE TABLE partsvendor (
  entity_id int not null references entity_credit_account(id) on delete
cascade,
  parts_id int,
  partnumber text,
  leadtime int2,
  lastcost NUMERIC,
  curr char(3),
  entry_id SERIAL PRIMARY KEY
);

>From this it appears that entity_id in partsvendor refers to an
entity_credit_account id.

In contrast, the partscustomer table is created with:

CREATE TABLE partscustomer (
  parts_id int,
  customer_id int not null references entity_credit_account(id) on
delete cascade,
  pricegroup_id int,
  pricebreak numeric,
  sellprice NUMERIC,
  validfrom date,
  validto date,
  curr char(3),
  entry_id SERIAL PRIMARY KEY
);

Note the different names: entity_id in partsvendor and customer_id in
partscustomer. The error would be avoided if column entity_id in
partsvendor were renamed vendor_id, and this would make partsvendor more
consistent with partscustomer as well.

Then in LedgerSMB/IC.pm I find:

        # get vendors
        $query = qq|
              SELECT v.id, e.name, pv.partnumber,
                     pv.lastcost, pv.leadtime, 
                     pv.curr AS vendorcurr
                FROM partsvendor pv
                JOIN vendor v ON (v.id = pv.entity_id) 
                JOIN entity e ON (e.id = pv.entity_id)
               WHERE pv.parts_id = ?
            ORDER BY 2|;


and


                $query = qq|
                    INSERT INTO partsvendor 
                                (vendor_id, parts_id, 
                                partnumber, lastcost, 
                                leadtime, curr)
                         VALUES (?, ?, ?, ?, ?, ?)|;

So here we have a single file that uses both column names, though in
different queries.

The vendor view takes its id column from the id column of the company
table. Does the code ensure a correspondence of id between the company
and entity_credit_account tables? This seems unlikely. Does the code
ensure a correspondence of id between the entity and
entity_credit_account tables? This also seems unlikely. Thus I am
suspicious of the first of the two queries above. Should other criteria
be used to join the tables? I haven't spent much time looking at this
yet and maybe all is well but my initial reaction is that this can't be
correct.

Then in LedgerSMB/OE.pm we have:

        $query = qq|
            SELECT pv.partnumber, pv.leadtime, pv.lastcost, pv.curr,
                pv.vendor_id, v.name
            FROM partsvendor pv
            JOIN vendor v ON (v.id = pv.vendor_id)
            WHERE pv.parts_id = ?|;

Again vendor_id is used to join with the vendor view. This makes sense,
given the name.

Then again, in LedgerSMB/PriceMatrix.pm I find:

        $query = qq|
            SELECT partnumber, lastcost
            FROM partsvendor
            WHERE parts_id = ?
            AND entity_id = $entity_id|;

Here we are back to entity_id. In this case, $entity_id is a form input.
I have don't know what it might normally be set to.


So, we have four queries on partsvendor: two use entity_id and two use
vendor_id. Not much of a basis for deciding to go one way or the other.

Given the entity table, the name entity_id for a foreign key on the
entity_credit_account table seems misleading. But then, given the vendor
view, which takes its id from the company table, the name vendor_id
seems as misleading, though in another direction. Maybe
credit_account_id would be a better name.

I take it from some of the comments on the tables that there once were
customer and vendor tables but these are now views and
entity_credit_account is new, consolidating some of the meta data for
customers and vendors. Are vendor_id and customer_id in the partsvendor
and partscustomer tables old/deprecated names and entity_id is to be the
new name used consistently in both tables and all queries? Then I would
only question the choice of name, again because entity_id suggests the
entity table to me, rather than the entity_credit_account table.

Regards,
Ian Goodacre