[Phpmyadmin-devel] Re: 2.3.0 Roadmap Discussion

[rcaskey at arches.uga.edu]

Heya all. Iv been working on a project which basically replicates alot 
of phpMyAdmin functionality, and I was about 600 lines of code into the 
project (nothing I know) when I thought to myself...*self*, before you 
go coding all that code to fake referential integrity, lets go see if 
you can use a table structure that phpMyAdmin is using...

Anyway, I decided to check out the current state of the table and here I 
am.

Basically, I am thinking that since phpMyAdmin has started to pollute 
the database with tables of its own that it might as well use a standard 
prefix to add a few more tables to enable more features (eventually down 
the road)...

In short this is what I propose (to be quickly shot down I am sure):
(BTW, alot of programs have variable table prefixes, is this really 
neccecerry?)

CREATE TABLE phpMyAdmin_relationships (
  id int(10) unsigned NOT NULL auto_increment,
  local_key_a varchar(32) NOT NULL default '',
  local_table varchar(32) NOT NULL default '',
  foreign_key_a varchar(32) NOT NULL default '',
  foreign_table_a varchar(32) NOT NULL default '',
  local_key_b varchar(32) default NULL,
  foreign_key_b varchar(32) default NULL,
  foreign_table_b varchar(32) default NULL,
  value_on_foreign_a_delete varchar(32) default NULL,
  value_on_foreign_b_delete varchar(32) default NULL,
  enable_cascade_actions enum('0','1') NOT NULL default '1',
  PRIMARY KEY  (id)
) TYPE=MyISAM

and two examples to clear things up a bit

INSERT INTO qdb_relationships VALUES (1, 'dr_id', 'perscriptions', 'id', 
'doctors', NULL, NULL, NULL, '', NULL, '1');
INSERT INTO qdb_relationships VALUES (2, 'trainer_id', 'apointments', 
'id', 'personal_trainers', 'trainee_id', 'id', 'trainiees', NULL, NULL, 
'1');


The first example is for a table which lists perscriptions and their 
doctors. If the matching doctor is deleted, the perscriptions id is 
blanked out (blank value in value_on_foreign_a_delete.

The second example is an example of a set of appointments between 
trainers and their clients. In either case, if a trainer or an employee 
is deleted, the corresponding record in the local table is deleted as 
well (symbolized by update value is null)

enable_cascade_options can be set to 0 for testing purposes, or if you 
only want to create a link to help you quickly navigate around in 
phpmyadmin

What do you think? Allows both many to one and many to many 
relationships...

Additional things that im planning to add in my project but would love 
to be implemented in phpMyAdmin 3.0 and in the schema sooner...

tbl_users, tbl_usergroups, tbl_groupmembership, tbl_columns, etc for 
managing different levels of access to phpMyAdmin all the way down to 
specific record locking if db designers include a phpMyAdmin_owner field 
or something like that

Any thoughts, comments? Im thinking some of this is probably already 
slightly out of the scope of phpMyAdmin, although you guys have been 
doing an increadible job of filling out the current scope of the project 
and it has been creeping out in its features recently, so maby such a 
drastic expansion could be planned for to allow it to commence in a 
planned and orderly fashion?

If not id settle for a schema so my app could play friendly phpMyAdmin 
and some extra features like the cascading updates :)