Page 1 of 1

PostPosted: Wed Jul 19, 2006 8:15 am
by Aoirthoir
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'"