TIMSS 6: Addresses

There seems to be a lot of confusion on how addresses are stored in TIMSS 6. First of all, addresses are broken into 2 parts: the address & the address detail. The address is stored in the CUS_ADDRESS (AD) table and has the street address (i.e. Address lines, City, State, Zip, Country).

The address detail, stored in the CUS_ADDRESS_DETAIL (CAD) table, has the label name, job title, company, personal line, mail stop, etc. It's stored this way so many customers can share the same street address.

Joining the 2 tables is done on the CUS_ADDRESS_ID. The CAD will have the MASTER_CUSTOMER_ID of the customer we are looking at. The CA will have the MASTER_CUSTOMER_ID of the customer who is the "owner" of the address. There can be many references to the CUS_ADDRESS_ID in the CAD table, but only one in the CA table. If an address is linked, the MASTER_CUSTOMER_ID will be different customers (i.e. the person in the CAD is not the Owner of the address in the CA table).

A primary address for the customer is denoted by the PRIORITY_SEQ field in the CAD. A value of 0 (zero) is the primary. The sequence also controls the display order on the Customer's Address tab.

There are also FORMATTED_ADDRESS and FORMATTED_DETAIL fields in the CA & CAD tables, respectively, that have all the address lines separated by CRLF (carriage return/line feed, ASCII values 13 and 10). These 2 fields can be added together to give the full address label. It's very handy when merging into a document because you only have to use one field and it's already formatted for the given country as you've defined in TIMSS.

When linking to an address (from the ORDER_DETAIL table) there are 3 thinks that make the address distinct: the MASTER_CUSTOMER_ID in the CAD table, the CUS_ADDRESS_ID, and the ADDRESS_TYPE_CODE in the CAD table. A customer can have multiple address, even multiple address linked to the same address, but they must be of different types.

A couple handy views are in TIMSS. CUS_ADDRESS_VW can be used when linking to addresses from orders or committee terms. CUS_PRIMARY_ADDRESS_VW is an easy way to get the customer's primary address. You can always refer to these views for a reminder how the CA and CAD should be joined.

Applies to: TIMSS6