Relational-Notes

Archived from the Xataface Users forum.

Aoirthoir — Wed Jul 19, 2006 8:15 am

What are Relational-Level Notes?

A relational note system allows us to enter notes on any table, or child tables which can then be viewed by the parent table.

So for instance I have several tables:

CUSTOMER_PERSON ( an individual person’s information )
CUSTOMER_ADDRESS ( since many customers have multiple addresses )
CUSTOMER_PHONE ( since many customers have multiple phone numbers )

Now for any customer I will probably want notes to be taken on different dates. So I have a notes table set up just for that. However, if I enter address information, I might want to detail notes on that, for instance why I am using this, why I added the address, why I changed it and so forth. While I have a comments field in every table, I want notes as a seperate module because I wish to have a running, dated record, of what has been done.

So if I enter notes on an address, I wish to also, at the Customer level, be able to see those notes. Well that is exactly what the Relational-Notes system does. It allows us to go into a customer, and see all the notes entered on any of the Customer modules. Also it lets us go into a module ( Phones, Addresses, Orders etc ) and see notes specific to just that module.

In dataface this is very very VERY easy to accomplish. Here is how.

First, in every table I have an ID_RECORD field. ( All of my ID_ fields tell me this is table specific information and not needed by the user ). If there is ANY chance that the table will be a child table to another table, I also place a PARENT_ID_RECORD field in it. This of course then is used to relate to the parent. For the notes table I have an additional field GRANDPARENT_ID_RECORD.

Thus if I create a note in the customer record, GRANDPARENT_ID_RECORD is set to the ID_RECORD of the customer. If I create a note in a module ( addresses, phones ) then GRANDPARENT_ID_RECORD of the note table is set to PARENT_ID_RECORD of the module table ( address ) and PARENT_ID_RECORD of the note table is set to ID_RECORD of the module table ( address ).

