agent address revision request

[GoogleCodeExporter]

Agent address is split into things like 'street_address_1' and 'zip_code.' 
These are then re-combined into formatted_address for display and use. This 
partitioning makes is difficult to import/export data, and it isn't clear what 
the benefits of this partitioning over one field are or could be.

Proposal: Alter table Addr to

ADDR_ID (Primary Key)
AGENT_ID (Foreign Key->Agent)
ADDR_TYPE (http://goo.gl/qTnY6)
VALID_ADDR_FG (Boolean)
Address (varchar 4000)
ADDR_REMARKS

Original issue reported on code.google.com by dust...@gmail.com on 28 Oct 2011 at 9:12

• Blocking: #589

[GoogleCodeExporter]

[deleted comment]

[GoogleCodeExporter]

VertNet/IPT medatadata may need address components broken out.

Original comment by dust...@gmail.com on 16 Jan 2013 at 6:03

[GoogleCodeExporter]

I like having institution separate from address, but am fine with concatenating 
address into one field except maybe country. So what about this modification of 
proposal:

Alter table Addr to

ADDR_ID (Primary Key)
AGENT_ID (Foreign Key->Agent)
ADDR_TYPE (http://goo.gl/qTnY6)
VALID_ADDR_FG (Boolean)
Institution (varchar XXX)
Address (varchar 4000)
Country (varchar XXX)
ADDR_REMARKS

Original comment by carla...@gmail.com on 5 Jun 2013 at 2:17

[GoogleCodeExporter]

Issue 589 has been merged into this issue.

Original comment by dust...@gmail.com on 28 Jun 2013 at 6:10

[GoogleCodeExporter]

[deleted comment]

[GoogleCodeExporter]

See https://groups.google.com/d/msg/arctos-ac/DNGrv8hmjnM/mPQMAR_9_cEJ

It would be good to get some more input (from where??) on this - is there 
really no standard (real or de facto) for managing addresses?

What utility do we need/want from addresses? So far identified:

1) Is a shipment foreign or not? (Shipment.foreign_shipment_flag is 
demonstrably useless; being able to compare shipped_from and shipped_to country 
would be a much more robust solution, but requires controlled country values.)

2) Autofill shipping labels. (Possible? Practical? Standards from shippers??)

3) Sharing data (see VertNet [really IPT] comment above).

Here are the data. They're pretty random; the column names/concepts apparently 
have little to do with the contents. NOT NULL columns are just trashier as 
people enter "-" and many variations of "N/A" and spaces and etc. to bypass the 
restrictions.

STREET_ADDR1 - text, NOT NULL, uncontrolled - http://pastebin.com/BzzbJGin
STREET_ADDR2 - text, NOT NULL, uncontrolled - http://pastebin.com/WyBzbEkK
CITY - text, NOT NULL, uncontrolled - http://pastebin.com/kwkztFYD
STATE - text, NOT NULL, uncontrolled - http://pastebin.com/wu1jjF6n
ZIP - - text, NOT NULL, uncontrolled http://pastebin.com/7XuzfpeJ
COUNTRY_CDE - text, NOT NULL, uncontrolled - http://pastebin.com/pEWpe0Ur
MAIL_STOP - text, NOT NULL, uncontrolled - http://pastebin.com/PC7FvcL2
ADDR_TYPE - 
http://arctos.database.museum/info/ctDocumentation.cfm?table=CTADDR_TYPE
JOB_TITLE - text, NOT NULL, uncontrolled - http://pastebin.com/Lac6VYJR
VALID_ADDR_FG - boolean, partially redundant with a confused by ADDR_TYPE - 
recommend getting rid of the flag
ADDR_REMARKS - This is mostly phone numbers (so I'm not putting the data 
online). What purpose does this concept serve?? Recommend removal.
INSTITUTION - text, NOT NULL, uncontrolled - http://pastebin.com/FjvNfTXC
DEPARTMENT - text, NOT NULL, uncontrolled - http://pastebin.com/SeGVEU5y

If we're to fill in UPS shipping labels (and IPT forms and anything else that 
requires structure), we probably need to retain the current fields but clean 
and control them. The apparent lack of standards and state of the current data 
suggest this would be somewhere between "difficult" and "futile."

Carla's hybrid model (Comment 3) should address the "is this a foreign 
shipment?" question in any context, but will NOT allow sharing data with 
structure (eg, vertnet, shipping labels).

Institution (Comment3) is better as an Trans_Agent, and I recommend dropping it 
from the hybrid model proposal.

Lacking better ideas and needing a consensus in order to proceed with related 
development, I'm unenthusiastically inclined to embrace Carla's 
string-plus-country hybrid model.

Original comment by dust...@gmail.com on 28 Jun 2013 at 7:39