Feature Request - transaction attributes

dustymc commented 1 year ago

Is your feature request related to a problem? Please describe.

We keep asking for weird transaction-things.

Describe what you're trying to accomplish

Flexibility (with agents and dates).

Describe the solution you'd like

Create a transaction attribute table, move some stuff to it

Describe alternatives you've considered

Don't.

Additional context

https://github.com/ArctosDB/arctos/issues/6340#issuecomment-1563251369 https://github.com/ArctosDB/arctos/issues/6853 https://github.com/ArctosDB/arctos/issues/4316


arctosprod@arctos>> \d trans
                              Table "core.trans"
       Column       |          Type           | Collation | Nullable | Default 
--------------------+-------------------------+-----------+----------+---------
 transaction_id     | integer                 |           | not null | 
 corresp_fg         | integer                 |           |          | 
 transaction_type   | character varying(18)   |           | not null | 
 nature_of_material | character varying(4000) |           | not null | 
 trans_remarks      | character varying(4000) |           |          | 
 collection_id      | integer                 |           | not null | 
 is_public_fg       | integer                 |           | not null | 0
 trans_date         | character varying(22)   |           |          | 
Indexes:
    "trans_pkey" PRIMARY KEY, btree (transaction_id)
Check constraints:
    "ck_nature_of_material_noprint" CHECK (checkfreetext(nature_of_material))
    "ck_trans_remarks_noprint" CHECK (checkfreetext(trans_remarks))
Foreign-key constraints:
    "fk_cttransaction_type" FOREIGN KEY (transaction_type) REFERENCES cttransaction_type(transaction_type)
    "fk_trans_collection" FOREIGN KEY (collection_id) REFERENCES collection(collection_id)
Referenced by:
    TABLE "accn" CONSTRAINT "fk_accn_trans" FOREIGN KEY (transaction_id) REFERENCES trans(transaction_id)
    TABLE "borrow" CONSTRAINT "fk_borrow_trans" FOREIGN KEY (transaction_id) REFERENCES trans(transaction_id)
    TABLE "loan" CONSTRAINT "fk_loan_trans" FOREIGN KEY (transaction_id) REFERENCES trans(transaction_id)
    TABLE "permit_trans" CONSTRAINT "fk_permittrans_trans" FOREIGN KEY (transaction_id) REFERENCES trans(transaction_id)
    TABLE "project_trans" CONSTRAINT "fk_projecttrans_trans" FOREIGN KEY (transaction_id) REFERENCES trans(transaction_id)
    TABLE "shipment" CONSTRAINT "fk_shipment_trans" FOREIGN KEY (transaction_id) REFERENCES trans(transaction_id)
    TABLE "trans_agent" CONSTRAINT "fk_transagent_trans" FOREIGN KEY (transaction_id) REFERENCES trans(transaction_id)
    TABLE "trans_container" CONSTRAINT "fk_transcontainer_trans" FOREIGN KEY (transaction_id) REFERENCES trans(transaction_id)
Policies:
    POLICY "rls_trans_policy"
      USING ((collection_id IN ( SELECT unnest(get_my_cids()) AS unnest)))
Triggers:
    trans_agent_entered AFTER INSERT ON trans FOR EACH ROW EXECUTE FUNCTION trigger_fct_trans_agent_entered()
    trg_trans_datecheck BEFORE INSERT OR UPDATE ON trans FOR EACH ROW EXECUTE FUNCTION trigger_fct_trg_trans_datecheck()

arctosprod@arctos>> \d loan
                                Table "core.loan"
      Column       |            Type             | Collation | Nullable | Default 
-------------------+-----------------------------+-----------+----------+---------
 transaction_id    | integer                     |           | not null | 
 loan_type         | character varying(25)       |           |          | 
 loan_status       | character varying(20)       |           | not null | 
 loan_instructions | character varying(4000)     |           |          | 
 return_due_date   | timestamp without time zone |           |          | 
 loan_description  | character varying(4000)     |           |          | 
 loan_number       | character varying(255)      |           | not null | 
 closed_date       | timestamp without time zone |           |          | 
 collection_id     | integer                     |           | not null | 
Indexes:
    "loan_pkey" PRIMARY KEY, btree (transaction_id)
    "ix_loan_type" btree (loan_type)
    "ix_u_loan" UNIQUE, btree (collection_id, loan_number)
    "ix_u_loan_number" btree (upper(loan_number::text))
