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 :)
On Sat, 27 Apr 2002 rcaskey@arches.uga.edu wrote:
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)...
I didn't read the rest of your email yet, but as regards PMA polluting dataspace, I think it is best if we just standardize on using a single database for all of our tables. I use 'phpMyAdmin' as my database for all of relations and bookmarks, as well as my new DB configuration system.
----- Original Message ----- From: "Robin Johnson" robbat2@fermi.orbis-terrarum.net
On Sat, 27 Apr 2002 rcaskey@arches.uga.edu wrote:
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)...
I didn't read the rest of your email yet, but as regards PMA polluting dataspace, I think it is best if we just standardize on using a single database for all of our tables. I use 'phpMyAdmin' as my database for all of relations and bookmarks, as well as my new DB configuration system.
If a user wants to, he is free chose a prefix for his phpMyAdmin tables. This is already possible in phpMyAdmin 2.2.x. Robin, putting all phpMyAdmin tables into a seperate DB is a good solution, but that's not always possible. I have some MySQL space on my ISP's server, but it doesn't allow me to create new databases...
Alexander
Robin Johnson a écrit :
On Sat, 27 Apr 2002 rcaskey@arches.uga.edu wrote:
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)...
I didn't read the rest of your email yet, but as regards PMA polluting dataspace, I think it is best if we just standardize on using a single database for all of our tables. I use 'phpMyAdmin' as my database for all of relations and bookmarks, as well as my new DB configuration system.
Robin,
in a multi-user configuration, how can you put the relationtable in a central db? Did you add a 'user' field? Do you let users modify the relationtable?
On Mon, 29 Apr 2002, Marc Delisle wrote:
Robin Johnson a �crit :
On Sat, 27 Apr 2002 rcaskey@arches.uga.edu wrote:
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)...
I didn't read the rest of your email yet, but as regards PMA polluting dataspace, I think it is best if we just standardize on using a single database for all of our tables. I use 'phpMyAdmin' as my database for all of relations and bookmarks, as well as my new DB configuration system.
Robin,
in a multi-user configuration, how can you put the relationtable in a central db? Did you add a 'user' field? Do you let users modify the relationtable?
All of my configuration thus far has been per server, as instead of having PMA connected to multiple servers, I just replicate the phpMyAdmin and mysql databases between my two servers.
I'm the only human user of phpMyAdmin in my setup.
in a multi-user configuration, how can you put the relationtable in a central db? Did you add a 'user' field? Do you let users modify the relationtable?
Unless I'm misunderstanding the question, the appropriate place to manage user permissions is within MySQL. You grant select access to the relations table for everyone, but only the administrator gets access to UPDATE the relations table.
Regards,
Jay Davis
"Jay F. Davis" a écrit :
in a multi-user configuration, how can you put the relationtable in a central db? Did you add a 'user' field? Do you let users modify the relationtable?
Unless I'm misunderstanding the question, the appropriate place to manage user permissions is within MySQL. You grant select access to the relations table for everyone, but only the administrator gets access to UPDATE the relations table.
Jay,
In a multi-user system, each developer must be able to update his relationtable (or if the relationtable becomes central, his part of the relation table). You let each developer manage his tables, so I think this should be the same for the relationtable.
-
Jay F. Davis -
Marc Delisle -
Rabus -
rcaskey＠arches.uga.edu -
Robin Johnson