Now I am attempting to set up something to start to document all of this work I am doing for our projects. (Currently Ive just been using a forum, but I need a place that I can go to to find my code, notes, tasks, and so on..)
So, just to get things started, I have created a number of tables as follows:
PROJECT_
MODULE_
TABLE_
FIELD_
TASK_
CODE_
NOTE_
Now each of these tables is a child table of the one above, and a grand child, great grand child and thus on for each table above in a cascade. I am currently (temporarily) managing this simply with hard coded fields named after each of the tables above a specific table. Each table has its own ID_RECORD field which is an AUTO Number, primary key.
So the PROJECT_ table has no other relational fields. But the MODULE_ has a field PROJECT_ID_RECORD. And TABLE_ has MODULE_ID_RECORD and PROJECT_ID_RECORD. So on down the line. For the time being this allows me to add a record to any [great][great][grand]child record and yet be able to see it by all of the parents above. For example the FIELD_ table has the following dataface code in the tables/FIELD_/relationships.ini:
[<-PROJECT] (<- Denotes a Parent link so these three link me UP the line)
PROJECT_.ID_RECORD = "$PROJECT_ID_RECORD"
[<-MODULE]
MODULE_.ID_RECORD = "$MODULE_ID_RECORD"
[<-TABLE]
TABLE_.ID_RECORD = "$TABLE_ID_RECORD"
[->TASK] (-> Denotes a child link)
TASK_.PROJECT_ID_RECORD = "$PROJECT_ID_RECORD" Setting TASK_ to the current PROJECT_
TASK_.MODULE_ID_RECORD = "$MODULE_ID_RECORD" setting TASK_ to the current MODULE_
TASK_.TABLE_ID_RECORD = "$TABLE_ID_RECORD" setting TASK_ to the current TABLE_
TASK_.FIELD_ID_RECORD = "$ID_RECORD" setting TASK_ to the current FIELD_
(Now what this means is..I can create task that I have to perform for a certain field. Lets say for example I have to copy this field from this table to another table ..or modify the zip field to do look ups in a zip code database. So I set the task for this field. Now if I am working on a PROJECT_, the same one for which I set the task on the field, and I wish to see ALL tasks for this project, I click the Tasks-> link in the PROJECT_ view page. Then all tasks created by any child records of this PROJECT_ record are displayed. Hope all that makes sense. The same explanations apply to the tables CODE_ and NOTE_ as their dataface code should demonstrate.)
[->CODE]
CODE_.PROJECT_ID_RECORD = "$PROJECT_ID_RECORD"
CODE_.MODULE_ID_RECORD = "$MODULE_ID_RECORD"
CODE_.TABLE_ID_RECORD = "$TABLE_ID_RECORD"
CODE_.FIELD_ID_RECORD = "$ID_RECORD"
[->NOTE]
NOTE_.PROJECT_ID_RECORD = "$PROJECT_ID_RECORD"
NOTE_.MODULE_ID_RECORD = "$MODULE_ID_RECORD"
NOTE_.TABLE_ID_RECORD = "$TABLE_ID_RECORD"
NOTE_.FIELD_ID_RECORD = "$ID_RECORD"
So I've done this, tested it and it works fabulously. Of course this is a temporary fix until I come up with something more substantial. (Thus the reason for this post.)
Now as you can see this creates problems for the future. What if I end up having 20 or 30 tables or more? (Egads!) While it does allow a child to be in a way a child of multiple tables, it only works as long as the tables are descending. If we are dealing with equals (siblings) we would have to use Mr. Steve's method of having a field named say CALLING_TABLE which would just contain the name of the table this particular row belongs to. (I am using this method in our system. We have lots of customers who have multiple addresses and phone numbers. So we set up an address table and a phone table along with notes for both. So the Customer table can view the notes written to by either the address or phone table).
And yes there is another issue. Many to many links. I would like to be able to enter a field once and have it used by many projects, modules, and tables. I've used the method from the documentation for relationships to do this with the PROJECT_ AND MODULE_ relationships. That though of course is easy. For that I have a new table with the following basics (some snipped):
RELATIONSHIP_PROJECT_MODULE (table name)
PROJECT_ID_RECORD (field name)
MODULE_ID_RECORD (field name)
PRIMARY KEY (`PROJECT_ID_RECORD`,`MODULE_ID_RECORD`)
And of course the dataface code in tables/PROJECT_/relationships.ini
[->MODULE]
MODULE_.ID_RECORD = RELATIONSHIP_PROJECT_MODULE.MODULE_ID_RECORD
RELATIONSHIP_PROJECT_MODULE.PROJECT_ID_RECORD = "$ID_RECORD"
Now tomorrow I have a couple of tasks. First reverse the relationship so I can place it in tables/MODULE_/relationships.ini ( I figure that isnt too difficult, just move that stuff up there around).. And then the tedious stuff. I am going to have to figure out a way to make these multi-generational relationships, many to many. Ugh..
OK if anyone has any ideas I wont complain. But mostly I am just tossing out some more of what I am using dataface for and how.
Holler yall.