Check constraints:
    "ck_loan_description_noprint" CHECK (checkfreetext(loan_description))
    "ck_loan_instructions_noprint" CHECK (checkfreetext(loan_instructions))
Foreign-key constraints:
    "fk_ctloan_status" FOREIGN KEY (loan_status) REFERENCES ctloan_status(loan_status)
    "fk_ctloan_type" FOREIGN KEY (loan_type) REFERENCES ctloan_type(loan_type)
    "fk_loan_collection" FOREIGN KEY (collection_id) REFERENCES collection(collection_id)
    "fk_loan_trans" FOREIGN KEY (transaction_id) REFERENCES trans(transaction_id)
Referenced by:
    TABLE "loan_item" CONSTRAINT "fk_loanitem_loan" FOREIGN KEY (transaction_id) REFERENCES loan(transaction_id)
    TABLE "media_relations" CONSTRAINT "media_relations_loan_id_fkey" FOREIGN KEY (loan_id) REFERENCES loan(transaction_id)

arctosprod@arctos>> \d accn
                            Table "core.accn"
     Column      |         Type          | Collation | Nullable | Default 
-----------------+-----------------------+-----------+----------+---------
 transaction_id  | integer               |           | not null | 
 accn_type       | character varying(35) |           | not null | 
 accn_status     | character varying(30) |           | not null | 
 accn_number     | character varying(60) |           | not null | 
 estimated_count | integer               |           |          | 
 received_date   | character varying(22) |           |          | 
 collection_id   | integer               |           | not null | 
Indexes:
    "accn_pkey" PRIMARY KEY, btree (transaction_id)
    "ix_u_accn" UNIQUE, btree (collection_id, accn_number)
Check constraints:
    "ck_accn_rec_dt" CHECK (ck_iso8601(received_date::text))
Foreign-key constraints:
    "fk_accn_collection" FOREIGN KEY (collection_id) REFERENCES collection(collection_id)
    "fk_accn_trans" FOREIGN KEY (transaction_id) REFERENCES trans(transaction_id)
    "fk_ctaccn_status" FOREIGN KEY (accn_status) REFERENCES ctaccn_status(accn_status)
    "fk_ctaccn_type" FOREIGN KEY (accn_type) REFERENCES ctaccn_type(accn_type)
Referenced by:
    TABLE "cataloged_item" CONSTRAINT "fk_catitem_accn" FOREIGN KEY (accn_id) REFERENCES accn(transaction_id)
    TABLE "media_relations" CONSTRAINT "media_relations_accn_id_fkey" FOREIGN KEY (accn_id) REFERENCES accn(transaction_id)
Triggers:
    tr_accn_aiu_flat AFTER INSERT OR UPDATE ON accn FOR EACH ROW EXECUTE FUNCTION trigger_fct_tr_accn_aiu_flat()

arctosprod@arctos>> \d borrow
                                  Table "core.borrow"
           Column            |          Type           | Collation | Nullable | Default 
-----------------------------+-------------------------+-----------+----------+---------
 transaction_id              | integer                 |           | not null | 
 lenders_trans_num_cde       | character varying(20)   |           |          | 
 lenders_invoice_returned_fg | integer                 |           | not null | 
 borrow_status               | character varying(20)   |           | not null | 
 lenders_instructions        | character varying(4000) |           |          | 
 lender_loan_type            | character varying(60)   |           |          | 
 borrow_number               | character varying(30)   |           | not null | 
 received_date               | character varying(22)   |           |          | 
 due_date                    | date                    |           |          | 
 lenders_loan_date           | character varying(22)   |           |          | 
 collection_id               | integer                 |           | not null | 
Indexes:
    "borrow_pkey" PRIMARY KEY, btree (transaction_id)
    "ix_u_borrow" UNIQUE, btree (collection_id, borrow_number)
Check constraints:
    "ck_borrow_due_date" CHECK (ck_iso8601(due_date::text))
    "ck_borrow_lenders_loan_date" CHECK (ck_iso8601(lenders_loan_date::text))
    "ck_borrow_received_date" CHECK (ck_iso8601(received_date::text))
Foreign-key constraints:
    "fk_borrow_collection" FOREIGN KEY (collection_id) REFERENCES collection(collection_id)
    "fk_borrow_trans" FOREIGN KEY (transaction_id) REFERENCES trans(transaction_id)
    "fk_ctborrow_status" FOREIGN KEY (borrow_status) REFERENCES ctborrow_status(borrow_status)