Lastly, since modules might end up having the same value in ID_RECORD ( an address with a value 12 and a phone with a value 12 ) I set up a field to let me know which module ( table ) this note is for. I call it PARENT_TABLE_NAME and in dataface I just put a text value. ( I got this idea from Mr. Steve Hannah the Dataface creator see here for his examples: [url]http://framework.weblite.ca/forum/dataface-users/86[/url].

So that is all. Here is the dataface code and my table layouts:

Code: Select all

`` CREATE TABLE CUSTOMER_PERSON (
  ID_RECORD mediumint( 8 ) unsigned zerofill NOT NULL auto_increment COMMENT ‘Unique RecordID. Auto Incremented. Used for Table Relationships and to identify specific record.’,
  ID_CHANGED timestamp NOT NULL default CURRENT_TIMESTAMP on update CURRENT_TIMESTAMP COMMENT ‘Date record was added or changed’,
  NAME_FIRST varchar( 100 ) NOT NULL COMMENT ‘Customer’’s first name.’,
  NAME_MIDDLE_INITIAL varchar( 1 ) default NULL COMMENT ‘Middle initial.’,
  NAME_LAST varchar( 100 ) NOT NULL COMMENT ‘Customer’’s last name.’,
  LINK_LIST_TYPE_NAME_TITLE varchar( 20 ) default NULL COMMENT ‘Links to name title table.’,
  LINK_LIST_TYPE_NAME_SUFFIX varchar( 20 ) default NULL COMMENT ‘links to name suffix table.’,
  SSN_3 smallint( 3 ) unsigned zerofill default NULL COMMENT ‘First three digits of ssn.’,
  SSN_2 smallint( 2 ) unsigned zerofill default NULL COMMENT ‘Middle two digits of ssn.’,
  SSN_4 smallint( 4 ) unsigned zerofill default NULL COMMENT ‘Last four digits of ssn.’,
  DATE_BIRTH date default NULL COMMENT ‘Birth date.’,
  GENDER enum( ‘Female’,’Male’,’Intersexed’ ) default NULL COMMENT ‘Gender.’,
  HEIGHT varchar( 20 ) default NULL COMMENT ‘Height.’,
  WEIGHT varchar( 20 ) default NULL COMMENT ‘Weight.’,
  EMAIL varchar( 100 ) default NULL COMMENT ‘Customer’’s email address.’,
  COMMENT text COMMENT ‘Usually a tiny box for typing freeform comments.’,
  PRIMARY KEY  ( ID_RECORD )
) ENGINE=MyISAM DEFAULT CHARSET=latin1 COMMENT=’Personal information on the Customer.’

CREATE TABLE CUSTOMER_ADDRESS (
  ID_RECORD mediumint( 8 ) unsigned zerofill NOT NULL auto_increment COMMENT ‘Unique RecordID. Auto Incremented. Used for Table Relationships and to identify specific record.’,
  ID_CHANGED timestamp NOT NULL default CURRENT_TIMESTAMP on update CURRENT_TIMESTAMP COMMENT ‘Date record was added or changed’,
  PARENT_ID_RECORD mediumint( 8 ) unsigned zerofill NOT NULL COMMENT ‘This is the same as ID_RECORDID of the calling table in a relationship. In this table it is not unique. Used for identification and somewhat dynamic relationships.’,
  LINK_LIST_TYPE_DELIVERY varchar( 20 ) default NULL COMMENT ‘Links to the Delivery Type Table. Examples UPS/DHL/Postal etc.’,
  ADDRESS_LINE1 varchar( 100 ) NOT NULL COMMENT ‘Usually the street address’,
  ADDRESS_LINE2 varchar( 100 ) default NULL COMMENT ‘Generally a Suite, Box, Block etc.’,
  CITY varchar( 100 ) NOT NULL COMMENT ‘City’,
  LINK_LIST_STANDARD_USA_STATE_NAMES char( 2 ) NOT NULL COMMENT ‘Links to the State Abbreviation/Name Table’,
  ZIP_5 mediumint( 5 ) unsigned zerofill NOT NULL COMMENT ‘Zip Code ( Will eventually link to a Zip code table’,
  ZIP_4 smallint( 4 ) unsigned zerofill default NULL COMMENT ‘Plus Four of the Zip’,
  DATE_RANGE_DELIVER_BEGIN date default NULL COMMENT ‘Date to start using this address’,
  DATE_RANGE_DELIVER_END date default NULL COMMENT ‘Date to cease using this address’,
  COMMENT text COMMENT ‘Usually a tiny box for typing freeform comments.’,
  PRIMARY KEY  ( ID_RECORD )
) ENGINE=MyISAM DEFAULT CHARSET=latin1 COMMENT=’Customer’’s Address and Delivery Info’

CREATE TABLE CUSTOMER_PHONE (
  ID_RECORD mediumint( 8 ) unsigned zerofill NOT NULL auto_increment COMMENT ‘Unique RecordID. Auto Incremented. Used for Table Relationships and to identify specific record.’,
  ID_CHANGED timestamp NOT NULL default CURRENT_TIMESTAMP on update CURRENT_TIMESTAMP COMMENT ‘Date record was added or changed’,
  PARENT_ID_RECORD mediumint( 8 ) unsigned zerofill NOT NULL COMMENT ‘This is the same as ID_RECORDID of the calling table in a relationship. In this table it is not unique. Used for identification and somewhat dynamic relationships.’,
  LINK_LIST_TYPE_PHONE varchar( 20 ) NOT NULL COMMENT ‘This links to the  TYPE_PHONE table. ( Examples Cell/Home/Fax etc )’,
  AREA_CODE smallint( 3 ) unsigned zerofill NOT NULL COMMENT ‘Area code’,
  PREFIX smallint( 3 ) unsigned zerofill NOT NULL COMMENT ‘First Three digits after the area code.’,
  SUFFIX smallint( 4 ) unsigned zerofill NOT NULL COMMENT ‘Last four digits of the number,’,
  EXTENSION smallint( 4 ) unsigned zerofill default NULL COMMENT ‘Extension’,
  COMMENT text COMMENT ‘Usually a tiny box for typing freeform comments.’,
  PRIMARY KEY  ( ID_RECORD )
) ENGINE=MyISAM DEFAULT CHARSET=latin1 COMMENT=’Phone info on the Customer’

CREATE TABLE CUSTOMER_NOTE (
  ID_RECORD mediumint( 8 ) unsigned zerofill NOT NULL auto_increment COMMENT ‘Unique RecordID. Auto Incremented. Used for Table Relationships and to identify specific record.’,
  ID_CHANGED timestamp NOT NULL default CURRENT_TIMESTAMP on update CURRENT_TIMESTAMP COMMENT ‘Date record was added or changed’,
  GRANDPARENT_ID_RECORD mediumint( 8 ) unsigned zerofill NOT NULL COMMENT ‘This is the same as PARENT_ID_RECORDID of the calling table in a relationship. In this table it is not unique. Used for identification and somewhat dynamic relationships.’,
  PARENT_ID_RECORD mediumint( 8 ) unsigned zerofill default NULL COMMENT ‘This is the same as ID_RECORDID of the calling table in a relationship. In this table it is not unique. Used for identification and somewhat dynamic relationships.’,
  PARENT_TABLE_NAME varchar( 50 ) default NULL COMMENT ‘This is the name of the calling table.’,
  COMMENT text COMMENT ‘Usually a tiny box for typing freeform comments.’,
  PRIMARY KEY  ( ID_RECORD )
) ENGINE=MyISAM DEFAULT CHARSET=latin1 COMMENT=’Notes on the Customer and any child tables’ ``

Now the dataface code:

tables/CUSTOMER_PERSON/relationships.ini

Code: Select all

[CUSTOMER_NOTE] CUSTOMER_NOTE.GRANDPARENT_ID_RECORD = "$ID_RECORD"

tables/CUSTOMER_ADDRESS/relationships.ini

Code: Select all

[CUSTOMER_NOTE] __sql__ = "select * from CUSTOMER_NOTE where GRANDPARENT_ID_RECORD='$PARENT_ID_RECORD' and PARENT_ID_RECORD='$ID_RECORD' and PARENT_TABLE_NAME='CUSTOMER_ADDRESS'"

tables/CUSTOMER_PHONE/relationships.ini

Code: Select all

[CUSTOMER_NOTE] __sql__ = "select * from CUSTOMER_NOTE where GRANDPARENT_ID_RECORD='$PARENT_ID_RECORD' and PARENT_ID_RECORD='$ID_RECORD' and PARENT_TABLE_NAME='CUSTOMER_PHONE'"

Or any table really:
tables/TABLE_NAME/relationships.ini

Code: Select all

[NOTE_TABLE_NAME] __sql__ = "select * from NOTE_TABLE_NAME where GRANDPARENT_ID_RECORD='$PARENT_ID_RECORD' and PARENT_ID_RECORD='$ID_RECORD' and PARENT_TABLE_NAME='Table name ( or any descriptor you choose as long as it is consistent for one table and DIFFERENT from all other tables or you WILL get errors'"