Table-ContactXREF

The XREF table contains a list of additional records that relate to a contact record. In it's basic format it simply provides a phone number, email address, url link, or simple single piece of data, but additionally it provides a link to such information as the type of contact, and expanded data such as address.

A single numeric id is retained to allow direct access to any entry to allow additional mapping tables to be created. The secondary key is built up of contact_id, source, and xorder which must be unique. xorder provides a special purpose to identify record types which must be unique ( contact type ) in which case xorder is '0', while for records where there may be multiple entries such as phone or email entries, xorder is a number and used to define the order of these records.

The data element of the record has three parts, XREF is a link to other content on the system. This may be an address record, or property record, but even that is evolving and looking more like just another 'contact' record with a 'property' type rather than the separate table that currently exists. The XKEY and XKEY_EXT pair provide the actual data for the record, such as POSTCODE and HOUSE_NAME or with a blank XKEY, phone number, email address, and other simple data, while some XKEY entries would not have any XKEY_EXT data, such as an NLPG or other reference link to another system.

A notes field is almost essential, and allows additional information to be added to a specific record. It also allows extended data to be stored for other applications, although it would be better to provide that is additional tables anyway.

The final block of fields provide information on the duration of a record, as all cross reference information must be able to indicate the period for which the information applies. For example, addresses change as people move, but previous addresses need to be retained, and the date of a move managed. contact information may apply for a period of time such as when a client is away for a period, so as well as current data, the history of activity must also be accessible. It is now just a matter of now working out how to handle 'notes and correspondence'. Whether to cross link to comment records for textual records, or simply use the data field of the XREF record ...

{code source=sql} 
CREATE TABLE CONTACT_XREF 
( 
XREF_ID Bigint NOT NULL, 
CONTENT_ID Bigint NOT NULL, 
SOURCE Varchar(20) NOT NULL, 
XORDER Smallint DEFAULT 0 NOT NULL, 
XREF Bigint,
XKEY Varchar(32), 
XKEY_EXT Varchar(256), 
DATA Blob sub_type 1, 
START_DATE Timestamp, 
LAST_UPDATE_DATE Timestamp, 
ENTRY_DATE Timestamp, 
END_DATE Timestamp, 
CONSTRAINT PK_CONTACT_XREF PRIMARY KEY (XREF_ID)
)
{/code}