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 :)