Referenced by:
    TABLE "media_relations" CONSTRAINT "media_relations_borrow_id_fkey" FOREIGN KEY (borrow_id) REFERENCES borrow(transaction_id)

Priority Please assign a priority-label. Unprioritized issues gets sent into a black hole of despair.

Jegelewicz commented 1 year ago

Sounds awesome - when do we start developing code tables?....

dustymc commented 1 year ago

when do we start developing code tables?

Before anything else, that's why I put the table definitions in the Issue; what can be moved? (My without-looking answer might be "whatever's NULLable.")

Jegelewicz commented 1 year ago

My without-looking answer might be "whatever's NULLable."

I agree in principle, but making nearly everything an attribute means that some things will just get ignored (I mean, they probably are now, but putting them one step further down the chain makes it even more likely!).

trans_remarks

seems like a great candidate and having two attributes (one that is "curatorial - aka not viewable by the public, which I am bringing up due to a comment by @AJLinn about making accessions public)

public remarks - information related to the transaction that should be publicly available private remarks - curatorial information related to the transaction that should not be publicly available

dates

trans_date closed_date received_date due_date return_due_date lenders_loan_date

see https://github.com/ArctosDB/arctos/issues/6598 These seem like great candidates as:

transaction processing history - the date a particular event in the transaction occurred categorical values for "date type" with the date in the determined date field:

initiation - date the transaction is initiated
due - the date all objects (parts) in the loan are expected to be returned
return - the date all objects (parts) in the loan were returned to their proper place in the collection
received - the date all objects (parts) in the transaction have been received by the collection (use return date to indicate when they are installed in their appropriate storage locations)
closed - the date all transaction requirements are met and the transaction is considered completed others? (assuming "ship date" is part of any shipments...)

counts

https://github.com/ArctosDB/arctos/issues/4316

estimated part count - an estimate of the number of objects (parts) to be included in the transaction estimated record count - an estimate of the number of catalog records to be included in the transaction

https://github.com/ArctosDB/arctos/issues/6853 These seem like they could be automated and included on the transaction page:

record count - number of catalog records included in the transaction returned count - number of objects included in the transaction with disposition = in collection on loan count - number of objects included in the transaction with disposition = on loan used up count - number of objects included in the transaction with disposition = used up

identifiers

lenders_trans_num_cde - the identifier assigned by the lending institution. or should this be more generic and instead be:

other transaction identifier - any identifier assigned to the transaction in addition to the transaction number of record in Arctos. Determiner should be the issuing agent.

OK - that's my thoughts for now....

dustymc commented 1 year ago

just get ignored (I mean, they probably are now

Yes, that's the split - don't think it matters HOW they get ignored....

dates

If dates should DO STUFF then they should probably remain "fields" - querying for "whateverdate past whatever event..." and getting 372 different answers (attributes are always in "zero or more" relationships to their parent) would be difficult to work with. (That is, were I responsible for loans I'd want loan.due_date sending me notifications - but I'd have been screaming about it being NULLable for a while too, so ??)

automated

Not really, there's no connection between disposition and loan. Send an item on loan, get it back, repeat 40 more times, send it out again, the first loan now has the item with a disposition of 'on loan' even though that particular loan was entirely dealt with decades ago. I can get the counts easy enough, but they don't mean what you are implying.

other transaction identifier

I like it, the alternative is probably a buch of things all used once or twice, general seems appropriate here.

mkoo commented 1 year ago

I like this in principle.... trying to imagine the regular usages for this. Can we add to next Issues meeting for a little more discussion/ input?

ccicero commented 1 year ago

I agree ith @mkoo, add to issues meeting agenda.

For counts, I think we need perhaps three - all of which can go in attributes: 1) estimated - what you think there should be (i.e., in accessions) 2) in hand (or some other term) - what you actually have in hand for cataloging or loaning; use cases would be a) tracking material as it is prepared prior to cataloging, b) loaning uncataloged material, ... this is NEW and relates to issue 6853 3) cataloged - actual count of cataloged records, could/should be automated

dustymc commented 1 year ago

CT discussion:

Loan type could/should be reduced to (parts, data).
Current loan type moves to attributes, not sure if combined with or separate from https://github.com/ArctosDB/arctos/issues/6968

dustymc commented 3 months ago

Going next task, I know how to do this.

ArctosDB / dev