Hello:
Despite field name changes for the relation table fields in 2.3.0, the relation table field names still seem to be misnamed.
The current names are:
master_db master_table master_field foreign_db foreign_table foreign_field
The primary key for the table is defined as:
PRIMARY KEY (`master_db`,`master_table`,`master_field`)
Unfortunately, this is the reverse of what I think makes sense (and probably the reverse of what most database administrators will understand).
Here's what I mean:
Normally, you have two kinds of keys in a relational database: primary keys and foreign keys. Foreign keys are always primary keys in one--and only one--other table. Consider the following simple example:
Table Products: Product ID << This is a primary key Description
Table Orders: Order ID Product ID << This is a foreign key Quantity
In the current PMA 2.3.0 relation table (if I understand it correctly), we would enter the following values:
master_table: Orders master_field: Product ID foreign_table: Products foreign_field: Product ID
Note that the primary key is called foreign and the the foreign key is called master. Ick.
I suggest reversing the naming of the relation table fields. Also, to make this even more clear, rename 'master' to 'primary', as this DOES refer to the actual primary keys of tables. This would also mean that the primary key for the relation table would be:
PRIMARY KEY (`foreign_db`,`foreign_table`,`foreign_field`)
This makes much more sense. (The same foreign key can be in many tables, but it must always refer to the same primary key.)
Perhaps I am totally misunderstanding, but the PRIMARY KEY definition of the relation table is what set me off, so please correct me if am confused.
Regards,
Jay Davis