When I wrote my comment after Larry Martell s problem, I already suspected it was somewhat out of place because to his problem it did not apply.
>>>> 2012/12/12 08:25 -0500, Shawn Green >>>> This is a perfectly acceptable naming convention to use. For example if you have a field on the `art` table that references the ID column of the `person` table to indicate the owner and another field to indicate the person who created the art, you might want to use the names `owner_person_id` and `artist_person_id` to keep them separate from the `id` column used to uniquely identify the work of art itself. In this design pattern, each table has a numeric ID column (string-based primary keys are perfectly legal but have their drawbacks and should be used with care) and to reference it from another table you can use the pattern <parenttable>_id. It keeps your naming conventions clean and predictable. If I were to try to use a USING operator in my opening example, I would be trying to match the PK fields of two separate types of data. (the USING example) SELECT ... FROM art INNER JOIN person USING(id)... Let's say that I renamed the id fields to art_id and person_id to make them table-specific. This still fails because a person's identifier as an owner is not the same as a work of art's creator. It also does not allow me to use the `person` table more than once in a single query. (FAIL: a renamed USING example) SELECT ... FROM art INNER JOIN person USING(person_id) <--- does this refer to the owner or the creator of the art? (the name template example) SELECT ... FROM art INNER JOIN person owner on art.owner_person_id = owner.id INNER JOIN person artist on art.artist_person_id = artist.id ... <<<<<<<< Well, you're right, a work can refer to people in at least two different aspects, there is the work's author, and the work s owner. Neither is appropriate for the same name as found in a list of people, because now a distinction is made in the undifferentiated mass. And, yes, in general I suspect that if in one table there are more foreign-key references to the same key in another table, there is enough difference in aspect that none of them is fittingly so named as in the original table. What if neither "author" nor "owner" directly referred to people, but, instead, "author" referred to a table of artists, with their training & style listed, and "owner" referred to a table of owners, with preferred styles of work listed? These tables in the end would refer to people; shall their references bear a name distinct from the key in the original table's? I believe that for every chain of foreign-key references from one table to another, if there is no other chain of foreign-key references from that one table to that other table (and no design-change that changes this is likely!), it is quite all right if along the chain each foreign-key reference and the key to which each refers have the same name. The nice thing about USING and NATURAL is that in a query only one coalesced field is yielded. I find it not quite right to pick between one field or another to yield when both are alike unless one of them is NULL. -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe: http://lists.mysql.com/mysql
