List:General Discussion« Previous MessageNext Message »
From:hsv Date:December 12 2012 6:09pm
Subject:Re: Foreign-key naming
View as plain text  
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.

Thread
Help with left outer joinLarry Martell11 Dec
  • Re: Help with left outer joinPeter Brawley11 Dec
    • Re: Help with left outer joinLarry Martell11 Dec
      • Re: Help with left outer joinPeter Brawley12 Dec
        • Re: Help with left outer joinLarry Martell12 Dec
  • Re: Help with left outer joinhsv12 Dec
    • Re: Help with left outer joinShawn Green12 Dec
      • Re: Help with left outer joinLarry Martell12 Dec
      • Re: Foreign-key naminghsv12 Dec
    • Re: Help with left outer joinLarry Martell12 Dec